Oracle 获取视图的DDL

version: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

通过sqlplus 获取视图的 DDL 的两种方法

先设置格式

set line 233 pages 233 long 9999

1. 通过 all_views/dba_views 视图

select text from dba_views where view_name='V_$DATABASE';

TEXT
--------------------------------------------------------------------------------
select "DBID","NAME","CREATED","RESETLOGS_CHANGE#","RESETLOGS_TIME","PRIOR_RESET
LOGS_CHANGE#","PRIOR_RESETLOGS_TIME","LOG_MODE","CHECKPOINT_CHANGE#","ARCHIVE_CH
ANGE#","CONTROLFILE_TYPE","CONTROLFILE_CREATED","CONTROLFILE_SEQUENCE#","CONTROL
FILE_CHANGE#","CONTROLFILE_TIME","OPEN_RESETLOGS","VERSION_TIME","OPEN_MODE","PR
OTECTION_MODE","PROTECTION_LEVEL","REMOTE_ARCHIVE","ACTIVATION#","SWITCHOVER#","
DATABASE_ROLE","ARCHIVELOG_CHANGE#","ARCHIVELOG_COMPRESSION","SWITCHOVER_STATUS"
,"DATAGUARD_BROKER","GUARD_STATUS","SUPPLEMENTAL_LOG_DATA_MIN","SUPPLEMENTAL_LOG
_DATA_PK","SUPPLEMENTAL_LOG_DATA_UI","FORCE_LOGGING","PLATFORM_ID","PLATFORM_NAM
E","RECOVERY_TARGET_INCARNATION#","LAST_OPEN_INCARNATION#","CURRENT_SCN","FLASHB
ACK_ON","SUPPLEMENTAL_LOG_DATA_FK","SUPPLEMENTAL_LOG_DATA_ALL","DB_UNIQUE_NAME",
"STANDBY_BECAME_PRIMARY_SCN","FS_FAILOVER_STATUS","FS_FAILOVER_CURRENT_TARGET","
FS_FAILOVER_THRESHOLD","FS_FAILOVER_OBSERVER_PRESENT","FS_FAILOVER_OBSERVER_HOST
" from v$database

2. 通过 dbms_metadata

SELECT dbms_metadata.get_ddl('VIEW', 'V_$DATABASE', 'SYS') FROM dual;

DBMS_METADATA.GET_DDL('VIEW','V_$DATABASE','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."V_$DATABASE" ("DBID", "NAME", "CREATED"
, "RESETLOGS_CHANGE#", "RESETLOGS_TIME", "PRIOR_RESE
TLOGS_CHANGE#", "PRIOR_RESETLOGS_TIME", "LOG_MODE",
"CHECKPOINT_CHANGE#", "ARCHIVE_CHANGE#", "CONTROLFIL
E_TYPE", "CONTROLFILE_CREATED", "CONTROLFILE_SEQUENC
E#", "CONTROLFILE_CHANGE#", "CONTROLFILE_TIME", "OPE
N_RESETLOGS", "VERSION_TIME", "OPEN_MODE", "PROTECTI
ON_MODE", "PROTECTION_LEVEL", "REMOTE_ARCHIVE", "ACT
IVATION#", "SWITCHOVER#", "DATABASE_ROLE", "ARCHIVEL
OG_CHANGE#", "ARCHIVELOG_COMPRESSION", "SWITCHOVER_S
TATUS", "DATAGUARD_BROKER", "GUARD_STATUS", "SUPPLEM
ENTAL_LOG_DATA_MIN", "SUPPLEMENTAL_LOG_DATA_PK", "SU
PPLEMENTAL_LOG_DATA_UI", "FORCE_LOGGING", "PLATFORM_
ID", "PLATFORM_NAME", "RECOVERY_TARGET_INCARNATION#"
, "LAST_OPEN_INCARNATION#", "CURRENT_SCN", "FLASHBAC
K_ON", "SUPPLEMENTAL_LOG_DATA_FK", "SUPPLEMENTAL_LOG
_DATA_ALL", "DB_UNIQUE_NAME", "STANDBY_BECAME_PRIMAR
Y_SCN", "FS_FAILOVER_STATUS", "FS_FAILOVER_CURRENT_T
ARGET", "FS_FAILOVER_THRESHOLD", "FS_FAILOVER_OBSERV
ER_PRESENT", "FS_FAILOVER_OBSERVER_HOST") AS
  select "DBID","NAME","CREATED","RESETLOGS_CHANGE#","RESE
TLOGS_TIME","PRIOR_RESETLOGS_CHANGE#","PRIOR_RESETLO
GS_TIME","LOG_MODE","CHECKPOINT_CHANGE#","ARCHIVE_CH
ANGE#","CONTROLFILE_TYPE","CONTROLFILE_CREATED","CON
TROLFILE_SEQUENCE#","CONTROLFILE_CHANGE#","CONTROLFI
LE_TIME","OPEN_RESETLOGS","VERSION_TIME","OPEN_MODE"
,"PROTECTION_MODE","PROTECTION_LEVEL","REMOTE_ARCHIV
E","ACTIVATION#","SWITCHOVER#","DATABASE_ROLE","ARCH
IVELOG_CHANGE#","ARCHIVELOG_COMPRESSION","SWITCHOVER
_STATUS","DATAGUARD_BROKER","GUARD_STATUS","SUPPLEME
NTAL_LOG_DATA_MIN","SUPPLEMENTAL_LOG_DATA_PK","SUPPL
EMENTAL_LOG_DATA_UI","FORCE_LOGGING","PLATFORM_ID","
PLATFORM_NAME","RECOVERY_TARGET_INCARNATION#","LAST_
OPEN_INCARNATION#","CURRENT_SCN","FLASHBACK_ON","SUP
PLEMENTAL_LOG_DATA_FK","SUPPLEMENTAL_LOG_DATA_ALL","
DB_UNIQUE_NAME","STANDBY_BECAME_PRIMARY_SCN","FS_FAI
LOVER_STATUS","FS_FAILOVER_CURRENT_TARGET","FS_FAILO
VER_THRESHOLD","FS_FAILOVER_OBSERVER_PRESENT","FS_FA
ILOVER_OBSERVER_HOST" from v$database

emmm。。。。这种办法查出来的DDL 语句，会换行，有点麻烦