--XBOS資料庫設定值取得SQL Script如下：
-- 1. 收集資料庫版本資訊
select banner from v$version
union all
select distinct banner from (select 'Oracle Client version '||client_version banner FROM v$session_connect_info WHERE sid = SYS_CONTEXT('USERENV', 'SID'))
union all
select 'Database CharacterSet: '||value from v$nls_parameters where parameter='NLS_CHARACTERSET'
union all
select 'Database archive mode: '||log_mode from v$database
union all
select 'Database Global Name: '||GLOBAL_NAME from GLOBAL_NAME
union all
select 'Database Server Name: '||host_name from v$instance
union all
select 'Database Startup Time: '||to_char(startup_time, 'YYYY/MM/DD HH:MI:SS') from v$instance
union all
select 'Database Client IP: '||sys_context('USERENV', 'IP_ADDRESS') from dual
union all
select 'Database Trace Log: '||value from v$diag_info where name='Diag Trace';
-- 2. 收集資料庫參數設定資訊
select name, value from v$parameter2 where name in (
       'audit_file_dest',
       'audit_trail',
       'cluster_database',
       'cluster_database_instances',
       'control_files',
       'control_management_pack_access',
       'cpu_count',
       'db_block_size',
       'db_files', 
       'db_name',
       'instance_mode',
       'memory_max_target', 
       'memory_target',
       'optimizer_features_enable', 
       'pga_aggregate_target',
       'processes',
       'remote_login_passwordfile',
       'service_names',
       'sessions',
       'sga_max_size', 
       'sga_target',
       'spfile',
       'statistics_level',
       'user_dump_dest',
       'utl_file_dir'
       )
order by name; 

-- 3. 收集Tablespace使用空間大小資訊
SELECT df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes FROM dba_data_files
         GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 1;

-- 4. 收集Data Files使用空間大小資訊
select file_name,tablespace_name, bytes/1048576 "SizeMB", bytes/blocks "BLOCK", autoextensible from dba_data_files
union all
select file_name,tablespace_name, bytes/1048576 "SizeMB", bytes/blocks "BLOCK", autoextensible from dba_temp_files;

-- 5. 收集主機名稱資訊
select instance_name,host_name,version,startup_time,status from v$instance;

-- 6. 收集資料庫名稱狀態
select dbid, db_unique_name, name, log_mode, open_mode, database_role, guard_status, platform_name, flashback_on from v$database;

-- 7. 收集表格(tables)使用空間大小資訊
select OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,round(BLOCKS/128) as SizeMB,AVG_ROW_LEN,round(AVG_ROW_LEN*NUM_ROWS/1048576) as LenMB
     from all_tables where owner in ('FBOSPROD', 'XBOSWRNT', 'XBOSPROD') and blocks is not null order by 1, 5 desc;

-- 8. 收集索引(indexes)使用空間大小資訊
select owner, segment_name, bytes/1024/1024 "SizeMB" from dba_segments
where owner in ('FBOSPROD', 'XBOSWRNT', 'XBOSPROD') and segment_type = 'INDEX' order by 3 desc;

-- 9. 收集Partition Table設定資訊
select table_name, partition_name, num_rows, round(blocks/128, 2) SizeMB, last_analyzed
from dba_tab_statistics where owner in ('FBOSPROD', 'XBOSWRNT', 'XBOSPROD');

--10. 收集資料表空間使用額度
select tablespace_name,username,max_bytes,max_blocks from dba_ts_quotas where username in ('FBOSPROD', 'XBOSWRNT', 'XBOSPROD');

--11. 收集資料庫SYS權限設定
select * from dba_sys_privs  where grantee in ('FBOSPROD', 'WRNTPROD', 'XBOSPROD')
union all
select * from dba_sys_privs  where grantee in ('FBOSLOAD', 'WRNTLOAD', 'XBOSLOAD')
union all
select * from dba_sys_privs  where grantee in ('FBOSLOAD_ROLE', 'WRNTLOAD_ROLE', 'XBOSLOAD_ROLE')
order by 1, 2;

--12. 收集資料庫ROLE權限設定
select * from dba_role_privs  where grantee in ('FBOSPROD', 'WRNTPROD', 'XBOSPROD')
union all
select * from dba_role_privs  where grantee in ('FBOSLOAD', 'WRNTLOAD', 'XBOSLOAD')
union all
select * from dba_role_privs  where grantee in ('FBOSLOAD_ROLE', 'WRNTLOAD_ROLE', 'XBOSLOAD_ROLE')
order by 1, 2;

--14. 收集資料庫User Objects Count
select owner, object_type, count(*) from dba_objects
where owner in ('FBOSPROD', 'WRNTPROD', 'XBOSPROD')
group by owner, object_type, object_type order by 1, 2;
select owner, object_type, count(*) from dba_objects
where owner in ('FBOSLOAD', 'WRNTLOAD', 'XBOSLOAD')
group by owner, object_type, object_type order by 1, 2;

--15. 收集物件授權Scripts語法
select 'GRANT DELETE, INSERT, SELECT, UPDATE ON XBOSPROD.' || object_name || ' TO XBOSLOAD;'  GRANT_TEXT
from dba_objects where owner = 'XBOSPROD' and object_type = 'TABLE'
union all
select 'GRANT EXECUTE ON XBOSPROD.' || object_name || ' TO XBOSLOAD;' GRANT_TEXT
from dba_objects where owner = 'XBOSPROD' and object_type in('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE')
union all
select 'GRANT ALTER, SELECT ON XBOSPROD.' || object_name || ' TO XBOSLOAD;' GRANT_TEXT
from dba_objects where owner = 'XBOSPROD' and object_type = 'SEQUENCE'
union all
select 'GRANT SELECT ON XBOSPROD.' || object_name || ' TO XBOSLOAD;' GRANT_TEXT
from dba_objects where owner = 'XBOSPROD' and object_type = 'VIEW'
order by GRANT_TEXT;

--16. 收集Create Synonym Scripts語法
select 'create or replace synonym XBOSLOAD.' || object_name || ' for XBOSPROD.'||object_name||';'
from dba_objects where owner = 'XBOSPROD';

--17. Schema OWNER & Table統計值收集Scripts語法
select table_name, last_analyzed from user_tab_statistics where table_name = 'TRANSFER_SEND';
exec dbms_stats.gather_table_stats(ownname => 'XBOSPROD' , tabname => 'TRANSFER_SEND',cascade => true);
exec dbms_stats.gather_schema_stats(ownname => 'XBOSPROD', degree => 4, cascade => true);
select table_name, last_analyzed from user_tab_statistics where table_name = 'TRANSFER_SEND';

--18. Tablespaces & datafiles list
select * from v$tablespace;      -- NAME, BIGFILE, FLASHBACK_ON
select * from v$datafile;        -- NAME, BYTES, BLOCKS, BLOCK_SIZE
select * from v$tempfile;        -- NAME, BYTES, BLOCKS, BLOCK_SIZE
select * from dba_tablespaces;   -- TABLESPACE_NAME, BIGFILE, BLOCK_SIZE
select * from dba_data_files;    -- FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS
select * from dba_temp_files;    -- FILE_NAME, TABLESPACE_NAME, BYTES, BLOCKS, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS