Re: DBMS_METADATA

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 26 Oct 2011 13:26:18 +0000 (UTC)
Message-ID: <pan.2011.10.26.13.26.19_at_gmail.com>

On Tue, 25 Oct 2011 11:22:38 -0700, ExecMan wrote:

> Also, tables with Nested Tables give me this error on the actual Nested
> Table entry:
>
> TABLE - ANN_BAL_SHEET - ORA-31603: object "ANN_BAL_SHEET" of type TABLE
> not found in schema "DATA_HOLDER"

You should filter out nested tables, using something like this:

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)

     FROM USER_TABLES u
     WHERE u.nested='NO' 

USER_TABLES view has a column which describes whether it's a nested table or not.

SQL> desc user_tables
 Name Null? Type

• --------

  ---

  TABLE_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME VARCHAR2(30) CLUSTER_NAME VARCHAR2(30) IOT_NAME VARCHAR2(30) STATUS VARCHAR2(8) PCT_FREE NUMBER PCT_USED NUMBER INI_TRANS NUMBER MAX_TRANS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER LOGGING VARCHAR2(3) BACKED_UP VARCHAR2(1) NUM_ROWS NUMBER BLOCKS NUMBER EMPTY_BLOCKS NUMBER AVG_SPACE NUMBER CHAIN_CNT NUMBER AVG_ROW_LEN NUMBER AVG_SPACE_FREELIST_BLOCKS NUMBER NUM_FREELIST_BLOCKS NUMBER DEGREE VARCHAR2(40) INSTANCES VARCHAR2(40) CACHE VARCHAR2(20) TABLE_LOCK VARCHAR2(8) SAMPLE_SIZE NUMBER LAST_ANALYZED DATE PARTITIONED VARCHAR2(3) IOT_TYPE VARCHAR2(12) TEMPORARY VARCHAR2(1) SECONDARY VARCHAR2(1) NESTED VARCHAR2(3) BUFFER_POOL VARCHAR2(7) FLASH_CACHE VARCHAR2(7) CELL_FLASH_CACHE VARCHAR2(7) ROW_MOVEMENT VARCHAR2(8) GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) DURATION VARCHAR2(15) SKIP_CORRUPT VARCHAR2(8) MONITORING VARCHAR2(3) CLUSTER_OWNER VARCHAR2(30) DEPENDENCIES VARCHAR2(8) COMPRESSION VARCHAR2(8) COMPRESS_FOR VARCHAR2(12) DROPPED VARCHAR2(3) READ_ONLY VARCHAR2(3) SEGMENT_CREATED VARCHAR2(3) RESULT_CACHE VARCHAR2(7)

SQL>

-- 
http://mgogala.byethost5.com

Received on Wed Oct 26 2011 - 08:26:18 CDT