oracle常用命令.docx

1、oracle常用命令oracle常用命令一、Oracle数据库实例、用户、目录及session会话查看：1、ORACLE SID查看设置查看SID、用户名$ env|grep SID 、select * from v$instance、select instance_name,host_name from v$instance;查看数据库所有用户及用户状态：SQL select usernames,account_status from dba_users;设置SID$ export ORACLE_SID=hisvhfs查看数据库DBID：SQLselect * from v$DBID2、查询

2、、设置Oracle数据库实例最大进程数及最大会话数查看系统最大session：SQLshow parameter session查看当前连接数：SQLselect count(*) from v$bgprocess查看系统最大进程数：SQLshow parameter process查看当前连接到数据库的用户：SQLselect count(*) from v$session查看当前数据库建立的会话情况：SQL select sid,serial#,username,program,machine,status from v$session;查询应用的连接数SQL:SQL SELECT b.M

3、ACHINE, b.PROGRAM, COUNT (*)FROM v$process a, v$session bWHERE a.ADDR = b.PADDR AND b.USERNAME IS NOT NULLGROUP BY b.MACHINE, b.PROGRAMORDER BY COUNT (*) DESC;查看当前数据库的并发连接数SQL select count(*) from v$session where status=ACTIVE;查看当前有哪些用户正在使用数据：select osuser,a.username,cpu_time/executions/1000000|s,sq

4、l_fulltext,machinefrom v$sessiona,v$sqlarea bwhere a.sql_address = b.addressorder by cpu_time/executions desc;查看数据库指定用户的连接情况SQL select sid,serial# from v$session where username=XX; XX为用户例如：SQL select sid,serial# from v$session where username=BSPDEV;SID SERIAL#- -204 4609399 5841590 6041清除用户下连接进程SQL

5、alter system kill session 204,4609;SQL alter system kill session 399,5841;SQL alter system kill session 590,6041;selectcount(*) fromv$process-当前的连接数select value fromv$parameterwhere name =processes-数据库允许的最大连接数修改processes和sessions值SQL alter system set processes=300 scope=spfile;系统已更改。SQL alter system

6、 set sessions=335 scope=spfile;系统已更改。重启数据库:shutdown immediate;startup;-查看当前有哪些用户正在使用数据SELECTosuser, a.username,cpu_time/executions/1000000|s, sql_fulltext,machinefrom v$session a, v$sqlarea bwherea.sql_address=b.addressorderbycpu_time/executions desc;修改processes和sessions值必须重启oracle服务器才能生效ORACLE的连接数(

7、sessions)与其参数文件中的进程数(process)有关，它们的关系如下：sessions=(1.1*process+5)3、查看数据库目录SQL select * from all_directories;4、查看数据库现有模式、是否归档SQlselect name,log_mode from v$database;也可以用下面的语句archive log list；(该方法需要as sysdba)查看数据库的创建日期和归档方式SQL Select Created, Log_Mode, Log_Mode From V$Database; 5、配置用户密码过期时间alter profil

8、e default limit password_life_time unlimited; 配置用户密码永不过期alter profile default limit password_life_time 100; 配置用户密码100天过期6、创建、配置新用户及查看用户属性解锁新用户：SQL alter user scott account unlock；SQL alter user scott identified by tiger;删除oracle用户：SQLdrop user username cascade; (删除与用户相关的所有对象)这样该用户包括该用户下的表，试图，同义词，过程，

9、索引，及相关的一切就删除了。创建用户并赋权限以及设置默认表空间。以sysdba用户登陆进行以下设置：- Create the user create user VHFSMidentified by vhnj1fsmdefault tablespace MGRVHFSTBSDEF 此处是设置默认表空间。temporary tablespace TEMPprofile DEFAULTquota unlimited on mgrvhfstbs2010此处是设置可操作的其他表空间quota unlimited on mgrvhfstbsdef;- Grant/Revoke role privilege

10、s grant connect to VHFSM;grant dba to VHFSM;- Grant/Revoke system privileges grant unlimited tablespace to VHFSM;查看用户及角色权限-1.查看所有用户：select * from dba_users;select * from all_users;select * from user_users;-2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：select * from dba_sys_privs;select * from user_sys_privs;-3.查看角

11、色(只能查看登陆用户拥有的角色)所包含的权限sqlselect * from role_sys_privs;-4.查看用户对象权限：select * from dba_tab_privs;select * from all_tab_privs;select * from user_tab_privs;-5.查看所有角色：select * from dba_roles;-6.查看用户或角色所拥有的角色：select * from dba_role_privs;select * from user_role_privs;-7.查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)

12、select * from V$PWFILE_USERS-注意：-1、以下语句可以查看Oracle提供的系统权限select name from sys.system_privilege_map-2、查看一个用户的所有系统权限(包含角色的系统权限)SELECT privilegeFROM dba_sys_privsWHERE grantee = DATAUSERUNIONSELECT privilegeFROM dba_sys_privsWHERE grantee IN (SELECT granted_role FROM dba_role_privs WHERE grantee = DATAU

13、SER);二、创建、管理Oracle表空间1、先查询空闲空间select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space; 2、增加Oracle表空间先查询数据文件名称、大小和路径的信息，语句如下：select tablespace_name,file_id,bytes,file_name from dba_data_files; 3、修改文件大小语句如下alter database datafile需要增加的数据文件路径，即上面查询出来的路径resize 800M; 4、创建Oracle表空间create t

14、ablespace test datafile /home/app/oracle/oradata/oracle8i/test01.dbf size 8M autoextend on next 5M maxsize 10M; create tablespace sales datafile /home/app/oracle/oradata/oracle8i/sales01.dbf size 800M autoextend on next 50M maxsize unlimited maxsize unlimited 是大小不受限制create tablespace sales datafile

15、/home/app/oracle/oradata/oracle8i/sales01.dbf size 800M autoextend on next 50M maxsize 1000M extent management local uniform; unform表示区的大小相同，默认为1M create tablespace sales datafile /home/app/oracle/oradata/oracle8i/sales01.dbf size 800M autoextend on next 50M maxsize 1000M extent management local uni

16、form size 500K; unform size 500K表示区的大小相同，为500K create tablespace sales datafile /home/app/oracle/oradata/oracle8i/sales01.dbf size 800M autoextend on next 50M maxsize 1000M extent management local autoallocate; autoallocate表示区的大小由随表的大小自动动态改变，大表使用大区小表使用小区create tablespace sales datafile /home/app/ora

17、cle/oradata/oracle8i/sales01.dbf size 800M autoextend on next 50M maxsize 1000M temporary; temporary创建字典管理临时表空间，要创建本地管理临时表空间要加temporary tablespace关键字create temporary tablespace sales tempfile /home/app/oracle/oradata/oracle8i/sales01.dbf size 800M autoextend on next 50M maxsize 1000M 创建本地管理临时表空间，如果是

18、临时表空间，所有语句中的datafile都换为tempfile，所有语句中的datafile都换为tempfile8i为表空间增加数据文件：altertablespace sales add datafile /home/app/oracle/oradata/oracle8i/sales02.dbf size 800M autoextend on next 50M maxsize 1000M; 5、查看表空间是否自动扩展SQL select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_f

19、iles order by file_iddesc;更改自动扩展属性：alter database datafile/home/app/oracle/oradata/oracle8i/sales01.dbf, /home/app/oracle/oradata/oracle8i/sales02.dbf /home/app/oracle/oradata/oracle8i/sales01.dbf autoextend off; 6、表空间的查看与修改查看用户默认表空间SQL select username,default_tablespace from dba_users;查看所有用户的默认表空间S

20、QL select username,default_tablespace from user_users;查看某个用户的默认表空间，前提需要connect该用户。SQL select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username = hr; 查看用户对应的默认表空间修改用户默认表空间SQL alter user zhanghr default tablespace test;设置数据库的默认临时表空间:SQLAlter database default temporary tabl

21、espacetemp_tbs_name;查看用户和默认表空间的关系:SQLselect username,default_tablespace from dba_users;查看临时表空间：SQL select file_name,file_id,blocks,user_blocks from dba_temp_files；SQL select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;查看undo表空间SQL show parameter undo;NAME T

22、YPE VALUE- - -undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1查看undo表空间大小SQL select sum(bytes)/1024/1024 current undo size(M) from dba_data_files where tablespace_name=UNDOTBS1; 通过增加数据文件来改变undo表空间大小SQL alter tablespace undotbs1add datafile /oracle/oradata/orc6/und

23、o02.dbf size 10M; 通过resize更改数据文件大小SQL alter database datafile /oracle/oradata/orc6/undo02.dbf resize 100M;查看某个表空间的数据文件SQL select file_name,tablespace_name,bytes/1024/1024 bytes MB,maxbytes/1024/1024 maxbytes MB from dba_data_files where tablespace_name=ORA1TBS;指定表空间名要大写查看所有表空间大小SQL select tablespace

24、_name,sum(bytes)/1024/1024 from dba_data_filesgroup by tablespace_name;查看已使用的表空间大小SQL select tablespace_name,sum(bytes)/1024/1024 from dba_free_spacegroup by tablespace_name;oracle查看表空间大小及使用率：方法一：SELECT UPPER(F.TABLESPACE_NAME),D.TOT_GROOTTE_MB,D.TOT_GROOTTE_MB,F.TOTAL_BYTES,TO_CHAR(ROUND(D.TOT_GROO

25、TTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),990.99), F.TOTAL_BYTES, F.MAX_BYTES FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,ROUND(S

26、UM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;方法二：SELECT D.TABLESPACE_NAME, SPACE | M SUM_SPACE(M), BLOCKS SUM_BLOCKS, SPACE - NVL (FREE_SPACE, 0) | M USED_SPACE(M), ROUND ( (1 - N

27、VL (FREE_SPACE, 0) / SPACE) * 100, 2) | % USED_RATE(%), FREE_SPACE | M FREE_SPACE(M) FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE

28、_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, SPACE | M SUM_SPACE(M), BLOCKS SUM_BLOCKS, USED_SPACE | M USED_SPACE(M), ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) | % USED_RATE(%), NVL (FREE_SPACE, 0) | M FR

29、EE_SPACE(M) FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM

30、V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1; 方法三：SELECT *FROM (SELECT a.tablespace_name,to_char(a.bytes / 1024 / 1024, 99,999.999) total_bytes,to_char(b.bytes / 1024 / 1024, 99,999.999) free_bytes,to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,99,999.999) use_bytes,to_char(1 - b.bytes / a.bytes) * 100, 99.99) | % USEFROM (