Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Artsiom.

Asked: April 03, 2017 - 11:03 am UTC

Last updated: April 05, 2017 - 9:53 am UTC

Version: 11g2

Viewed 1000+ times

You Asked

Hello,

I have problem with executing dbms_metadata.get_ddl. You can find sqript with issue here:
https://livesql.oracle.com/apex/livesql/file/content_ES4SXSIQNK2JLYFHH3V752UA6.html

Why does get ddl procedure lasting for a long time(endless, more then 20 h.) in this case?

How can I increase performance?

Thank you

with LiveSQL Test Case:

and Chris said...

Eek, you're creating the table in the temporary tablespace! Don't do that!

As the docs say:

No permanent schema objects can reside in a temporary tablespace.

Things won't work as you expect if you use this:

CREATE TABLE TABLE1 ( 
  COL_1  NUMBER(38)                       NOT NULL, 
  COL_2    NUMBER(38)                       NOT NULL, 
  START_DT     DATE                             NOT NULL, 
  END_DT       DATE                             NOT NULL, 
  LOAD_ID      NUMBER(38)                       NOT NULL 
) 
TABLESPACE TEMP ;

insert into TABLE1 values (1, 1, sysdate, sysdate, 1);

SQL Error: ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace


This may be related to why it's taking so long for you to create the table. Stripping out the tablespace sections it's fast for me (I've edited out most of the partitions for brevity):

SQL> set sqlbl on
SQL> set timing on
SQL> CREATE TABLE TABLE1 (
  2    COL_1  NUMBER(38)                       NOT NULL,
  3    COL_2    NUMBER(38)                       NOT NULL,
  4    START_DT     DATE                             NOT NULL,
  5    END_DT       DATE                             NOT NULL,
  6    LOAD_ID      NUMBER(38)                       NOT NULL
  7  )
  8  nocompress
  9  RESULT_CACHE (MODE DEFAULT)
 10  PCTUSED    40
 11  PCTFREE    10
 12  INITRANS   1
 13  MAXTRANS   255
 14  STORAGE    (
 15              BUFFER_POOL      DEFAULT
 16              FLASH_CACHE      DEFAULT
 17              CELL_FLASH_CACHE DEFAULT
 18             )
 19  PARTITION BY RANGE (START_DT)
 20  SUBPARTITION BY HASH (COL_1)
 21  (
 22    PARTITION P201409 VALUES LESS THAN (TO_DATE(' 2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 23      NOLOGGING
 24      NOCOMPRESS
 25
 26      PCTUSED    40
 27      PCTFREE    10
 28      INITRANS   1
 29      MAXTRANS   255
 30      STORAGE    (
 31                  MAXSIZE          UNLIMITED
 32                  BUFFER_POOL      DEFAULT
 33                  FLASH_CACHE      DEFAULT
 34                  CELL_FLASH_CACHE DEFAULT
 35                 )
 36      SUBPARTITIONS 32 ,
...
442    PARTITION P999912 VALUES LESS THAN (TO_DATE(' 9999-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
443      NOLOGGING
444      NOCOMPRESS
445
446      PCTUSED    40
447      PCTFREE    10
448      INITRANS   1
449      MAXTRANS   255
450      STORAGE    (
451                  MAXSIZE          UNLIMITED
452                  BUFFER_POOL      DEFAULT
453                  FLASH_CACHE      DEFAULT
454                  CELL_FLASH_CACHE DEFAULT
455                 )
456      SUBPARTITIONS 32
457  )
458  NOCACHE
459  NOPARALLEL
460  MONITORING;

Table created.

Elapsed: 00:00:01.16
SQL>
SQL> CREATE UNIQUE INDEX PK_TABLE1 ON TABLE1
  2  (COL_1, COL_2, START_DT)
  3
  4    PCTFREE    10
  5    INITRANS   2
  6    MAXTRANS   255
  7    STORAGE    (
  8                BUFFER_POOL      DEFAULT
  9                FLASH_CACHE      DEFAULT
 10                CELL_FLASH_CACHE DEFAULT
 11               )
 12  LOCAL (
 13    PARTITION P201409
 14      NOLOGGING
 15      NOCOMPRESS
 16
 17      PCTFREE    10
 18      INITRANS   2
 19      MAXTRANS   255
 20      STORAGE    (
 21                  MAXSIZE          UNLIMITED
 22                  BUFFER_POOL      DEFAULT
 23                  FLASH_CACHE      DEFAULT
 24                  CELL_FLASH_CACHE DEFAULT
 25                 )
 26      ,
...
405    PARTITION P999912
406      NOLOGGING
407      NOCOMPRESS
408
409      PCTFREE    10
410      INITRANS   2
411      MAXTRANS   255
412      STORAGE    (
413                  MAXSIZE          UNLIMITED
414                  BUFFER_POOL      DEFAULT
415                  FLASH_CACHE      DEFAULT
416                  CELL_FLASH_CACHE DEFAULT
417                 )
418  )
419  NOPARALLEL;

Index created.

Elapsed: 00:00:01.06
SQL>
SQL> ALTER TABLE TABLE1 ADD (
  2    CONSTRAINT PK_TABLE1
  3    PRIMARY KEY
  4    (COL_1, COL_2, START_DT)
  5    USING INDEX LOCAL
  6    ENABLE VALIDATE);

Table altered.

Elapsed: 00:00:00.31
SQL>
SQL> select dbms_metadata.get_ddl('TABLE', 'TABLE1') from dual;

DBMS_METADATA.GET_DDL('TABLE','TABLE1')
--------------------------------------------------------------------------------

  CREATE TABLE "CHRIS"."TABLE1"
   (    "COL_1" NUMBER(38,0) NOT NULL ENABLE,
        "


Elapsed: 00:00:03.67


If it still takes 20+ hours on a normal tablespace, something's not right. Speak with support!

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.