--调查环境所用的脚本----------------------------------------------------------------------------------
-- 作为系统用户登录，执行
----------------------------------------------------------------------------------

spool /o.log
set linesize 256
col NAME                                 format a40
col MEMBER                             format a40
col FILE_NAME                        format a40
col TABLESPACE_NAME           format a20
col DEFAULT_TABLESPACE       format a20
col TEMPORARY_TABLESPACE  format a20
col SEGMENT_NAME                 format a20
col USERNAME                         format a20
col OWNER                              format a20
col TABLE_NAME                      format a20
col TABLE_OWNER                   format a20
col INDEX_NAME                      format a20
col SEGMENT_NAME                 format a20

----------------------------------------------------------------------------------
-- 确认控制文件重做日志文件 数据文件的位置和名称
-- 这些文件和初始化参数文件{init(SID).ora}是冷备份的对象
----------------------------------------------------------------------------------
select * from v$controlfile;
select * from v$logfile;
select FILE#, STATUS, ENABLED, NAME from v$datafile; 

----------------------------------------------------------------------------------
--关于重做日志的信息
----------------------------------------------------------------------------------
select * from v$log;
select * from v$logfile; 

----------------------------------------------------------------------------------
--关于数据文件的信息
----------------------------------------------------------------------------------

select a.TABLESPACE_NAME, a.FILE_ID, a.FILE_NAME,
       a.BYTES/1024/1024 SIZE_MB, b.CREATE_BYTES/1024/1024 CREATE_MB,
       a.INCREMENT_BY*b.BLOCK_SIZE/1024/1024 NEXT_MB, a.MAXBYTES/1024/1024 MAX_MB
  from DBA_DATA_FILES a, v$datafile b
where a.FILE_ID = b.FILE#;

----------------------------------------------------------------------------------
--  数据文件的大小(KB)，使用量(KB)，空闲容量(KB)
----------------------------------------------------------------------------------
select a.FILE_ID, a.FILE_NAME,
       min(a.BYTES)/1024 SIZE_KB, min(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB,
       sum(b.BYTES)/1024 FREE_KB
  from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.FILE_ID, a.FILE_NAME; 
----------------------------------------------------------------------------------
--关于表空间的信息
----------------------------------------------------------------------------------

select TABLESPACE_NAME, INITIAL_EXTENT/1024 INITIAL_EXTENT_KB, NEXT_EXTENT/1024 NEXT_EXTENT_KB,
       MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, MIN_EXTLEN, STATUS, CONTENTS, LOGGING
  from dba_tablespaces;

----------------------------------------------------------------------------------
--  表空间的大小(KB)，使用量(KB)，空闲容量(KB)
----------------------------------------------------------------------------------
select a.TABLESPACE_NAME,
       min(a.BYTES)/1024 SIZE_KB, min(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB, sum(b.BYTES)/1024 FREE_KB
  from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME;

select a.TABLESPACE_NAME, a.FILE_NAME, sum(a.BYTES)/1024 SIZE_KB, sum(a.BYTES)/1024 - sum(b.BYTES)/1024 USED_KB, sum(b.BYTES)/1024 FREE_KB
  from dba_data_files a, dba_free_space b
where a.FILE_ID = b.FILE_ID
group by a.TABLESPACE_NAME, a.FILE_NAME; 
------------------------------------------------------------------------------------
--扩展的信息:扩展数为2个以上的数据段的一览表
------------------------------------------------------------------------------------
select TABLESPACE_NAME, OWNER, SEGMENT_NAME, count(*)
from dba_extents
group by TABLESPACE_NAME, OWNER, SEGMENT_NAME
having count(*) >= 2;

select TABLESPACE_NAME, OWNER, TABLE_NAME,
    INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE
  from dba_tables
  where (OWNER, TABLE_NAME) in (
          select OWNER, SEGMENT_NAME
            from dba_extents
            where SEGMENT_TYPE = 'TABLE'
            group by TABLESPACE_NAME, OWNER, SEGMENT_NAME, SEGMENT_TYPE
            having count(*) >= 2)
  order by TABLESPACE_NAME, OWNER, TABLE_NAME;

select TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME,
    INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE
  from dba_indexes
  where (OWNER, INDEX_NAME) in (
          select OWNER, SEGMENT_NAME
            from dba_extents
            where SEGMENT_TYPE = 'INDEX'
            group by TABLESPACE_NAME, OWNER, SEGMENT_NAME, SEGMENT_TYPE
            having count(*) >= 2)
  order by TABLESPACE_NAME, OWNER, TABLE_NAME, INDEX_NAME; 
------------------------------------------------------------------------------------
--表信息表信息(1)
------------------------------------------------------------------------------------
break on OWNER
select OWNER, TABLE_NAME,
    PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,
    INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
    TABLESPACE_NAME
  from dba_tables
  order by OWNER, TABLE_NAME;

--表信息(2)
----------------------------------------------------------------------------------
-- 为了求出NUM_ROWS,BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN的值,
-- 需要事先执行ANALYZE命令。
-- analyze table 表名 compute statistics;
----------------------------------------------------------------------------------
select OWNER, TABLE_NAME,
    NUM_ROWS, BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
    TABLESPACE_NAME
  from dba_tables
  order by OWNER, TABLE_NAME;
 
------------------------------------------------------------------------------------
--索引信息:索引信息(1)
------------------------------------------------------------------------------------
select TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS,
INI_TRANS, MAX_TRANS, INITIAL_EXTENT/1024 INITIAL_EXTENT_KB , NEXT_EXTENT/1024 NEXT_EXTENT_KB, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, PCT_FREE,
STATUS, TABLESPACE_NAME
  from dba_indexes
  order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;

------------------------------------------------------------------------------------
--索引信息(2)
------------------------------------------------------------------------------------
select TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS,
BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY,
STATUS, TABLESPACE_NAME
  from dba_indexes
  order by TABLE_OWNER, TABLE_NAME, INDEX_NAME;
 
------------------------------------------------------------------------------------
--关于用户的信息用户信息
------------------------------------------------------------------------------------
select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE, CREATED, PROFILE
  from dba_users
  order by USERNAME;
------------------------------------------------------------------------------------
--分配给用户的权限
------------------------------------------------------------------------------------
break on GRANTEE
select * from dba_role_privs order by GRANTEE, GRANTED_ROLE;

----------------------------------------------------------------------------------
--基本信息
----------------------------------------------------------------------------------
select * from v$version;
select * from v$option;
select * from v$sga;
select * from v$instance;
select * from v$database;
col VALUE format a40
select * from v$parameter;