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>