查看数据库基本情况

set linesize 1000
column orderid format 999
column inst_id format 999
column name format a30
column VALUE format a80

WITH w1 AS
(SELECT 10 orderid,
NULL AS inst_id,
'数据库创建时间' NAME,
to_char(t.created, 'YYYY-MM-DD HH24:MI:SS') VALUE
FROM v$database t
UNION ALL
SELECT 20 orderid, t.inst_id, '数据库版本' NAME, t.version VALUE
FROM gv$instance t
UNION ALL
SELECT 130 orderid,
NULL AS inst_id,
'RAC' AS NAME,
decode(t.value, 'TRUE', 'Yes', 'No') AS VALUE
FROM v$option t
WHERE t.parameter = 'Real Application Clusters'
UNION ALL
SELECT 135 orderid,
NULL AS inst_id,
'ADG' AS NAME,
decode(t.switchover_status, 'TO STANDBY', 'Yes', 'No') ||
decode(t.switchover_status,
'TO STANDBY',
';' || t.open_mode || ',' || t.database_role || ',' ||
t.protection_mode || ',' || t.protection_level,
'') VALUE
FROM v$database t
UNION ALL
SELECT 7 orderid,
NULL AS inst_id,
'数据库DBID' AS NAME,
to_char(dbid) AS VALUE
FROM gv$database t
WHERE rownum < 2
UNION ALL
SELECT 5 orderid, NULL AS inst_id, '数据库名称' AS NAME, NAME AS VALUE
FROM gv$database t
WHERE rownum < 2
UNION ALL
SELECT 60 orderid, t.inst_id, '数据库实例' AS NAME, instance_name AS VALUE
FROM gv$instance t
UNION ALL
SELECT 70 orderid,
NULL AS inst_id,
'数据库归档' AS NAME,
log_mode AS VALUE
FROM gv$database t
WHERE rownum < 2
UNION ALL
SELECT 80 orderid, NULL AS inst_id, '数据库归档位置' NAME, VALUE
FROM (SELECT NAME,
VALUE,
SUM(log_arch_count) over(PARTITION BY 1 ORDER BY 1) AS log_arch_counts
FROM (SELECT NAME,
VALUE,
(CASE
WHEN t.name LIKE 'log_archive_dest%' THEN
1
ELSE
0
END) AS log_arch_count
FROM v$parameter t
WHERE (t.name LIKE 'log_archive_dest%' OR
t.name = 'db_recovery_file_dest')
AND t.name NOT LIKE 'log_archive_dest_state%'
AND t.value IS NOT NULL))
WHERE (log_arch_counts > 0 AND NAME LIKE 'log_archive_dest%')
OR (log_arch_counts = 0 AND NAME LIKE 'db_recovery_file_dest%')
UNION ALL
SELECT 90 orderid,
NULL AS inst_id,
'数据库归档文件大小' NAME,
to_char(nvl(round(SUM(t.blocks * t.block_size) / 1024 / 1024 / 1024),
0)) || 'G' AS VALUE
FROM v$archived_log t
WHERE t.deleted = 'NO'
UNION ALL
SELECT 100 orderid,
NULL AS inst_id,
'数据库闪回' AS NAME,
flashback_on AS VALUE
FROM gv$database t
WHERE rownum < 2
UNION ALL
SELECT 110 orderid, NULL AS inst_id, '数据库恢复区位置' NAME, t.value
FROM v$parameter t
WHERE t.name = 'db_recovery_file_dest'
UNION ALL
SELECT 120 orderid,
NULL AS inst_id,
'数据库恢复区大小' NAME,
to_char(round(to_number(nvl(t.value, '0')) / 1024 / 1024 / 1024)) || 'G' AS VALUE
FROM v$parameter t
WHERE t.name = 'db_recovery_file_dest_size'
UNION ALL
SELECT 200 orderid,
t.inst_id,
'运行时间' AS NAME,
'启动时间：' || to_char(startup_time, 'YYYY-MM-DD HH24:MI:SS') ||
'；运行时间：' || trunc(SYSDATE - (startup_time)) || '天 ' ||
trunc(24 *
((SYSDATE - startup_time) - trunc(SYSDATE - startup_time))) ||
'小时 ' || MOD(trunc(1440 * ((SYSDATE - startup_time) -
trunc(SYSDATE - startup_time))),
60) || '分 ' ||
MOD(trunc(86400 *
((SYSDATE - startup_time) - trunc(SYSDATE - startup_time))),
60) || '秒' VALUE
FROM gv$instance t)
SELECT * FROM w1 ORDER BY nvl(inst_id, 0), orderid, NAME;