A oracle日常维护sql

oracle日常维护sql

//数据库信息搜集

sql>spool ~/sqltext.txt //" ~ "代表具体的路径,生成一个文本

sql>select version from product_component_version where SUBSTR(PRODUCT,1,6)='Oracle';

sql>show parameter

sql>show parameter spfile

sql>select instance_name,status from v$instance;

sql>select name from v$database;

sql>select name from v$datafile;

sql>select name from v$controlfile;

sql>select member from v$logfile;

sql>select tablespace_name,file_name from dba_tablespaces;

sql>select name from v$tempfile;

sql>col PARAMETER format a20

//字符集

sql>select * from v$nls_database_parameter where parameter='NLS_CHARACTERSET';

//数据文件信息

col FILE_NAME for a50

col TABLESPACE_NAME for a30

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 "BYTES(M)",USER_BYTES/1024/1024 USER_BYTES,status from dba_data_files order by file_name;

//临时数据文件信息

col FILE_NAME for a50

col TABLESPACE_NAME for a30

select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024 "BYTES(M)",USER_BYTES/1024/1024 USER_BYTES,status from dba_temp_files order by file_name ;

//用户信息

col USERNAME format a20

col DEFAULT_TABLESPACE format a20

select username,default_tablespace,temporary_tablespace from dba_users;

//回滚段状态

sql>select segment_name,tablespace_name,status from dba_rollback_segs;

//日志文件信息

sql>col MEMBER format a50

sql>select a.member,a.group#,b.thread#,b.members,b.status from v$logfile a,v$log b where a.group#=b.group#;

第1页下一页