Oracle獲取資料庫中的物件建立語句

背景：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