IDC资讯中心
1． 查看本用户下的各种对象的sql脚本
sql语句：
表：
select * from cat;
select * from tab;
select table_name from user_tables;
视图：
select text from user_views where view_name=upper(&view_name);
索引：
select index_name,table_owner,table_name,tablespace_name,status from user_indexes order by table_name;
触发器：
select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;
快照：
select owner,name,master,table_name,last_refresh,next from user_snapshots order by owner,next;
同义词：
select * from syn;
序列：
select * from seq;
数据库链路：
select * from user_db_links;
约束限制：
select table_name,constraint_name,search_condition,status from user_constraints;
本用户读取其他用户对象的权限：
select * from user_tab_privs;
本用户所拥有的系统权限：
select * from user_sys_privs;
用户：
select * from all_users order by user_id;
表空间剩余自由空间情况：
select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space group by tablespace_name；
数据字典：
select table_name from dict order by table_name;
锁及资源信息：
select * from v$lock;不包括ddl锁
数据库字符集：
select name,value$ from props$ where name=nls_characterset;
inin.ora参数：
select name,value from v$parameter order by name;
sql共享池：
select sql_text from v$sqlarea;
数据库：
select * from v$database
控制文件：
select * from v$controlfile;
重做日志文件信息：
select * from v$logfile;
来自控制文件中的日志文件信息：
select * from v$log;
来自控制文件中的数据文件信息：
select * from v$datafile;
nls参数当前值：
select * from v$nls_parameters;
oracle版本信息：
select * from v$version;
描述后台进程：
select * from v$bgprocess;
查看版本信息：
select * from product_component_version;
2． 监控数据库
select 查看数据库的版本 from dual;
select version from product_component_version where substr(product,1,6)=oracle;
select 查看数据库库对象 from dual;
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
select 查看数据库的创建日期和归档方式 from dual;
select created, log_mode, log_mode from v$database;
select 查看控制文件 from dual;
select name from v$controlfile;
select 查看表空间的名称及大小 from dual;
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name;
select 查看表空间物理文件的名称及大小 from dual;
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;
select 查看表空间的使用情况 from dual;
select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space group by tablespace_name ;
select a.tablespace_name,a.bytes total,b.bytes used, c.bytes free, (b.bytes*100)/a.bytes “% used”,(c.bytes*100)/a.bytes “% free”
from sys.sm$ts_avail a,sys.sm$ts_used b,sys.sm$ts_free c
where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name;
select 查看日志文件 from dual;
select member from v$logfile;
select 查看回滚段名称及大小 from dual;
select segment_name, tablespace_name, r.status, (initial_extent/1024) initialextent,(next_extent/1024) nextextent, max_extents, v.curext curextent from dba_rollback_segs r, v$rollstat v where r.segment_id = v.usn(+) order by segment_name;
