oracle常用命令

一、日志相关

SQL> SELECT * FROM v$log;     --查看重做日志组
SQL> SELECT * FROM v$logfile; --查看日志组成员

SQL> ALTER DATABASE ADD LOGFILE 
               GROUP 4 ('d:\redolog04a.log', 'd:\redolog04b.log') SIZE 100M; --添加日志组

SQL> ALTER DATABASE ADD LOGFILE
               MEMBER 'd:\redolog04c.log' TO GROUP 4;   --添加日志组成员

SQL> ALTER DATABASE DROP LOGFILE GROUP 4; --删除日志组
SQL> ALTER DATABASE DROP LOGFILE MEMBER 'd:\redolog04b.log'; --删除日志组成员

SQL> SELECT name,log_mode FROM v$database; --查看归档模式状态
SQL> ARCHIVE LOG LIST --查看归档模式状态

SQL> ALTER SYSTEM SET log_archive_dest_1='location=d:\archive_log'; --设置归档日志路径
SQL> ALTER SYSTEM SET log_archive_dest_2='location=e:\archive_log'; --添加归档日志路径
SQL> ALTER SYSTEM SET log_archive_max_processes = 5; --设置归档进程数

SQL> ALTER SYSTEM SET 
           log_archive_format = "archive_%t_%s_%r.log" scope=spfile; --修改日志文件命名格式

SQL> SHUTDOWN IMMEDIATE --关闭数据库
SQL> STARTUP MOUNT      --加载数据库
SQL> ALTER DATABASE ARCHIVELOG; --启用归档模式
SQL> ALTER DATABASE OPEN;       --打开数据库
SQL> ALTER SYSTEM SWITCH LOGFILE;  --强制日志切换
SQL> SELECT * FROM v$archived_log; --查看已归档日志

--查看补全日志是否开启
SQL> SELECT supplemental_log_data_min,
            supplemental_log_data_pk,
            supplemental_log_data_ui,
            supplemental_log_data_fk,
            supplemental_log_data_all
       FROM v$database;

--添加/删除数据库补全日志的语法
SQL> ALTER DATABASE
     {ADD|DROP}
     SUPPLEMENTAL LOG DATA
     [([ALL] [,PRIMARY KEY] [,UNIQUE INDEX] [,FOREIGN KEY])] COLUMNS];

--示例1:
SQL> ALTER DATABASE
     ADD SUPPLEMENTAL LOG DATA;

--示例2:
SQL> ALTER DATABASE
     ADD SUPPLEMENTAL LOG DATA
     (PRIMARY KEY, FOREIGN KEY, UNIQUE INDEX) COLUMNS;

--添加/删除表级组合补全日志组语法
SQL> ALTER TABLE
     {table_name}
     {ADD|DROP}
     SUPPLEMENTAL LOG DATA
     [([ALL] [,PRIMARY KEY] [,UNIQUE INDEX] [,FOREIGN KEY]) COLUMNS];

--添加/删除表级命名补全日志组语法
SQL> ALTER TABLE
     {table_name}
     {ADD|DROP}
     SUPPLEMENTAL LOG GROUP
     {group_name}
     (column_1 [NO LOG] [,column_2] [NO LOG] [,column_n] [NO LOG])
     [ALWAYS];

--示例:
SQL> ALTER TABLE
     order
     ADD SUPPLEMENTAL LOG GROUP
     log_grp_order(orderid, crdt NO LOG, status)
     ALWAYS;

--查看表级命名补全日志组
SQL> SELECT * FROM dba_log_groups; 
SQL> SELECT * FROM dba_log_group_columns;

SQL> CREATE PFILE [='pfile_name' ]
       FROM {{SPFILE [='spfile_name']}|MEMORY};
SQL> CREATE SPFILE [='spfile_name']
       FROM {{PFILE [='pfile_name']}|MEMORY}

未完待续。。。