Oracle 10G 命令全集

Oracle 10G 命令全集 2008-12-25 15:52

第一章：日志管理 第二章：表空间管理 第三章：表 第四章：索引 第五章：约束

第六章：LOAD数据 第七章：reorganizing data

第八章： managing password security and resources 第九章：Managing users 第十章：managing privileges 第十一章： manager role

第十二章: BACKUP and RECOVERY

Oracle命令全集

第一章：日志管理 1.forcing log switches

sql> alter system switch logfile; 2.forcing checkpoints

sql> alter system checkpoint; 3.adding online redo log groups

sql> alter database add logfile [group 4]

sql> ('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m; 4.adding online redo log members sql> alter database add logfile member sql> '/disk3/log1b.rdo' to group 1, sql> '/disk4/log2b.rdo' to group 2;

5.changes the name of the online redo logfile

sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log' sql> to 'c:/oracle/oradata/redo01.log'; 6.drop online redo log groups

sql> alter database drop logfile group 3; 7.drop online redo log members

sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log'; 8.clearing online redo log files

sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo'; 9.using logminer analyzing redo logfiles a. in the init.ora specify utl_file_dir = ' '

b. sql> execute dbms_logmnr_d.build('oradb.ora','c:oracleoradblog');

c. sql> execute dbms_logmnr_add_logfile('c:oracleoradataoradbredo01.log', sql> dbms_logmnr.new);

d. sql> execute dbms_logmnr.add_logfile('c:oracleoradataoradbredo02.log',

sql> dbms_logmnr.addfile);

e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:oracleoradblogoradb.ora'); f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters sql> v$logmnr_logs);

g. sql> execute dbms_logmnr.end_logmnr;

第二章：表空间管理 1.create tablespaces

sql> create tablespace tablespace_name datafile 'c:oracleoradatafile1.dbf' size 100m, sql> 'c:oracleoradatafile2.dbf' size 100m minimum extent 550k [logging/nologging] sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0) sql> [online/offline] [permanent/temporary] [extent_management_clause] 2.locally managed tablespace

sql> create tablespace user_data datafile 'c:oracleoradatauser_data01.dbf' sql> size 500m extent management local uniform size 10m; 3.temporary tablespace

sql> create temporary tablespace temp tempfile 'c:oracleoradatatemp01.dbf' sql> size 500m extent management local uniform size 10m; 4.change the storage setting

sql> alter tablespace app_data minimum extent 2m;

sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999); 5.taking tablespace offline or online sql> alter tablespace app_data offline; sql> alter tablespace app_data online; 6.read_only tablespace

sql> alter tablespace app_data read only|write; 7.droping tablespace

sql> drop tablespace app_data including contents; 8.enableing automatic extension of data files

sql> alter tablespace app_data add datafile 'c:oracleoradataapp_data01.dbf'size 200m sql> autoextend on next 10m maxsize 500m; 9.change the size fo data files manually

sql> alter database datafile 'c:oracleoradataapp_data.dbf'resize 200m; 10.Moving data files: alter tablespace

sql> alter tablespace app_data rename datafile 'c:oracleoradataapp_data.dbf' sql> to 'c:oracleapp_data.dbf'; 11.moving data files:alter database

sql> alter database rename file 'c:oracleoradataapp_data.dbf' sql> to 'c:oracleapp_data.dbf';

第三章：表

1.create a table

sql> create table table_name (column datatype,column datatype]....) sql> tablespace tablespace_name [pctfree integer] [pctused integer] sql> [initrans integer] [maxtrans integer]

sql> storage(initial 200k next 200k pctincrease 0 maxextents 50) sql> [logging|nologging] [cache|nocache] 2.copy an existing table

sql> create table table_name [logging|nologging] as subquery 3.create temporary table

sql> create global temporary table xay_temp as select * from xay; on commit preserve rows/on commit delete rows

4.pctfree = (average row size - initial row size) *100 /average row size pctused = 100-pctfree- (average row size*100/available data space) 5.change storage and block utilization parameter

sql> alter table table_name pctfree=30 pctused=50 storage(next 500k sql> minextents 2 maxextents 100); 6.manually allocating extents

sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf'); 7.move tablespace

sql> alter table employee move tablespace users; 8.deallocate of unused space

sql> alter table table_name deallocate unused [keep integer] 9.truncate a table

sql> truncate table table_name; 10.drop a table

sql> drop table table_name [cascade constraints]; 11.drop a column

sql> alter table table_name drop column comments cascade constraints checkpoint 1000; alter table table_name drop columns continue; 12.mark a column as unused

sql> alter table table_name set unused column comments cascade constraints; alter table table_name drop unused columns checkpoint 1000; alter table orders drop columns continue checkpoint 1000 data_dictionary : dba_unused_col_tabs

第四章：索引

1.creating function-based indexes

sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped); 2.create a B-tree index

sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer] sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0 sql> maxextents 50);

3.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows

4.creating reverse key indexes

sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k sql> next 200k pctincrease 0 maxextents 50) tablespace indx; 5.create bitmap index

sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k sql> pctincrease 0 maxextents 50) tablespace indx; 6.change storage parameter of index

sql> alter index xay_id storage (next 400k maxextents 100); 7.allocating index space

sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf'); 8.alter index xay_id deallocate unused;

第五章：约束

1.define constraints as immediate or deferred

sql> alter session set constraint[s] = immediate/deferred/default; set constraint[s] constraint_name/all immediate/deferred; 2. sql> drop table table_name cascade constraints

sql> drop tablespace tablespace_name including contents cascade constraints 3. define constraints while create a table

sql> create table xay(id number(7) constraint xay_id primary key deferrable sql> using index storage(initial 100k next 100k) tablespace indx); primary key/unique/references table(column)/check 4.enable constraints

sql> alter table xay enable novalidate constraint xay_id; 5.enable constraints

sql> alter table xay enable validate constraint xay_id;

第六章：LOAD数据

1.loading data using direct_load insert

sql> insert /*+append */ into emp nologging sql> select * from emp_old; 2.parallel direct-load insert

sql> alter session enable parallel dml;

sql> insert /*+parallel(emp,2) */ into emp nologging sql> select * from emp_old; 3.using sql*loader sql> sqlldr scott/tiger sql> control = ulcase6.ctl

sql> log = ulcase6.log direct=true

第七章：reorganizing data 1.using expoty

$exp scott/tiger tables(dept,emp) file=c:emp.dmp log=exp.log compress=n direct=y 2.using import

$imp scott/tiger tables(dept,emp) file=emp.dmp log=imp.log ignore=y 3.transporting a tablespace

sql>alter tablespace sales_ts read only;

$exp sys/.. file=xay.dmp transport_tablespace=y tablespace=sales_ts triggers=n constraints=n $copy datafile

$imp sys/.. file=xay.dmp transport_tablespace=y datafiles=(/disk1/sles01.dbf,/disk2 /sles02.dbf)

sql> alter tablespace sales_ts read write; 4.checking transport set

sql> DBMS_tts.transport_set_check(ts_list =>'sales_ts' ..,incl_constraints=>true); 在表transport_set_violations 中查看

sql> dbms_tts.isselfcontained 为true 是， 表示自包含

第八章： managing password security and resources 1.controlling account lock and password

sql> alter user juncky identified by oracle account unlock; 2.user_provided password function

sql> function_name(userid in varchar2(30),password in varchar2(30), old_password in varchar2(30)) return boolean 3.create a profile : password setting

sql> create profile grace_5 limit failed_login_attempts 3 sql> password_lock_time unlimited password_life_time 30

sql>password_reuse_time 30 password_verify_function verify_function sql> password_grace_time 5; 4.altering a profile

sql> alter profile default failed_login_attempts 3

sql> password_life_time 60 password_grace_time 10; 5.drop a profile

sql> drop profile grace_5 [cascade]; 6.create a profile : resource limit

sql> create profile developer_prof limit sessions_per_user 2 sql> cpu_per_session 10000 idle_time 60 connect_time 480; 7. view => resource_cost : alter resource cost dba_Users,dba_profiles 8. enable resource limits

sql> alter system set resource_limit=true;

第九章：Managing users

1.create a user: database authentication

sql> create user juncky identified by oracle default tablespace users sql> temporary tablespace temp quota 10m on data password expire sql> [account lock|unlock] [profile profilename|default]; 2.change user quota on tablespace

sql> alter user juncky quota 0 on users; 3.drop a user

sql> drop user juncky [cascade]; 4. monitor user

view: dba_users , dba_ts_quotas

第十章：managing privileges

1.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs 2.grant system privilege

sql> grant create session,create table to managers; sql> grant create session to scott with admin option;

with admin option can grant or revoke privilege from any user or role; 3.sysdba and sysoper privileges:

sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile, alter tablespace begin/end backup,recover database alter database archivelog,restricted session

sysdba: sysoper privileges with admin option,create database,recover database until 4.password file members: view:=> v$pwfile_users

5.O7_dictionary_accessibility =true restriction access to view or tables in other schema 6.revoke system privilege

sql> revoke create table from karen; sql> revoke create session from scott; 7.grant object privilege

sql> grant execute on dbms_pipe to public;

sql> grant update(first_name,salary) on employee to karen with grant option; 8.display object privilege : view => dba_tab_privs, dba_col_privs 9.revoke object privilege

sql> revoke execute on dbms_pipe from scott [cascade constraints]; 10.audit record view :=> sys.aud$ 11. protecting the audit trail

sql> audit delete on sys.aud$ by access; 12.statement auditing sql> audit user;

13.privilege auditing

sql> audit select any table by summit by access; 14.schema object auditing

sql> audit lock on summit.employee by access whenever successful;

15.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts, dba_obj_audit_opts

16.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object, dba_audit_session,dba_audit_statement

第十一章： manager role 1.create roles

sql> create role sales_clerk;

sql> create role hr_clerk identified by bonus; sql> create role hr_manager identified externally; 2.modify role

sql> alter role sales_clerk identified by commission; sql> alter role hr_clerk identified externally; sql> alter role hr_manager not identified; 3.assigning roles

sql> grant sales_clerk to scott; sql> grant hr_clerk to hr_manager;

sql> grant hr_manager to scott with admin option; 4.establish default role

sql> alter user scott default role hr_clerk,sales_clerk; sql> alter user scott default role all;

sql> alter user scott default role all except hr_clerk; sql> alter user scott default role none; 5.enable and disable roles sql> set role hr_clerk;

sql> set role sales_clerk identified by commission; sql> set role all except sales_clerk; sql> set role none;

6.remove role from user

sql> revoke sales_clerk from scott; sql> revoke hr_manager from public; 7.remove role

sql> drop role hr_manager; 8.display role information

view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs, role_sys_privs,role_tab_privs,session_roles

第十二章: BACKUP and RECOVERY

1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat

2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size 3. Monitoring Parallel Rollback

> v$fast_start_servers , v$fast_start_transactions 4.perform a closed database backup (noarchivelog) > shutdown immediate > cp files /backup/ > startup

5.restore to a different location

> connect system/manager as sysdba > startup mount

> alter database rename file '/disk1/../user.dbf'to '/disk2/../user.dbf'; > alter database open; 6.recover syntax

--recover a mounted database >recover database;

>recover datafile '/disk1/data/df2.dbf'; >alter database recover database; --recover an opened database >recover tablespace user_data; >recover datafile 2;

>alter database recover datafile 2;

7.how to apply redo log files automatically >set autorecovery on

>recover automatic datafile 4; 8.complete recovery:

--method 1(mounted databae)

>copy c:backupuser.dbf c:oradatauser.dbf >startup mount

>recover datafile 'c:oradatauser.dbf; >alter database open;

--method 2(opened database,initially opened,not system or rollback datafile)

>copy c:backupuser.dbf c:oradatauser.dbf (alter tablespace offline) >recover datafile 'c:oradatauser.dbf' or >recover tablespace user_data;

>alter database datafile 'c:oradatauser.dbf' online or >alter tablespace user_data online;

--method 3(opened database,initially closed not system or rollback datafile) >startup mount

>alter database datafile 'c:oradatauser.dbf' offline; >alter database open

>copy c:backupuser.dbf d:oradatauser.dbf

>alter database rename file 'c:oradatauser.dbf'to 'd:oradatauser.dbf'

>recover datafile 'e:oradatauser.dbf' or recover tablespace user_data; >alter tablespace user_data online;

--method 4(loss of data file with no backup and have all archive log) >alter tablespace user_data offline immediate;

>alter database create datafile 'd:oradatauser.dbf'as 'c:oradatauser.dbf'' >recover tablespace user_data; >alter tablespace user_data online 5.perform an open database backup

> alter tablespace user_data begin backup; > copy files /backup/

> alter database datafile '/c:/../data.dbf' end backup; > alter system switch logfile; 6.backup a control file

> alter database backup controlfile to 'control1.bkp'; > alter database backup controlfile to trace; 7.recovery (noarchivelog mode) > shutdown abort > cp files > startup

8.recovery of file in backup mode >alter database datafile 2 end backup; 9.clearing redo log file

>alter database clear unarchived logfile group 1;

>alter database clear unarchived logfile group 1 unrecoverable datafile; 10.redo log recovery

>alter database add logfile group 3 'c:oradataredo03.log'size 1000k; >alter database drop logfile group 1; >alter database open;

or >cp c:oradataredo02.log' c:oradataredo01.log >alter database clear logfile 'c:oradatalog01.log'; Oracle 10G 命令全集2 2008-12-25 15:54

1. 执行一个SQL脚本文件 SQL>start file_name SQL>@ file_name

我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。

2. 对当前的输入进行编辑 SQL>edit

3. 重新运行上一次运行的sql语句 SQL>/

4. 将显示的内容输出到指定文件 SQL> SPOOL file_name

在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。

5. 关闭spool输出 SQL> SPOOL OFF

只有关闭spool输出，才会在输出文件中看到输出的内容。

6．显示一个表的结构 SQL> desc table_name

7. COL命令：

主要格式化列的显示形式。 该命令有许多选项，具体如下：

COL[UMN] [{ column|expr} [ option ...]] Option选项可以是如下的子句: ALI[AS] alias CLE[AR]

FOLD_A[FTER] FOLD_B[EFORE] FOR[MAT] format HEA[DING] text

JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} LIKE { expr|alias} NEWL[INE]

NEW_V[ALUE] variable NOPRI[NT]|PRI[NT] NUL[L] text

OLD_V[ALUE] variable ON|OFF

WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]

1). 改变缺省的列标题

COLUMN column_name HEADING column_heading For example:

Sql>select * from dept;

DEPTNO DNAME LOC ---------- ---------------------------- ---------

10 ACCOUNTING NEW YORK sql>col LOC heading location sql>select * from dept;

DEPTNO DNAME location --------- ---------------------------- -----------

10 ACCOUNTING NEW YORK

2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上： Sql>select * from emp

Department name Salary ---------- ---------- ----------

10 aaa 11

SQL> COLUMN ENAME HEADING ’Employee|Name’ Sql>select * from emp Employee

Department name Salary ---------- ---------- ---------- 10 aaa 11

note: the col heading turn into two lines from one line.

3). 改变列的显示长度： FOR[MAT] format

Sql>select empno,ename,job from emp; EMPNO ENAME JOB ---------- ---------- ---------

7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN Sql> col ename format a40

EMPNO ENAME JOB

---------- ---------------------------------------- --------- 7369 SMITH CLERK

7499 ALLEN SALESMAN 7521 WARD SALESMAN

4). 设置列标题的对齐方式

JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]} SQL> col ename justify center SQL> /

EMPNO ENAME JOB

---------- ---------------------------------------- --------- 7369 SMITH CLERK

7499 ALLEN SALESMAN 7521 WARD SALESMAN

对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边

5). 不让一个列显示在屏幕上 NOPRI[NT]|PRI[NT] SQL> col job noprint SQL> /

EMPNO ENAME

---------- ---------------------------------------- 7369 SMITH 7499 ALLEN 7521 WARD

6). 格式化NUMBER类型列的显示： SQL> COLUMN SAL FORMAT $99,990 SQL> / Employee

Department Name Salary Commission ---------- ---------- --------- ---------- 30 ALLEN $1,600 300

7). 显示列值时，如果列值为NULL值，用text值代替NULL值 COMM NUL[L] text

SQL>COL COMM NUL[L] text

8). 设置一个列的回绕方式

WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED] COL1 -------------------- HOW ARE YOU?

SQL>COL COL1 FORMAT A5 SQL>COL COL1 WRAPPED COL1 ----- HOW A RE YO U?

SQL> COL COL1 WORD_WRAPPED COL1 ----- HOW ARE YOU?

SQL> COL COL1 WORD_WRAPPED COL1 ----- HOW A

9). 显示列的当前的显示属性值 SQL> COLUMN column_name

10). 将所有列的显示属性设为缺省值 SQL> CLEAR COLUMNS

8. 屏蔽掉一个列中显示的相同的值 BREAK ON break_column SQL> BREAK ON DEPTNO

SQL> SELECT DEPTNO, ENAME, SAL FROM EMP

WHERE SAL < 2500 ORDER BY DEPTNO;

DEPTNO ENAME SAL ---------- ----------- --------- 10 CLARK 2450 MILLER 1300

20 SMITH 800 ADAMS 1100

9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。

BREAK ON break_column SKIP n

SQL> BREAK ON DEPTNO SKIP 1 SQL> /

DEPTNO ENAME SAL ---------- ----------- --------- 10 CLARK 2450 MILLER 1300

20 SMITH 800 ADAMS 1100

10. 显示对BREAK的设置 SQL> BREAK

11. 删除6、7的设置 SQL> CLEAR BREAKS

12. Set 命令：

该命令包含许多子命令： SET system_variable value

system_variable value 可以是如下的子句之一：

APPI[NFO]{ON|OFF|text} ARRAY[SIZE] {15|n}

AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n} AUTOP[RINT] {ON|OFF} AUTORECOVERY [ON|OFF]

AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] BLO[CKTERMINATOR] {.|c} CMDS[EP] {;|c|ON|OFF} COLSEP {_|text}

COM[PATIBILITY]{V7|V8|NATIVE} CON[CAT] {.|c|ON|OFF} COPYC[OMMIT] {0|n}

COPYTYPECHECK {ON|OFF} DEF[INE] {&|c|ON|OFF}

DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}] ECHO {ON|OFF}

EDITF[ILE] file_name[.ext] EMB[EDDED] {ON|OFF} ESC[APE] {\\|c|ON|OFF} FEED[BACK] {6|n|ON|OFF}

FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL} FLU[SH] {ON|OFF} HEA[DING] {ON|OFF} HEADS[EP] {||c|ON|OFF}

INSTANCE [instance_path|LOCAL] LIN[ESIZE] {80|n} LOBOF[FSET] {n|1}

LOGSOURCE [pathname] LONG {80|n}

LONGC[HUNKSIZE] {80|n}

MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}] NEWP[AGE] {1|n|NONE} NULL text

NUMF[ORMAT] format NUM[WIDTH] {10|n} PAGES[IZE] {24|n} PAU[SE] {ON|OFF|text}

RECSEP {WR[APPED]|EA[CH]|OFF} RECSEPCHAR {_|c}

SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_ WRAPPED]|TRU[NCATED]}]

SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]} SHOW[MODE] {ON|OFF}

SQLBL[ANKLINES] {ON|OFF}

SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]} SQLCO[NTINUE] {> |text} SQLN[UMBER] {ON|OFF} SQLPRE[FIX] {#|c}

SQLP[ROMPT] {SQL>|text}

SQLT[ERMINATOR] {;|c|ON|OFF} SUF[FIX] {SQL|text} TAB {ON|OFF}

TERM[OUT] {ON|OFF} TI[ME] {ON|OFF} TIMI[NG] {ON|OFF} TRIM[OUT] {ON|OFF} TRIMS[POOL] {ON|OFF} UND[ERLINE] {-|c|ON|OFF} VER[IFY] {ON|OFF} WRA[P] {ON|OFF}

1). 设置当前session是否对修改的数据进行自动提交 SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}

2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句 SQL> SET ECHO {ON|OFF}

3).是否显示当前sql语句查询或修改的行数 SQL> SET FEED[BACK] {6|n|ON|OFF}

默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数

4).是否显示列标题

SQL> SET HEA[DING] {ON|OFF}

当set heading off 时，在每页的上面不显示列标题，而是以空白行代替

5).设置一行可以容纳的字符数 SQL> SET LIN[ESIZE] {80|n}

如果一行的输出内容大于设置的一行可容纳的字符数，则折行显示。

6).设置页与页之间的分隔

SQL> SET NEWP[AGE] {1|n|NONE}

当set newpage 0 时，会在每页的开头有一个小的黑方框。 当set newpage n 时，会在页和页之间隔着n个空行。 当set newpage none 时，会在页和页之间没有任何间隔。

7).显示时，用text值代替NULL值

SQL> SET NULL text

8).设置一页有多少行数

SQL> SET PAGES[IZE] {24|n}

如果设为0，则所有的输出内容为一页并且不显示列标题

9).是否显示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。 SQL> SET SERVEROUT[PUT] {ON|OFF}

在编写存储过程时，我们有时会用dbms_output.put_line将必要的信息输出，以便对存储过程进行调试，只有将serveroutput变量设为on后，信息才能显示在屏幕上。

10).当SQL语句的长度大于LINESIZE时，是否在显示时截取SQL语句。 SQL> SET WRA[P] {ON|OFF}

当输出的行的长度大于设置的行的长度时（用set linesize n命令设置），当set wrap on时，输出行的多于的字符会另起一行显示，否则，会将输出行的多于字符切除，不予显示。

11).是否在屏幕上显示输出的内容，主要用与SPOOL结合使用。 SQL> SET TERM[OUT] {ON|OFF}

在用spool命令将一个大表中的内容输出到一个文件中时，将内容输出在屏幕上会耗费大量的时间，设置set termspool off后，则输出的内容只会保存在输出文件中，不会显示在屏幕上，极大的提高了spool的速度。

12).将SPOOL输出中每行后面多余的空格去掉 SQL> SET TRIMS[OUT] {ON|OFF}

13)显示每个sql语句花费的执行时间 set TIMING {ON|OFF}

14．修改sql buffer中的当前行中，第一个出现的字符串 C[HANGE] /old_value/new_value SQL> l

1* select * from dept SQL> c/dept/emp

1* select * from emp

15．编辑sql buffer中的sql语句 EDI[T]

16．显示sql buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行 L[IST] [n]

17．在sql buffer的当前行下面加一行或多行 I[NPUT]

18．将指定的文本加到sql buffer的当前行后面 A[PPEND]

SQL> select deptno, 2 dname 3 from dept;

DEPTNO DNAME ---------- --------------

10 ACCOUNTING 20 RESEARCH 30 SALES

40 OPERATIONS

SQL> L 2 2* dname SQL> a ,loc 2* dname,loc SQL> L

1 select deptno, 2 dname,loc 3* from dept SQL> /

DEPTNO DNAME LOC ---------- -------------- -------------

10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

19．将sql buffer中的sql语句保存到一个文件中 SAVE file_name

20．将一个文件中的sql语句导入到sql buffer中 GET file_name

21．再次执行刚才已经执行的sql语句 RUN or /

22．执行一个存储过程 EXECUTE procedure_name

23．在sql*plus中连接到指定的数据库

CONNECT user_name/passwd@db_alias

24．设置每个报表的顶部标题 TTITLE

25．设置每个报表的尾部标题 BTITLE

26．写一个注释 REMARK [text]

27．将指定的信息或一个空行输出到屏幕上 PROMPT [text]

28．将执行的过程暂停，等待用户响应后继续执行 PAUSE [text]

Sql>PAUSE Adjust paper and press RETURN to continue. 29．将一个数据库中的一些数据拷贝到另外一个数据库（如将一个表的数据拷贝到另一个数据库）

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query

sql>COPY FROM SCOTT/TIGER@HQ TO JOHN/CHROME@WEST create emp_temp

USING SELECT * FROM EMP

30．不退出sql*plus，在sql*plus中执行一个操作系统命令： HOST

Sql> host hostname

该命令在windows下可能被支持。

31．在sql*plus中，切换到操作系统命令提示符下，运行操作系统命令后，可以再次切换回sql*plus： !

sql>!

$hostname $exit sql>

该命令在windows下不被支持。

32．显示sql*plus命令的帮助 HELP

如何安装帮助文件：

Sql>@ ?\\sqlplus\\admin\\help\\hlpbld.sql ?\\sqlplus\\admin\\help\\helpus.sql Sql>help index

33．显示sql*plus系统变量的值或sql*plus环境变量的值 Syntax

SHO[W] option

where option represents one of the following terms or clauses: system_variable ALL

BTI[TLE]

ERR[ORS] [{FUNCTION|PROCEDURE|PACKAGE|PACKAGE BODY| TRIGGER|VIEW|TYPE|TYPE BODY} [schema.]name] LNO

PARAMETERS [parameter_name] PNO

REL[EASE] REPF[OOTER] REPH[EADER] SGA SPOO[L] SQLCODE TTI[TLE] USER

1) . 显示当前环境变量的值： Show all

2) . 显示当前在创建函数、存储过程、触发器、包等对象的错误信息 Show error

当创建一个函数、存储过程等出错时，变可以用该命令查看在那个地方出错及相应的出错信息，进行修改后再次进行编译。

3) . 显示初始化参数的值：

show PARAMETERS [parameter_name]

4) . 显示数据库的版本： show REL[EASE]

5) . 显示SGA的大小

show SGA

6). 显示当前的用户名 show user

本文来源：https://www.bwwdw.com/article/pgap.html