Oracle maintain sql & sqlplus tools

Oracle SQL常见维护视图

1,用户
all_users,查询所有用户
dba_users,查询user与表空间对应关系

2,表
all_tables,查询表,表空间,用户对应关系
dba_tables,查询表,表空间,用户对应关系

3,归档文件arhived_log
select thread#,sequence#,name,applied from v$archived_log order by thread#,sequence#;
select sequence#,substr(name,63,23) name,first_change# FSCN,first_time,next_change# NSCN,next_time from v$archived_log;
select thread#,sequence#,substr(name,20,18) name,to_char(FIRST_TIME,'yyyy-mm-dd hh24:mi:ss') first_time,NEXT_CHANGE#,to_char(NEXT_TIME,'yyyy-mm-dd hh24:mi:ss') next_time,NEXT_CHANGE#,applied,status from v$archived_log order by first_time;

4,重置parameter参数
alter system reset 参数名 scope=spfile sid='*';

5,以一个表创建另一个表
create table TABLE_NAME1as select * from TABLE_NAME2;

6,Dataguard日志gap
SQL> select * from v$archive_gap;

7,字符集查询
SQL>select * from nls_database_parameters;

8,归档
alter system archive log current;
alter system switch logfile;

sqlplus常用的参数

set设置
(1),set serveroutput on DMBS_OUTPUT能够在sqlplus下正常输出
(2),set linesize 132
(3),set pagesize 100
(4),set sqlprompt "_date _user @ _connect_identifier>" 提示符改变为25-FEB-12 SYS @ hdb>
(5),set colsep | 列之间使用"|"符号分隔开

column设置
(1),definevalue1=idle
    column column1 new_valuevalue1将column1重新命名为变量value1
(2),column value1 format a10 设置列的宽度

时间格式设置
$export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
或者
SQL>alter session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';

其他设置
(1),spool file.txt或者file.xls @file.sql spool off
    将file.sql执行的输出导入至txt或xls文件
(2),sqlplus / as sysdba @file.sql
    执行file.sql
(3),SQL> REMARK XXX表示注释XXX的内容
(4),define_editor=vi表示在vi中编辑sqlplus缓存内容.

例子

(1)将提示符号改变

11-MAR-12 SYS @ hdb>define gname=idle

11-MAR-12 SYS @ hdb>column global_name new_value gname

11-MAR-12 SYS @ hdb>select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name,instr(global_name,'.') dot from global_name);

GLOBAL_NAME

-----------------------------------------------------------------------------------------------------------

sys@HDB

11-MAR-12 SYS @ hdb>set sqlprompt '&gname>'

sys@HDB>conn hr/hr

Connected.

sys@HDB>define gname=idle

sys@HDB>select lower(user)||'@'||substr(global_name,1,decode(dot,0,length(global_name),dot-1)) global_name from (select global_name,instr(global_name,'.') dot from global_name);

GLOBAL_NAME

-----------------------------------------------------------------------------------------------------------

hr@HDB

sys@HDB>set sqlprompt '&gname>'

hr@HDB>