SQL> select * from v$database;
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME
---------- --------- ------------------ ----------------- ------------------
PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TI LOG_MODE CHECKPOINT_CHANGE#
----------------------- ------------------ ------------ ------------------
ARCHIVE_CHANGE# CONTROL CONTROLFILE_CREATE CONTROLFILE_SEQUENCE#
--------------- ------- ------------------ ---------------------
CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETL VERSION_TIME
------------------- ------------------ ----------- ------------------
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_A
-------------------- -------------------- -------------------- --------
ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVEL
----------- ----------- ---------------- ------------------ --------
SWITCHOVER_STATUS DATAGUAR GUARD_S SUPPLEME SUP SUP FOR PLATFORM_ID
-------------------- -------- ------- -------- --- --- --- -----------
PLATFORM_NAME
--------------------------------------------------------------------------------
RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN
---------------------------- ---------------------- -----------
FLASHBACK_ON SUP SUP DB_UNIQUE_NAME
------------------ --- --- ------------------------------
STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET
-------------------------- ---------------------- ------------------------------
FS_FAILOVER_THRESHOLD FS_FAIL
--------------------- -------
FS_FAILOVER_OBSERVER_HOST
--------------------------------------------------------------------------------
CON PRIMARY_DB_UNIQUE_NAME SUP MIN_REQUIRED_CAPTURE_CHANGE#
--- ------------------------------ --- ----------------------------
390458102 PROD 19-NOV-18 4335196 19-NOV-18
DBID NAME CREATED RESETLOGS_CHANGE# RESETLOGS_TIME
---------- --------- ------------------ ----------------- ------------------
PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TI LOG_MODE CHECKPOINT_CHANGE#
----------------------- ------------------ ------------ ------------------
ARCHIVE_CHANGE# CONTROL CONTROLFILE_CREATE CONTROLFILE_SEQUENCE#
--------------- ------- ------------------ ---------------------
CONTROLFILE_CHANGE# CONTROLFILE_TIME OPEN_RESETL VERSION_TIME
------------------- ------------------ ----------- ------------------
OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_A
-------------------- -------------------- -------------------- --------
ACTIVATION# SWITCHOVER# DATABASE_ROLE ARCHIVELOG_CHANGE# ARCHIVEL
----------- ----------- ---------------- ------------------ --------
SWITCHOVER_STATUS DATAGUAR GUARD_S SUPPLEME SUP SUP FOR PLATFORM_ID
-------------------- -------- ------- -------- --- --- --- -----------
PLATFORM_NAME
--------------------------------------------------------------------------------
RECOVERY_TARGET_INCARNATION# LAST_OPEN_INCARNATION# CURRENT_SCN
---------------------------- ---------------------- -----------
FLASHBACK_ON SUP SUP DB_UNIQUE_NAME
------------------ --- --- ------------------------------
STANDBY_BECAME_PRIMARY_SCN FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET
-------------------------- ---------------------- ------------------------------
使用print_table脚本可以得到很标准的输出:
SQL> set serveroutput on
SQL> exec print_table('select * from v$database');
DBID : 390458102
NAME : PROD
CREATED : 19-nov-2018 15:29:37
RESETLOGS_CHANGE# : 4335196
RESETLOGS_TIME : 19-nov-2018 15:32:12
PRIOR_RESETLOGS_CHANGE# : 4335192
PRIOR_RESETLOGS_TIME : 19-nov-2018 15:07:27
LOG_MODE : NOARCHIVELOG
CHECKPOINT_CHANGE# : 4318350019
ARCHIVE_CHANGE# : 4318205018
CONTROLFILE_TYPE : CURRENT
CONTROLFILE_CREATED : 19-nov-2018 15:29:37
CONTROLFILE_SEQUENCE# : 13801
CONTROLFILE_CHANGE# : 4318380595
CONTROLFILE_TIME : 13-jan-2019 11:12:15
OPEN_RESETLOGS : NOT ALLOWED
VERSION_TIME : 19-nov-2018 15:29:37
OPEN_MODE : READ WRITE
PROTECTION_MODE : MAXIMUM PERFORMANCE
PROTECTION_LEVEL : UNPROTECTED
REMOTE_ARCHIVE : ENABLED
ACTIVATION# : 404458956
SWITCHOVER# : 404458956
DATABASE_ROLE : PRIMARY
ARCHIVELOG_CHANGE# : 0
ARCHIVELOG_COMPRESSION : DISABLED
SWITCHOVER_STATUS : NOT ALLOWED
DATAGUARD_BROKER : DISABLED
GUARD_STATUS : NONE
SUPPLEMENTAL_LOG_DATA_MIN : NO
SUPPLEMENTAL_LOG_DATA_PK : NO
SUPPLEMENTAL_LOG_DATA_UI : NO
FORCE_LOGGING : NO
PLATFORM_ID : 13
PLATFORM_NAME : Linux x86 64-bit
RECOVERY_TARGET_INCARNATION# : 2
LAST_OPEN_INCARNATION# : 2
CURRENT_SCN : 4318380619
FLASHBACK_ON : NO
SUPPLEMENTAL_LOG_DATA_FK : NO
SUPPLEMENTAL_LOG_DATA_ALL : NO
DB_UNIQUE_NAME : PROD
STANDBY_BECAME_PRIMARY_SCN : 0
FS_FAILOVER_STATUS : DISABLED
FS_FAILOVER_CURRENT_TARGET :
FS_FAILOVER_THRESHOLD : 0
FS_FAILOVER_OBSERVER_PRESENT :
FS_FAILOVER_OBSERVER_HOST :
CONTROLFILE_CONVERTED : NO
PRIMARY_DB_UNIQUE_NAME :
SUPPLEMENTAL_LOG_DATA_PL : NO
MIN_REQUIRED_CAPTURE_CHANGE# :
-----------------
PL/SQL procedure successfully completed.
print_table脚本如下:
create or replace procedure print_table(p_query in varchar2) AUTHID CURRENT_USER is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
begin
execute immediate 'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl);
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while (dbms_sql.fetch_rows(l_theCursor) > 0) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value(l_theCursor, i, l_columnValue);
dbms_output.put_line(rpad(l_descTbl(i).col_name, 30) || ': ' ||
l_columnValue);
end loop;
dbms_output.put_line('-----------------');
end loop;
execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/
引用自tom:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958