1、为什么要使用Startup Migrate
STARTUP MIGRATE was introduced in 9.2 as a mechanism to be sure that most everything that needs to be done to run an upgrade script or a patch script is done automatically. In the past, customers were expected to adjust certain initialization parameters prior to beginning an upgrade or applying a a patch, but most of this is now done automatically by STARTUP MIGRATE. When a customer starts a database in MIGRATE mode, the following ALTER SYSTEM commands will be set automatically:
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET “_SYSTEM_TRIG_ENABLED”=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE=MEMORY;
ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE=MEMORY;
这些我们可以从数据库的Startup Migrate命令启动数据库的日志中可以看出来

Sun Oct  9 21:53:04 2011
ALTER SYSTEM enable restricted session;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _system_trig_enabled=FALSE SCOPE=MEMORY;
Autotune of undo retention is turned off. 
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET _undo_autotune=FALSE SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET undo_retention=900 SCOPE=MEMORY;
MMNL started with pid=12, OS id=8452
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET aq_tm_processes=0 SCOPE=MEMORY;
Sun Oct  9 21:53:04 2011
Resource Manager disabled during database migration: plan '' not set
Sun Oct  9 21:53:04 2011
ALTER SYSTEM SET resource_manager_plan='' SCOPE=MEMORY;
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN MIGRATE

2、Startup Migrate主用作用
在9i，无论升级/降级 数据库都是startup migrate
10g后增加了upgrade参数，升级可直接用startup upgrade，降级仍是startup migrate

3、Startup Migrate辅助作用（解决部分ORA-00701）

SQL> alter index I_H_OBJ#_COL# rebuild;
alter index I_H_OBJ#_COL# rebuild
*
ERROR at line 1:
ORA-00701: object necessary for warmstarting database cannot be altered

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup migrate;
ORACLE instance started.
Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> alter index I_H_OBJ#_COL# rebuild;

Index altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 139531744 bytes
Fixed Size 452064 bytes
Variable Size 121634816 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

1、sqlplus显示语言设置
export NLS_LANG=”AMERICAN_AMERICA.ZHS16GBK”
export NLS_LANG=”SIMPLIFIED CHINESE_CHINA.ZHS16GBK”
注意数据库编码：ZHS16GBK/UTF8

2、dbms_output.put_line显示最前面空格
SQL> set serveroutput on
SQL> exec dbms_output.put_line(‘     abc’);
abc

PL/SQL procedure successfully completed.

SQL> set serveroutput on format wrapped
SQL> exec dbms_output.put_line(‘   abc’);
   abc

PL/SQL procedure successfully completed.

3、sqlplus 语句中间查询对象结构
SQL> select owner
2 # desc input
Name Null? Type
—————————————– ——– —————————-
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

2 ,status from input where rownum<10;

no rows selected
下一行以#开头, 就可以执行一条sql*plus命令, 执行完后, 刚才的语句可以继续输入

4、Sql*plus中sql语句中间有空行
SQL> select owner
2
SQL> ,status from input where rownum<10; SP2-0734: unknown command beginning “,status fr…” – rest of line ignored. 原因是sqlplus遇到空行就认为是语句结束了. 其实要改变这种现象, 只要使用SQLBLANKLINES参数就可以了 SQL> SET SQLBLANKLINES ON
SQL> select owner
2
3 ,status from input where rownum<10;

no rows selected