oracle常用命令Word格式.docx
《oracle常用命令Word格式.docx》由会员分享,可在线阅读,更多相关《oracle常用命令Word格式.docx(39页珍藏版)》请在冰豆网上搜索。
selectcount(*)fromv$sessionwherestatus='
ACTIVE'
;
查看当前有哪些用户正在使用数据:
selectosuser,a.username,cpu_time/executions/1000000||'
s'
sql_fulltext,machine
fromv$sessiona,v$sqlareab
wherea.sql_address=b.address
orderbycpu_time/executionsdesc;
查看数据库指定用户的连接情况
selectsid,serial#fromv$sessionwhereusername='
XX'
XX为用户
例如:
BSPDEV'
SIDSERIAL#
--------------------
2044609
3995841
5906041
清除用户下连接进程
altersystemkillsession'
204,4609'
399,5841'
590,6041'
修改processes和sessions值
SQL>
altersystemsetprocesses=300scope=spfile;
系统已更改。
altersystemsetsessions=335scope=spfile;
修改processes和sessions值必须重启oracle服务器才能生效
ORACLE的连接数(sessions)与其参数文件中的进程数(process)有关,它们的关系如下:
sessions=(1.1*process+5)
1.3查看数据库目录
select*fromall_directories;
1.4查看数据库现有模式、是否归档
SQl>
selectname,log_modefromv$database;
也可以用下面的语句
archiveloglist;
(该方法需要assysdba)
查看数据库的创建日期和归档方式
SelectCreated,Log_Mode,Log_ModeFromV$Database;
1.5配置用户密码过期时间
alterprofile"
default"
limitpassword_life_timeunlimited;
配置用户密码永不过期
limitpassword_life_time100;
配置用户密码100天过期
1.6创建、配置新用户及查看用户属性
解锁新用户:
alteruserscottaccountunlock;
alteruserscottidentifiedbytiger;
删除oracle用户:
dropuserusernamecascade;
(删除与用户相关的所有对象)
这样该用户包括该用户下的表,试图,同义词,过程,索引,及相关的一切就删除了。
创建用户并赋权限以及设置默认表空间。
以sysdba用户登陆进行以下设置:
--Createtheuser
createuserVHFSM
identifiedbyvhnj1fsm
defaulttablespaceMGRVHFSTBSDEF此处是设置默认表空间。
temporarytablespaceTEMP
profileDEFAULT
quotaunlimitedonmgrvhfstbs2010此处是设置可操作的其他表空间
quotaunlimitedonmgrvhfstbsdef;
--Grant/Revokeroleprivileges
grantconnecttoVHFSM;
grantdbatoVHFSM;
--Grant/Revokesystemprivileges
grantunlimitedtablespacetoVHFSM;
查看用户及角色权限
--1.查看所有用户:
select*fromdba_users;
select*fromall_users;
select*fromuser_users;
--2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限):
select*fromdba_sys_privs;
select*fromuser_sys_privs;
--3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>
select*fromrole_sys_privs;
--4.查看用户对象权限:
select*fromdba_tab_privs;
select*fromall_tab_privs;
select*fromuser_tab_privs;
--5.查看所有角色:
select*fromdba_roles;
--6.查看用户或角色所拥有的角色:
select*fromdba_role_privs;
select*fromuser_role_privs;
--7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
select*fromV$PWFILE_USERS
--注意:
--1、以下语句可以查看Oracle提供的系统权限
selectnamefromsys.system_privilege_map
--2、查看一个用户的所有系统权限(包含角色的系统权限)
SELECTprivilege
FROMdba_sys_privs
WHEREgrantee='
DATAUSER'
UNION
WHEREgranteeIN(SELECTgranted_roleFROMdba_role_privsWHEREgrantee='
);
2创建、管理Oracle表空间
1、先查询空闲空间
selecttablespace_name,file_id,block_id,bytes,blocksfromdba_free_space;
2、增加Oracle表空间
先查询数据文件名称、大小和路径的信息,语句如下:
selecttablespace_name,file_id,bytes,file_namefromdba_data_files;
3、修改文件大小语句如下
alterdatabasedatafile
'
需要增加的数据文件路径,即上面查询出来的路径
resize800M;
4、创建Oracle表空间
createtablespacetest
datafile'
/home/app/oracle/oradata/oracle8i/test01.dbf'
size8M
autoextendon
next5M
maxsize10M;
createtablespacesales
/home/app/oracle/oradata/oracle8i/sales01.dbf'
size800M
next50M
maxsizeunlimited
maxsizeunlimited是大小不受限制
maxsize1000M
extentmanagementlocaluniform;
unform表示区的大小相同,默认为1M
extentmanagementlocaluniformsize500K;
unformsize500K表示区的大小相同,为500K
extentmanagementlocalautoallocate;
autoallocate表示区的大小由随表的大小自动动态改变,大表使用大区小表使用小区
temporary;
temporary创建字典管理临时表空间,要创建本地管理临时表空间要加temporarytablespace关键字
createtemporarytablespacesales
tempfile'
创建本地管理临时表空间,如果是临时表空间,所有语句中的datafile都换为tempfile,所有语句中的datafile都换为tempfile8i
为表空间增加数据文件:
altertablespacesalesadd
/home/app/oracle/oradata/oracle8i/sales02.dbf'
autoextendonnext50M
maxsize1000M;
5、查看表空间是否自动扩展
selectfile_id,file_name,tablespace_name,autoextensible,increment_byfromdba_data_filesorderbyfile_iddesc;
更改自动扩展属性:
/home/app/oracle/oradata/oracle8i/sales01.dbf
autoextendoff;
6、表空间的查看与修改
查看用户默认表空间
selectusername,default_tablespacefromdba_users;
查看所有用户的默认表空间
selectusername,default_tablespacefromuser_users;
查看某个用户的默认表空间,前提需要connect该用户。
selectUSERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACEfromdba_userswhereusername='
hr'
查看用户对应的默认表空间
修改用户默认表空间
alteruserzhanghrdefaulttablespacetest;
设置数据库的默认临时表空间:
Alterdatabasedefaulttemporarytablespacetemp_tbs_name;
查看用户和默认表空间的关系:
selectusername,default_tablespacefromdba_users;
查看临时表空间:
selectfile_name,file_id,blocks,user_blocksfromdba_temp_files;
selecttablespace_name,current_users,total_blocks,used_blocks,free_blocksfromv$sort_segment;
查看undo表空间
showparameterundo;
NAMETYPEVALUE
-----------------------------------------------------------------------------
undo_managementstringAUTO
undo_retentioninteger900
undo_tablespacestringUNDOTBS1
查看undo表空间大小
selectsum(bytes)/1024/1024"
currentundosize(M)"
fromdba_data_fileswheretablespace_name='
UNDOTBS1'
通过增加数据文件来改变undo表空间大小
altertablespaceundotbs1
adddatafile'
/oracle/oradata/orc6/undo02.dbf'
size10M;
通过resize更改数据文件大小
alterdatabasedatafile'
resize100M;
查看某个表空间的数据文件
selectfile_name,tablespace_name,bytes/1024/1024"
bytesMB"
maxbytes/1024/1024"
maxbytesMB"
ORA1TBS'
指定表空间名要大写
查看所有表空间大小
selecttablespace_name,sum(bytes)/1024/1024fromdba_data_files
groupbytablespace_name;
查看已使用的表空间大小
selecttablespace_name,sum(bytes)/1024/1024fromdba_free_space
oracle查看表空间大小及使用率:
方法一:
SELECTUPPER(F.TABLESPACE_NAME),D.TOT_GROOTTE_MB,D.TOT_GROOTTE_MB,F.TOTAL_BYTES,TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'
990.99'
),F.TOTAL_BYTES,F.MAX_BYTESFROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC;
方法二:
SELECTD.TABLESPACE_NAME,
SPACE||'
M'
"
SUM_SPACE(M)"
BLOCKS"
SUM_BLOCKS"
SPACE-NVL(FREE_SPACE,0)||'
USED_SPACE(M)"
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)||'
%'
"
USED_RATE(%)"
FREE_SPACE||'
FREE_SPACE(M)"
FROM(SELECTTABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2)SPACE,
SUM(BLOCKS)BLOCKS
FROMDBA_DATA_FILES
GROUPBYTABLESPACE_NAME)D,
(SELECTTABLESPACE_NAME,
ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACE
FROMDBA_FREE_SPACE
GROUPBYTABLESPACE_NAME)F
WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
UNIONALL
BLOCKSSUM_BLOCKS,
USED_SPACE||'
ROUND(NVL(USED_SPACE,0)/SPACE*100,2)||'
NVL(FREE_SPACE,0)||'
FROMDBA_TEMP_FILES
ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACE
FROMV$TEMP_SPACE_HEADER
ORDERBY1;
方法三:
SELECT*
FROM(SELECTa.tablespace_name,
to_char(a.bytes/1024/1024,'
99,999.999'
)total_bytes,
to_char(b.bytes/1024/1024,'
)free_bytes,
to_char(a.bytes/1024/1024-b.bytes/1024/1024,
)use_bytes,
to_char((1-b.bytes/a.bytes)*100,'
99.99'
)||'
USE
FROM(SELECTtablespace_name,SUM(bytes)bytes
FROMdba_data_files
GROUPBYtablespace_name)a,
(SELECTtablespace_name,SUM(bytes)bytes
FROMdba_free_space
GROUPBYtablespace_name)b
WHEREa.tablespace_name=b.tablespace_name
UNIONALL
SELECTc.tablespace_name,
to_char(c.bytes/1024/1024,'
to_char((c