背景：oracle数据库中有很多无效对象(这里以 视图举例) 使用dbms_metadata.get_ddl()函数可以做到
SQL> select owner, object_name, object_type from dba_objects where status = 'INVALID' and owner='GAC_GAEI_USER' and OBJECT_TYPE='VIEW'; OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- GAC_GAEI_USER V_REG_INSU01_SEGMENT_LV02 VIEW GAC_GAEI_USER V_REG_TERM_MANFBRAND_CITY_Q VIEW GAC_GAEI_USER V_NE_VERSION_PRICE VIEW GAC_GAEI_USER V_NE_MANF_PRICE VIEW GAC_GAEI_USER V_NE_MARKET_PRICE VIEW GAC_GAEI_USER V_DEALER_QUARTER_CITY_NO_BRAND VIEW GAC_GAEI_USER V_PART_CUST_SRC VIEW GAC_GAEI_USER V_PART_MAINTAIN VIEW GAC_GAEI_USER V_SPARE_PART_PRICE VIEW GAC_GAEI_USER V_REG_INSU01_SEGMENT_LV03 VIEW GAC_GAEI_USER V_NE_SUB_MODEL_PRICE VIEW
SQL> SET PAGESIZE 0
SQL> SET LONG 1000
SQL> select dbms_metadata.get_ddl('VIEW','V_NE_MARKET_SALES','GAC_GAEI_USER') from dual;
DBMS_METADATA.GET_DDL('VIEW','V_NE_MARKET_SALES','GAC_GAEI_USER')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW "GAC_GAEI_USER"."V_NE_MARKET_SALES" ("STANDARD_ID
", "YM_ID", "SALES_TYPE_ID", "FUEL_TYPE_ID", "BQ_SALES", "BQ_YEARACC_SALES") AS
select
STANDARD_ID,
YM_ID,
SALES_TYPE_ID,
FUEL_TYPE_ID,
SUM(BQ_SALES) AS BQ_SALES,
SUM(BQ_YEARACC_SALES) AS BQ_YEARACC_SALES
DBMS_METADATA.GET_DDL('VIEW','V_NE_MARKET_SALES','GAC_GAEI_USER')
--------------------------------------------------------------------------------
from FDW_NE_MANF_SALES
GROUP BY STANDARD_ID, YM_ID, SALES_TYPE_ID, FUEL_TYPE_ID
