Oracle 命令大全底部为环境变量配置路径。

1 运行SQLPLUS工具sqlplus2 以OS的默认身份连接/ as sysdba3 显示当前用户名show user4 直接进入SQLPLUS命令提示符sqlplus /nolog5 在命令提示符以OS身份连接connect / as sysdba6 以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/tigerselect * from tab;11 查看EMP的表结构及记录内容desc empselect empno,ename from emp;12 以OS的身份登看SGA,共享池,CACHE的信息connect / as sysdbashow sgaselect name,value/1024/1024 from v$sga;show parameter shared_pool_sizeselect value/1024/1024 from v$parameter where name ='shared_pool_size';show parameter db_cache_sizeselect value/1024/1024 from v$parameter where name ='db_cache_size';13 查看所有含有SIZE的信息show parameter sizebitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608db_16k_cache_size big integer 0db_2k_cache_size big integer 0db_32k_cache_size big integer 0db_4k_cache_size big integer 0db_8k_cache_size big integer 0db_block_size integer 4096db_cache_size big integer 33554432db_keep_cache_size big integer 0db_recycle_cache_size big integer 0NAME TYPE V ALUE------------------------------------ ----------- -------------global_context_pool_size stringhash_area_size integer 1048576java_max_sessionspace_size integer 0java_pool_size big integer 33554432large_pool_size big integer 8388608max_dump_file_size string UNLIMITEDobject_cache_max_size_percent integer 10object_cache_optimal_size integer 102400olap_page_pool_size integer 33554432oracle_trace_collection_size integer 5242880parallel_execution_message_size integer 2148NAME TYPE V ALUE------------------------------------ ----------- -------------sga_max_size big integer 143727516shared_pool_reserved_size big integer 2516582shared_pool_size big integer 50331648sort_area_retained_size integer 0sort_area_size integer 524288workarea_size_policy string AUTO14 显示SGA的信息select * from v$sgastat;POOL NAME BYTES----------- -------------------------- ---------- fixed_sga 453532buffer_cache 33554432log_buffer 656384shared pool subheap 46884shared pool KGK heap 3756shared pool KQR M PO 586792shared pool KQR S PO 180232shared pool KQR S SO 5128shared pool sessions 410720shared pool sql area 2144664shared pool 1M buffer 2098176POOL NAME BYTES----------- -------------------------- ---------- shared pool KGLS heap 901756shared pool parameters 8352shared 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 2068shared pool XDB Schema Cac 4966300POOL NAME BYTES----------- -------------------------- ---------- shared pool joxs heap init 4220shared pool kgl simulator 563260shared pool sim memory hea 44184 shared pool table definiti 1728shared pool trigger defini 1896shared pool trigger inform 1140shared pool trigger source 448shared pool type object de 69120shared pool Checkpoint queue 282304 shared pool VIRTUAL CIRCUITS 265160 shared pool dictionary cache 1610880POOL NAME BYTES----------- -------------------------- ---------- shared pool KSXR receive buffers 1033000shared pool character set object 323724shared pool FileIdentificatonBlock 323292shared pool message pool freequeue 834752shared pool KSXR pending messages que 841036shared pool event statistics per sess 1718360shared pool fixed allocation callback 180large pool free memory 8388608java pool free memory 33554432已选择42行。

15 显示PGA的信息select * from v$pgastat;NAME V ALUE UNIT---------------------------------------------------------------- ---------- ---------aggregate PGA target parameter 16777216 bytesaggregate PGA auto target 7640064 bytesglobal memory bound 838656 bytestotal PGA inuse 8293376 bytestotal PGA allocated 13106176 bytesmaximum PGA allocated 22090752 bytestotal freeable PGA memory 0 bytesPGA memory freed back to OS 0 bytestotal PGA used for auto workareas 0 bytesmaximum PGA used for auto workareas 4096 bytestotal PGA used for manual workareas 0 bytesNAME V ALUE UNIT---------------------------------------------------------------- ---------- ---------maximum PGA used for manual workareas 4096 bytesover allocation count 0bytes processed 8783872 bytesextra bytes read/written 0 bytescache hit percentage 100 percent已选择16行。

17 在$ORACLE_HOME/sqlplus/admin/glogin.sql中加入环境变量,以后每次启动生效define _editor=viset line 200018 将当前命令随加到文件中save c:a.sql append19 将指定文件的命读出缓冲区get c:a.sql20 执行脚本语句@ c:a.sql21 将输入保存到指定文件中spool c:O.LOGselect * from v$sga;spool off22 设定行大小set linesize 200023 设定页大小set pagesize 1024 设定字符列格式col ename format a3025 设定数字列格式col sal format 999,999.99926 10G查看文件$ORACLE_HOME/install/protlist显示端口http://127.0.0.1:5560/isqlplus9i查看文件$ORACLE_HOME/Apache/Apache/ports.ini显示端口http://127.0.0.1:7778/isqlplushttp://127.0.0.1:7778/isqlplusdba27 启动Oracle 9i监听程序Oracle的监听程序主要是为客户端的连接提供接口$ lsnrctl start28 关闭Oracle 9i监听程序$ lsnrctl stop29 启动Oracle Web Server$ cd $ORACLE_HOME/Apache/Apache/bin$ ./startJServ.sh/database/oracle/product/9i/Apache/Apache/bin/apachectl start: httpd started30 关闭Oracle Web Server$ cd $ORACLE_HOME/Apache/Apache/bin$ ./stopJServ.sh/database/oracle/product/9i/Apache/Apache/bin/apachectl stop: httpd stopped31 启动Oracle Web Server后默认的端口号是7777unixcd $ORACLE_HOME/Apache/Apache/bin/htpasswd $ORACLE_HOME/sqlplus/admin/iplusdba.pw adminwindowscd D:oracleora92ApacheApachebinhtpasswd D:oracleora92/sqlplus/admin/iplusdba.pw adminNew password: *****Re-type new password: *****Adding password for user admin32 pfile: $ORACLE_BASE/admin/实例名/Pfilespfile: $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 user35 当前用户的缺省表空间SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME=(select user from dual);37 显示正在使用的初始化参数文件show parameter spfileNAME TYPE V ALUE------------------------------------ ----------- ------------------------------spfile string %ORACLE_HOME%DA TABASESPFILE%ORACLE_SID%.ORA38 用SPfile转Pfilecreate pfile='c:init.ora' from spfile;create pfile from spfile;UNIX将生成在$ORACLE_HOME/dbs目录下WINDOWS将生成在$ORACLE_HOME/DA TABASE目录下39 显示cache的值show parameter cache前面两个下划线的是动态调整的参数前面一个下划线的是内部参数40 显示正在使用的参数和文本参数值select * from v$parameter;select * from v$spparameter;41 显示动态性能视图的结构desc v$parameter;名称NUMNAMETYPEV ALUEISDEFAULTISSES_MODIFIABLEISSYS_MODIFIABLEISMODIFIEDISADJUSTEDDESCRIPTIONUPDA TE_COMMENT42 有条件的显示动态参数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的大小保存在SPFILEalter 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 pgaNAME TYPE V ALUE------------------------------------ ----------- ------------------------------pga_aggregate_target big integer 1677721649 带PFILE来启动资料库,?代表ORACLE的主目录startup pfile=?databaseinitmydb.ora50 显示当前数据库的状态,mount的状态下就可以查看数据库的状态select open_mode from v$database;51 显示当前实例nomount状态下就可以查看实例select * from v$instance;52 显示background_dump_dest的路径,show parameter background_dump_destNAME TYPE V ALUE------------------------------------ ----------- ------------------------------ background_dump_dest string D:oracleadminthwerpbdump53 显示当有用户的表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 -1063 显示当前实例SELECT * FROM V$INSTANCE;64 显示当前数据库信息SELECT * FROM V$DA TABASE;65 显示当前ORACLR资料的版本SELECT * FROM V$VERSION;66 显示当前的选项SELECT * FROM V$OPTION;67 显示当前实例的初始化参数SELECT * FROM V$PARAMETER;68 显示数据文件SELECT * FROM V$DA TAFILE;69 显示日志文件SELECT * FROM V$LOGFILE70 显示控制文件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$SGASTA T;76 显示PGA的统计数SELECT * FROM V$PGASTA T;77 显示有哪些控制文件SHOW PARAMETER CONTROL_FILES;78 增加一个控制文件一定要在NOMOUNT状态下进行.语句如下. alter system set control_files='D:ORADA TAMYDBCONTROL01.CTL', 'D:ORADA TAMYDBCONTROL02.CTL','D:ORADA TAMYDBCONTROL03.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 FORCE80 显示当前控制文件SELECT * FROM V$CONTROLFILE;SHOW PARAMETER CONTROL81 删除一个控制文件alter system set control_files='D:ORADA TAMYDBCONTROL01.CTL', 'D:ORADA TAMYDBCONTROL02.CTL','D:ORADA TAMYDBCONTROL03.CTL' SCOPE=SPFILE82 显示当前使用的日志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 DA TABASE ADD LOGFILE GROUP 4 ( 'D:ORADA TAMYDBredo4.log') SIZE 10240K;92 删除日志组ALTER DA TABASE DROP LOGFILE GROUP 4 ;90 增加日志组成员ALTER DA TABASE ADD LOGFILE MEMBER 'D:ORADA TAMYDBredo11.log' TO GROUP 1;91 删除日志组成员ALTER DA TABASE DROP LOGFILE MEMBER 'D:ORADA TAMYDBredo14.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 mfrom dba_data_files group by tablespace_name;96 显示表空间还有多少没有使用select tablespace_name,sum(bytes)/1024/1024 mfrom dba_free_space group by tablespace_name;97 创建表空间10G CREA TE BIGFILE TABLESPACE "MYTBS1" DA TAFILE 'D:ORADA TAMYDBmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;9I CREA TE TABLESPACE "MYTBS1" DA TAFILE 'D:oracleORADA TAthwerpmytbs_01.dbf' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;CREA TE SMALLFILE TABLESPACE "MYTBS1" DA TAFILE 'D:ORADA TAMYDBmytbs_01.dbf' SIZE 100M , 'D:ORADA TAMYDBmytbs02.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 DA TAFILE 'D:ORADA TAMYDBmytbs03.dbf' SIZE 100M100 修改表空间的大小ALTER DA TABASE DA TAFILE 'D:ORADA TAMYDBMYTBS_01.DBF' RESIZE 200M101 表空间改文件的自动扩展ALTER DA TABASE DA TAFILE 'D:ORADA TAMYDBMYTBS03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED102 删除表空间和表空间所属文件drop tablespace mytbs1 including contents and datafiles;103 建立临时表空间CREA TE SMALLFILE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADA TAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1MCREA TE TEMPORARY TABLESPACE "MYTEMP" TEMPFILE 'D:oracleORADA TAthwerpmytemp01.dbf' SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M104 更改用户的临时表空间ALTER USER "GARY" TEMPORARY TABLESPACE "MYTEMP"105 设置成默认的表空间ALTER DA TABASE DEFAULT TEMPORARY TABLESPACE "MYTEMP"106 创建临时表空间组ALTER TABLESPACE MYTEMP TABLESPACE GROUP MYTEMP_GROUPALTER TABLESPACE TEMP TABLESPACE GROUP MYTEMP_GROUP107 创建重做表空间CREA TE UNDO TABLESPACE "MYUNDO" DA TAFILE 'D:ORADA TAMYDBmyundo01.dbf' SIZE 200M108 显示重做参数show parameter undo109 回滚段的使用情况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表空间CREA TE TABLESPACE "MYTBS3" DA TAFILE 'D:oracleORADA TAthwerpmytbs3.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:ORADA TAMYDBusers01.dbf' size 1m);123 指定参数建立表create table C (a int) tablespace system pctfree 10 pctused 60 storage(freelists 2);CREA TE TABLE "SYS"."D" ( "A" V ARCHAR2(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 用包的命令查看表行的物理地址selectDBMS_ROWID.ROWID_RELA TIVE_FNO(ROWID),DBMS_ROWID.ROWID_BLOCK_NUM BER(ROWID),DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) FROM A127 建立表索引CREA TE 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 截取表TRUNCA TE TABLE A;135 删除表DROP TABLE A CASCADE CONSTRAINTS;136 显示垃圾桶(10G才有)show recyclebinSELECT * 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_DA TE TO START_DA TE;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 建立一个普通索引CREA TE INDEX A_NAME_IND ON A(OWNER,OBJECT_NAME);151 建立一个函数索引SELECT /*+ INDEX(A A_NAME2_IND) */ * FROM A WHERE UPPER(OBJECT_NAME)='A';152 建立一个位图索引CREA TE 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='S YS'155 显示所有用户的信息SELECT * FROM DBA_USERS;156 显示当前数据库的用户SELECTUSERNAME,ACCOUNT_STA TUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS;160 建立一个新用户create user edpthw identified by "123456"default tablespace userstemporary tablespace tempquota unlimited on usersquota 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 CREA TE ANY TABLE TO "hmtong"GRANT CREA TE TABLE TO "hmtong"GRANT DROP ANY TABLE TO "hmtong"GRANT SELECT ANY TABLE TO "hmtong"169 折消授权REVOKE UNLIMITED TABLESPACE FROM "hmtong"170 对象授权GRANT SELECT ON "hmtong"."A" TO "edpthw"171 折消对象授权REVOKE SELECT ON "hmtong"."A" FROM "edpthw"172 取消进入系统直接用默认密码,而一定要自己输入密码才可以进入unix $ORACLE_HOMENETWORKADMINSQLNET.ORAwindows D:oracleproduct10.1.0Db_1NETWORKADMINSQLNET.ORA SQLNET.AUTHENTICA TION_SERVICES= (NTS,NONE)修改$ORACLE_HOME/network/admin/sqlnet.ora文件将原有的：SQLNET.AUTHENTICA TION_SERVICES= (NTS)改为:SQLNET.AUTHENTICA TION_SERVICES= (NONE)或者直接注释：#SQLNET.AUTHENTICA TION_SERVICES= (NTS)173 查询Pwfile中存放的用户信息select * from v$pwfile_users;174 授权sysdba给指定用户grant sysdba to hmtong;175 取消指定用户的sysdba权限revoke sysdba from hmtong;176 修改系统的授权的属性alter system set remote_login_passwordfile=exclusive; 能sysdba登录,能授权alter system set remote_login_passwordfile=shared scope=spfile; 只能sysdba登录,不能授权alter system set remote_login_passwordfile=NONE; 取消177 重建口令文件orapwd file=PWDmydb.ora password=itpub178 创建一个用户create user hmtong identified by abcdefg;179 授权连接给指定用户GRANT "CONNECT" TO "hmtong";GRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO HMTONG;180 创建表create table t1(a int);create table t2(a int);181 查看系统特权select * from user_sys_privs;182 查看对象特权select * from user_tab_privs;183 查看被授予的角色select * from user_role_privs;184 查看角色的有什么权限select * from role_sys_privs;185 查看角色的有哪些表select * from role_tab_privs;186 查看当前会话的权限select * from session_privs;187 监听器的状态lsnrctl start$ORACLE_HOME/network/admin/listener.ora188 监听器的启动和关闭lsnrctl start [名字]lsnrctl stop [名字]189 监听器的状态和服务信息lsnrctl statlsnrctl service190 配置监听器的工具netcanetmgr192 动态注册监听器alter system register;193 配置监听器的两个参数show parameter local_listenershow parameter remote_listener194 追加设置一个服务名,服务名可以多个对应一个实例alter system set service_names=mydb,appdb195 客户连接配置文件$ORACLE_HOME/network/admin/tnsname.ora196 检查指定的监听器是否存在TNSPING MYDB197 将SQLNET.ORA配置好就可以connect system/testdb1@192.168.100.80:1521/testdb1方式连接SQLNET.AUTHENTICA TION_SERVICES= (NTS,NONE)NAMES.DIRECTORY_PA TH= (TNSNAMES,EZCONNECT)198 快速重启资料库shutdown immediate199 将资料库开启到mount的状态startup mount200 显示数据库的归档还是非归档模式archive log list数据库日志模式存档模式自动存档禁用存档终点D:oracleora92RDBMS最早的概要日志序列64下一个存档日志序列69当前日志序列69201 在数据库mount的状态下更改数据库的归档模式alter database archivelog; 归档alter database noarchivelog; 非归档202 打开数据库alter database open;203 查看归档的路径的SQL> show parameter log_archive_dest_x204 动态的配置归档路径在两个不同的目录下同时归档alter system set log_archive_dest_1='location=e:archive';alter system set log_archive_dest_2='location=d:archive';205 切换系统的下一个日志文件alter system switch logfile; (10G就会产生归档文件)206 定义自动归档alter system set log_archive_start=true scope=spfile; (9i)(10G不用设)207 查看数据库的归档的信息select * from v$log;208 查看曾经做过哪些归档及路径select * from v$archived_log;209 查看有哪还没有做归档的信息select * from v$archive;210 设置归档路径2暂时不归档和启用归档alter system set log_archive_dest_state_2=defer;alter system set log_archive_dest_state_2=enable;211 查看最少路径归档数目的参数show parameter log_archive_min_succeed_dest212 查看归档路径的文件名格式参数show parameter log_archive_format213 查改归档文件名的格式alter system set log_archive_format='mydb_%s_%r_%t.log' scope=spfile;214 显示出快速恢复区的大小和路径show parameter db_recovery_file_destshow parameter db_recovery_file_dest_size215 更改归档路径到到快速恢复区alter system set log_archive_dest_1='location=use_db_recovery_file_dest';216 更改快速恢复区的路径alter system set db_recovery_file_dest='e:recover';217 查看快速恢复区的使用情况select * from v$recovery_file_dest;218 更改快速恢复区的大小alter system set db_recovery_file_dest_size=10g;219 查看数据库的归档模式select log_mode from v$database;archive log list220 查看归档文件的归档路径show parameter log_archive_destselect * from v$archive_dest;221 查看归档文件select * from v$archive;222 查看曾经归档过的记录select * from v$archived_log;223 启动在线备份表空间alter tablespace users begin backup;224 查询指定表空间的文件路径select file_name from dba_data_files where tablespace_name='USERS';225 在SQL状态上暂时离开返回到SHELLhost ocopy user*.dbf e:backup226 结束生成备份表空间alter tablespace users end backup;227 备份数据库的控制文件alter database backup controlfile to 'e:backupcontrol01.ctl';228 生成创建控制文件的脚本alter database backup controlfile to trace as 'e:backupcontrol01.sql';229 查询哪个文件需要恢复select * from v$recover_file;230 查询指定文件号的详细信息select * from v$datafile where file#=6;231 查询有哪些需要归档select * from v$recovery_log;232 恢复指字的数据文件recover datafile 6;233 创建一个例子表create table a11 tablespace mytbs3 as select * from dba_objects;233 查询有没有指定的文件select * from dba_extents where file_id=6;224 自动找到归档日志恢复相应数据文件recover AUTOMA TIC datafile 6;225 将一个表空间设置成不可用ALTER TABLESPACE USERS OFFLINE IMMEDIA TE226 恢复指定表空间RECOVER TABLESPACE USERS;227 将指定的表文件设置成不可用ALTER DA TABASE DA TAFILE 6 OFFLINE;228 将指定的表文件设置成在线ALTER DA TABASE DA TAFILE 6 ONLINE;229 创建一个测试表CREA TE TABLE TEST (A DA TE);230 插入当前系统日期INSERT INTO TEST V ALUES(SYSDA TE);231 查询TEST的值SELECT * FROM TEST;232 在注册表里的设置日期格式NLS_DA TE_FORMA T=YYYY-MM-DD HH24:MI:SS233 恢复到指定的时间点RECOVER DA TABASE UNTIL TIME '2006-11-26 10:49:43';234 打开资料库并重置重做日志alter database open resetlogs;235 恢复控制文件RECOVER DA TABASE using backup controlfile UNTIL cancel;236 追加临时表空间alter tablespace temp add tempfile 'd:oradatamydbtemp01.dbf' size 10m reuse;237 Rman的连接connect target /238 显示要备份的数据库文件report schema;239 在RMAN的指定位置来备份文件run {allocate channel ch1 type disk format 'e:rman%U.bak' maxpiecesize=2g; backup database;}240 在RMAN的显示威者默认设置show all;backup database;241 配置缺省的路径CONFIGURE CHANNEL DEVICE TYPE DISK FORMA T 'E:RMAN%U.BAK' MAXPIECESIZE 2G;242 清除缺省配置CONFIGURE CHANNEL DEVICE type DISK clear;243 配置并行数CONFIGURE DEVICE TYPE DISK PARALLELISM 2;244 配置自动备份控制文件CONFIGURE CONTROLFILE AUTOBACKUP on;245 配置有益度CONFIGURE RETENTION POLICY TO REDUNDANCY 2;246 配置可以恢复到7天内的保留窗口CONFIGURE RETENTION POLICY TO recovery window of 7;247 备份表空间backup tablespace users;248 备份数据文件backup datafile 6;249 备份前一天的重做日志backup archivelog until time 'sysdate-1';250 备份SP文件backup spfile;251 备份当前的控制文件backup current controlfile;252 列出所有的备份集list backupset ;253 列出所有的汇总备份集list backupset summary;254 列出具体的备份信息list backupset 16255 删除备份集13delete backupset 13;256 不提示删除所有的备份集delete noprompt backupset;257 凡是USERS的备份都删除delete backup of tablespace users;258 列出哪些不需要的备份集report obsolete ;259 边备份边压缩数据库backup as compressed backupset database;260 增量备份时的0级备份一个表空间backup incremental level 0 tablespace users;261 2级备份累积备份backup incremental level 2 cumulative tablespace users;262 删除指定日期的归档日志DELETE ARCHIVELOG UNTIL TIME 'SYSDA TE-1/24';263 在RMAN恢复指定表空间RUN {SQL 'ALTER TABLESPACE USERS OFFLINE IMMEDIA TE'; RESTORE TABLESPACE USERS;RECOVER TABLESPACE USERS;SQL 'ALTER TABLESPACE USERS ONLINE';}264 在RMAN的恢复控制文件STARTUP NOMOUNTRESTORE CONTROLFILE FROM AUTOBACKUP;ALTER DA TABASE MOUNT;RESTORE DA TABASE V ALIDA TE;RECOVER DA TABASE ;ALTER DA TABASE OPEN RESETLOGS;265 查询数据库的字符集select * from database_properties where property_name like 'NLS_CHA%';NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK266 导出数据到一个文件中exp userid=system/oracle file=full.dmp full=yexp userid=system/oracle file=full1.dmp,full2.dmp,full3.dmp filesize=2g full=y direct=y log=full.log feedback=10000267 导出指定用户的表exp userid=system/oracle file=gary.dmp owner=gary,wei direct=y log=garyexp userid=system/oracle file=gary.dmp owner=gary direct=y log=gary.log268 导出指定表exp userid=system/oracle file=gary_a.dmp tables=gary.a direct=y log=gary269 导出指定表exp userid=system/oracle file=gary_a1.dmp tables=gary.a query='where owner=''GARY''' log=gary.LOG270 查看导出的帮助信息exp -help271 删除gary的用户drop user gary cascade;272 重建用户GARYCREA TE USER "GARY" PROFILE "DEFAULT" IDENTIFIED BY"GARY" ACCOUNT UNLOCK273 授权用户GARYGRANT UNLIMITED TABLESPACE,CONNECT,RESOURCE TO "GARY";274 导入用户GARY的数据imp userid=system/oracle fromuser=gary touser=gary show=yimp userid=system/oracle fromuser=gary touser=gary file=full.dmp log=imp.logimp userid=system/oracle fromuser=gary touser=wei file=gary.dmp log=imp.log275 查询状态select object_name,object_type,owner from dba_objects where status='INV ALID';EXEC UTL_RECOMP.RECOMP_PARALLEL276 建立对象,允许可以用这个目录来导入导出create directory mydir as 'e:expdata';。