oracle DBA常用命令大全.doc

1 运行SQLPLUS工具 sqlplus 2 以OS的默认身份连接 / as sysdba 3 显示当前用户名 show user 4 直接进入SQLPLUS命令提示符 sqlplus /nolog 5 在命令提示符以OS身份连接 connect / as sysdba 6 以SYSTEM的身份连接 connect system/xxxxxxx服务名 7 显示当然用户有哪些表 select * from tab; 8 显示有用户名和帐户的状态 select username,account_status from dba_users; 9 将SCOTT帐号解锁(加锁) alter user scott account unlock(lock); 10 以SCOTT的身份连接并且查看所属表 connect scott/tiger select * from tab; 11 查看EMP的表结构及记录内容 desc emp select empno,ename from emp; 12 以OS的身份登看SGA,共享池,CACHE的信息 connect / as sysdba show sga select name,value/1024/1024 from v$sga; show parameter shared_pool_size select value/1024/1024 from v$parameter where name =shared_pool_size; show parameter db_cache_size select value/1024/1024 from v$parameter where name =db_cache_size; 13 查看所有含有SIZE的信息 show parameter size bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_block_size integer 4096 db_cache_size big integer 33554432 db_keep_cache_size big integer 0 db_recycle_cache_size big integer 0 NAME TYPE VALUE - - - global_context_pool_size string hash_area_size integer 1048576 java_max_sessionspace_size integer 0 java_pool_size big integer 33554432 large_pool_size big integer 8388608 max_dump_file_size string UNLIMITED object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 olap_page_pool_size integer 33554432 oracle_trace_collection_size integer 5242880 parallel_execution_message_size integer 2148 NAME TYPE VALUE - - - sga_max_size big integer 143727516 shared_pool_reserved_size big integer 2516582 shared_pool_size big integer 50331648 sort_area_retained_size integer 0 sort_area_size integer 524288 workarea_size_policy string AUTO 14 显示SGA的信息 select * from v$sgastat; POOL NAME BYTES - - - fixed_sga 453532 buffer_cache 33554432 log_buffer 656384 shared pool subheap 46884 shared pool KGK heap 3756 shared pool KQR M PO 586792 shared pool KQR S PO 180232 shared pool KQR S SO 5128 shared pool sessions 410720 shared pool sql area 2144664 shared pool 1M buffer 2098176 POOL NAME BYTES - - - shared pool KGLS heap 901756 shared pool parameters 8352 shared pool free memory 38687204 shared pool PL/SQL DIANA 420816 shared pool FileOpenBlock 695504 shared pool PL/SQL MPCODE 135692 shared pool library cache 2985576 shared pool miscellaneous 4889396 shared pool MTTR advisory 21164 shared pool PLS non-lib hp 2068 shared pool XDB Schema Cac 4966300 POOL NAME BYTES - - - shared pool joxs heap init 4220 shared pool kgl simulator 563260 shared pool sim memory hea 44184 shared pool table definiti 1728 shared pool trigger defini 1896 shared pool trigger inform 1140 shared pool trigger source 448 shared pool type object de 69120 shared pool Checkpoint queue 282304 shared pool VIRTUAL CIRCUITS 265160 shared pool dictionary cache 1610880 POOL NAME BYTES - - - shared pool KSXR receive buffers 1033000 shared pool character set object 323724 shared pool FileIdentificatonBlock 323292 shared pool message pool freequeue 834752 shared pool KSXR pending messages que 841036 shared pool event statistics per sess 1718360 shared pool fixed allocation callback 180 large pool free memory 8388608 java pool free memory 33554432 已选择42行。 15 显示PGA的信息 select * from v$pgastat; NAME VALUE UNIT - - - aggregate PGA target parameter 16777216 bytes aggregate PGA auto target 7640064 bytes global memory bound 838656 bytes total PGA inuse 8293376 bytes total PGA allocated 13106176 bytes maximum PGA allocated 22090752 bytes total freeable PGA memory 0 bytes PGA memory freed back to OS 0 bytes total PGA used for auto workareas 0 bytes maximum PGA used for auto workareas 4096 bytes total PGA used for manual workareas 0 bytes NAME VALUE UNIT - - - maximum PGA used for manual workareas 4096 bytes over allocation count 0 bytes processed 8783872 bytes extra bytes read/written 0 bytes cache hit percentage 100 percent 已选择16行。 17 在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入环境变量,以后每次启动生效 define _editor=vi set line 2000 18 将当前命令随加到文件中 save c:a.sql append 19 将指定文件的命读出缓冲区 get c:a.sql 20 执行脚本语句 c:a.sql 21 将输入保存到指定文件中 spool c:O.LOG select * from v$sga; spool off 22 设定行大小 set linesize 2000 23 设定页大小 set pagesize 10 24 设定字符列格式 col ename format a30 25 设定数字列格式 col sal format 999,999.999 26 10G查看文件$ORACLE_HOME/install/protlist显示端口 :5560/isqlplus 9i查看文件$ORACLE_HOME/Apache/Apache/ports.ini显示端口 :7778/isqlplus :7778/isqlplusdba 27 启动Oracle 9i监听程序 Oracle的监听程序主要是为客户端的连接提供接口 $ lsnrctl start 28 关闭Oracle 9i监听程序 $ lsnrctl stop 29 启动Oracle Web Server $ cd $ORACLE_HOME/Apache/Apache/bin $ ./startJServ.sh /database/oracle/product/9i/Apache/Apache/bin/apachectl start: httpd started 30 关闭Oracle Web Server $ cd $ORACLE_HOME/Apache/Apache/bin $ ./stopJServ.sh /database/oracle/product/9i/Apache/Apache/bin/apachectl stop: httpd stopped 31 启动Oracle Web Server后默认的端口号是7777 unix cd $ORACLE_HOME/Apache/Apache/bin/ htpasswd $ORACLE_HOME/sqlplus/admin/iplusdba.pw admin windows cd D:oracleora92ApacheApachebin htpasswd D:oracleora92/sqlplus/admin/iplusdba.pw admin New password: * Re-type new password: * Adding password for user admin 32 pfile: $ORACLE_BASE/admin/实例名/Pfile spfile: $ORACLE_HOME/dbs 监听器配置文件:listener.ora: $ORACLE_HOME/network/admin/ tnsnames.ora: $ORACLE_HOME/network/admin/ 33 指定用户的表空间 SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=用户名 34 当前用户 select user from dual;show user 35 当前用户的缺省表空间 SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=(select user from dual); 37 显示正在使用的初始化参数文件 show parameter spfile NAME TYPE VALUE - - - spfile string %ORACLE_HOME%DATABASESPFILE% ORACLE_SID%.ORA 38 用SPfile转Pfile create pfile=c:init.ora from spfile; create pfile from spfile; UNIX将生成在$ORACLE_HOME/dbs目录下 WINDOWS将生成在$ORACLE_HOME/DATABASE目录下 39 显示cache的值 show parameter cache 前面两个下划线的是动态调整的参数 前面一个下划线的是内部参数 40 显示正在使用的参数和文本参数值 select * from v$parameter; select * from v$spparameter; 41 显示动态性能视图的结构 desc v$parameter; 名称 NUM NAME TYPE VALUE ISDEFAULT ISSES_MODIFIABLE ISSYS_MODIFIABLE ISMODIFIED ISADJUSTED DESCRIPTION UPDATE_COMMENT 42 有条件的显示动态参数 select * from v$parameter where name like %db_cache%; 43 修改参数语句 alter system set sga_max_size=256m scope=spfile; &写入SPFILE,重启生效 alter system set sga_max_size=256m scope=memory; &写入内存区,立即生效 alter system set sga_max_size=256m scope=both; &即写入内存区,又写入SPFILE,默认为BOTH. 44 动态调整large_pool_size的语句 alter system set large_pool_size=16M; 45 当前正在使用的SGA的大小 select sum(bytes)/1024/1024 from v$sgastat; 46 设置db_cache_size的大小保存在SPFILE alter system set db_cache_size=128m scope=spfile; 47 设置shared_pool_size的大小保存在SPFILE中 alter system set shared_pool_size=80m scope=spfile; 48 显示PGA的状态 select * from v$pgastat; show parameter pga NAME TYPE VALUE - - - pga_aggregate_target big integer 16777216 49 带PFILE来启动资料库,?代表ORACLE的主目录 startup pfile=?databaseinitmydb.ora 50 显示当前数据库的状态,mount的状态下就可以查看数据库的状态 select open_mode from v$database; 51 显示当前实例 nomount状态下就可以查看实例 select * from v$instance; 52 显示 background_dump_dest的路径 , show parameter background_dump_dest NAME TYPE VALUE - - - background_dump_dest string D:oracleadminthwerpbdump 53 显示当有用户的表 select * from user_tables; 54 显示当有用户可以访问表 select * from all_tables; 55 显示用户为SCOTT的表 select * from dba_tables where owner=SCOTT; 56 显示所有用户信息 select * from dba_users; 57 建立用户并指定密码 create user edpthw identified by edpthw; 58 给用户授权 grant connect,resource to edpthw; 59 建立一个表 create table a(a int); 60 显示数据字典中表名为USER开头的表名 select table_name from dict where table_name like USER%; 61 在UNIX下oerr ora 错误号查看错误的帮助信息. 62 在unix 查看日志中有哪些错误 grep ORA alert_PROD.log|tail -10 63 显示当前实例 SELECT * FROM V$INSTANCE; 64 显示当前数据库信息 SELECT * FROM V$DATABASE; 65 显示当前ORACLR资料的版本 SELECT * FROM V$VERSION; 66 显示当前的选项 SELECT * FROM V$OPTION; 67 显示当前实例的初始化参数 SELECT * FROM V$PARAMETER; 68 显示数据文件 SELECT * FROM V$DATAFILE; 69 显示日志文件 SELECT * FROM V$LOGFILE 70 显示控制文件 SELECT * FROM V$CONTROLFILE; 71 显示当有有哪些会话 SELECT * FROM V$SESSION; 72 显示当前有哪些进程 SELECT * FROM V$PROCESS; 73 显示当前后台进程 SELECT * FROM V$BGPROCESS; 74 显示SGA的大小 SELECT * FROM V$SGA; 75 显示SGA的统计数 SELECT * FROM V$SGASTAT; 76 显示PGA的统计数 SELECT * FROM V$PGASTAT; 77 显示有哪些控制文件 SHOW PARAMETER CONTROL_FILES; 78 增加一个控制文件一定要在NOMOUNT状态下进行.语句如下. alter system set control_files=D:ORADATAMYDBCONTROL01.CTL, D:ORADATAMYDBCONTROL02.CTL, D:ORADATAMYDBCONTROL03.CTL, E:CONTROL04.CTL SCOPE=SPFILE; alter system set control_files=D:oracleoradatathwerpCONTROL01.CTL, D:oracleoradatathwerpCONTROL02.CTL, D:oracleoradatathwerpCONTROL03.CTL, D:oracleoradatathwerpCONTROL04.CTL scope=spfile; 79 数据重新启动 STARTUP FORCE 80 显示当前控制文件 SELECT * FROM V$CONTROLFILE; SHOW PARAMETER CONTROL 81 删除一个控制文件 alter system set control_files=D:ORADATAMYDBCONTROL01.CTL, D:ORADATAMYDBCONTROL02.CTL, D:ORADATAMYDBCONTROL03.CTL SCOPE=SPFILE 82 显示当前使用的日志 SELECT * FROM V$LOG; 83 显示实例的归档模式 ARCHIVE LOG LIST; 84 显示重做日志文件 SELECT * FROM V$LOGFILE; 85 手工切换重做日志文件 ALTER SYSTEM SWITCH LOGFILE; 86 发出检查点 ALTER SYSTEM CHECKPOINT; 87 显示重做日期的历史记录 select * from v$log_history; 88 更改会语的日期格式 alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss; 89 增加日志组 ALTER DATABASE ADD LOGFILE GROUP 4 ( D:ORADATAMYDBredo4.log) SIZE 10240K; 92 删除日志组 ALTER DATABASE DROP LOGFILE GROUP 4 ; 90 增加日志组成员 ALTER DATABASE ADD LOGFILE MEMBER D:ORADATAMYDBredo11.log TO GROUP 1; 91 删除日志组成员 ALTER DATABASE DROP LOGFILE MEMBER D:ORADATAMYDBredo14.log ; 92 显示系统表空间 select * from dba_tablespaces; 93 显示系统表空间所拥有的文件 select * from dba_data_files; 94 显示系统临时表空间 select * from dba_temp_files; 95 显示表空间的已被使用多少 select tablespace_name,sum(bytes)/1024/1024 m from dba_data_files group by tablespace_name; 96 显示表空间还有多少没有使用 select tablespace_name, sum(bytes)/1024/1024 m from dba_free_space group by tablespace_name; 97 创建表空间 10G CREATE BIGFILE TABLESPACE MYTBS1 DATAFILE D:ORADATAMYDBmytbs_01.dbf SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; 9I CREATE TABLESPACE MYTBS1 DATAFILE D:oracleORADATAthwerpmytbs_01.dbf SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; CREATE SMALLFILE TABLESPACE MYTBS1 DATAFILE D:ORADATAMYDBmytbs_01.dbf SIZE 100M , D:ORADATAMYDBmytbs02.dbf SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 98 在指字表空间里建表 create table t1 (a int) tablespace mytbs1; 99 在指字表空间里建索引 create index t1_ind on t1(a) tablespace mytbs1; 100 在指定表空间里增加数据文件 ALTER TABLESPACE MYTBS1 ADD DATAFILE D:ORADATAMYDBmytbs03.dbf SIZE 100M 100 修改表空间的大小 ALTER DATABASE DATAFILE D:ORADATAMYDBMYTBS_01.DBF RESIZE 200M 101 表空间改文件的自动扩展 ALTER DATABASE DATAFILE D:ORADATAMYDBMYTBS03.DBF AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED 102 删除表空间和表空间所属文件 drop tablespace mytbs1 including contents and datafiles; 103 建立临时表空间 CREATE SMALLFILE TEMPORARY TABLESPACE MYTEMP TEMPFILE D:oracleORADATAthwerpmytemp01.dbf SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M CREATE TEMPORARY TABLESPACE MYTEMP TEMPFILE D:oracleORADATAthwerpmytemp01.dbf SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M 104 更改用户的临时表空间 ALTER USER GARY TEMPORARY TABLESPACE MYTEMP 105 设置成默认的表空间 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE MYTEMP 106 创建临时表空间组 ALTER TABLESPACE MYTEMP TABLESPACE GROUP MYTEMP_GROUP ALTER TABLESPACE TEMP TABLESPACE GROUP MYTEMP_GROUP 107 创建重做表空间 CREATE UNDO TABLESPACE MYUNDO DATAFILE D:ORADATAMYDBmyundo01.dbf SIZE 200M 108 显示重做参数 show parameter undo 109 回滚段的使用情况 select * from dba_rollback_segs; 110 更改重做表空间 alter system set undo_tablespace=myundo; 111 建立一个表 create table a tablespace users as select * from dba_objects; 112 插入一些记录 insert into a select * from a; 113 有条件查询表名放在哪个表空间 select * from dba_tables where table_name=A and owner=SYS; 114 有条件查询段名放在哪个表空间 select * from dba_segments where segment_name=A and owner=SYS; 115 查询有哪些段类型 select distinct segment_type from dba_segments; 116 查询段和表空间的对应关系 select * from dba_extents where segment_name=A and owner=SYS; 117 建立一个16K表空间 CREATE TABLESPACE MYTBS3 DATAFILE D:oracleORADATAthwerpmytbs3.dbf SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 16384; 118 指字参数建立表 create table b (a int,b varchar2(10) tablespace users INITRANS 2 PCTFREE 5; 119 显示当前所使用的块大小 show parameter block_size; 120 释放表的高水位的空间 alter table a deallocate unused; 121 高水位前移.速度快 truncate table a; 122 分配空间给表 alter table a allocate extent (datafile D:ORADATAMYDBusers01.dbf size 1m); 123 指定参数建立表 create table C (a int) tablespace system pctfree 10 pctused 60 storage(freelists 2); CREATE TABLE SYS.D ( A VARCHAR2(10) TABLESPACE SYSTEM PCTFREE 5 PCTUSED 60 INITRANS 2 MAXTRANS 100 STORAGE ( FREELISTS 2) 124 生成一个测试表 create table a tablespace users as select * from dba_objects; 125 查看表行的物理地址 select rowid form a; 126 用包的命令查看表行的物理地址 select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM A 127 建立表索引 CREATE INDEX A_IND ON A(OBJECT_NAME) TABLESPACE MYTBS2; 128 查看表的索引 SELECt * FROM DBA_INDEXES WHERE TABLE_NAME=A; SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME=A_IND; 129 移动重组表空间 ALTER TABLE A MOVE TABLESPACE USERS; 130 重建表索引 ALTER INDEX A_IND REBUILD; 131 设置行可以内部移动属性 ALTER TABLE A ENABLE ROW MOVEMENT; 132 在原地进行行的移动 ALTER TABLE A SHRINK SPACE; 133 查看表所存储的信息 SELECT BYTES FORM DBA_SEGMENTS WHERE SEGMENT_NAME=A; SELECT OWNER,SEGMENT_NAME,BYTES FROM DBA_SEGMENTS WHERE SEGMENT_NAME=A; 134 截取表 TRUNCATE TABLE A; 135 删除表 DROP TABLE A CASCADE CONSTRAINTS; 136 显示垃圾桶(10G才有) show recyclebin SELECT * FROM DBA_RECYCLEBIN; SELECT * FROM USER_RECYCLEBIN; 137 恢复已删除的表 flashback table a to before drop; 138 恢复较早已前的已删除的表 flashback table BIN$hWrBq1irS0COp10n22NZJg=$0 to before drop rename to a1; 139 查询指定表的索引 select * from dba_indexs where table_name=A; 140 清除垃圾桶 PURGE RECYCLEBIN; PURGE DBA_RECYCLEBIN; 141 删除表并清除垃圾桶(10G) DROP TABLE A PURGE; 142 删除表的列 ALTER TABLE A DROP COLUMN COMMENTS CASCAGE CONSTRAINTES CHECKPOINT 1000; 143 重命名列 ALTER TABLE A RENAME COLUMN HIRE_DATE TO START_DATE; 144 标记不可使用的列 ALTER TABLE A SET UNUSED COLUMN COMMENTS CASCADE CONSTRAINTS; 145 删除不再使用的的列 ALTER TABLE A DROP UNUSED COLUMNS CHECKPOINT 10000; 146 继续列的删除操作 ALTER TABLE A DROP COLUMNS CONTINUE CHECKPOINT 10000; 147 增加表的主索引 ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID); 148 删除表的主索引 ALTER TABLE A DROP PRIMARY KEY; 149 索引表的主索引并指定索引所使用的表空间 ALTER TABLE A ADD PRIMARY KEY (OBJECT_ID) USING INDEX TABLESPACE USERS; 150 建立一个普通索引 CREATE INDEX A_NAME_IND ON A(OWNER,OBJECT_NAME); 151 建立一个函数索引 SELECT /*+ INDEX(A A_NAME2_IND) */ * FROM A WHERE UPPER(OBJECT_NAME)=A; 152 建立一个位图索引 CREATE BITMAP INDEX A_NAME_BT ON A(OWNER); 153 查询数据库的信息 SELECT /*+ INDEX(A A_NAME_BT) */ * FROM A WHERE OWNER=SYS OR OWNER=SYSTEM; SELECT /*+ FULL(A) */ * FROM A WHERE OWNER=SYS OR OWNER=SYSTEM; 154 生成一个脚本来建立索引重整 SELECT ALTER INDEX |INDEX_NAME| COALESCE; FROM DBA_INDEXES WHERE TABLE_NAME=A AND OWNER=SYS SELECT ALTER INDEX |INDEX_NAME| REBUILD ONLINE; FROM DBA_INDEXES WHERE TABLE_NAME=A AND OWNER=SYS 155 显示所有用户的信息 SELECT * FROM DBA_USERS; 156 显示当前数据库的用户 SELECT USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS; 160 建立一个新用户 create user edpthw identified by 123456 default tablespace users temporary tablespace temp quota unlimited on users quota 10m on mytbs2; 161 查询限额信息 select * from dba_ts_quotas; select * from user_ts_quotas; 162 用户改密码 alter user edpthw identified by edpthw; 163 锁用户 alter user edpthw account lock; 164 使密码过期,迫使改下一次进入改密码 alter user edpthw password expire; 165 取消限额 alter user edpthw quota unlimited on mytbs2; 166 删除用户 drop user hmtong cascade; 167 授权用户 grant create session,create table,create view to edpthw; 168 分别授权 GRANT CREATE ANY TABLE TO hmtong GRANT CREATE TABLE TO hmtong GRANT DROP ANY TABLE TO hmtong GRANT SELECT ANY TABLE TO hmtong 169 折消授权 REVOKE UNLIMITED TABLESPACE FROM hmtong 170 对象授权 GRANT SE