Enterprise Manager

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to obtain the complete DDL of a TableSpace? / Cómo obtener el DDL completo de un TableSpace?

John Cristian CaldMay 20 2019 — edited May 23 2019

Good day,

I would like to know how to get the full DDL of a TableSpace. I've been noticing that when executing:

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name) as DDL

FROM dba_tablespaces

WHERE tablespace_name = '&tbs';

The full DDL is not obtained, for example, I get something like:

DDL

--------------------------------------------------------------------------------

CREATE TABLESPACE "MI_TBS" DATAFILE

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.350.964344761' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.441.964344855' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.442.964344863' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.443.964344865' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.256.964344869' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.257.964344875' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.516.964344883' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.517.964344893' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.518.964344895' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.519.964344899' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.909.974451679' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.985.990978729' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.986.990980825' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.987.990980833' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.990.991583059' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.991.991583123' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.1137.1008403559' RESIZE 34358689792;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.1136.1008403577' RESIZE 34358689792;

oracle@miInstance1>

But, if I use the edition Toad for Oracle Xpert 2017 12.11.0.95, I get a DDL like:

CREATE TABLESPACE MI_TBS DATAFILE

'+DATAC3' SIZE 33554416K AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED,

'+DATAC3' SIZE 33554416K AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,

'+DATAC3' SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,

'+DATAC3' SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED

LOGGING

DEFAULT

NO INMEMORY

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL

FLASHBACK ON;

Note the difference in the DDL, in the first one, after the parameter DATAFILE the specification of the diskgroup '+DATAC3' does not appear, unlike the DDL of the Toad, the specification of the diskgroup '+DATAC3' appears.

So, my question is, how can you get the full DDL of a TableSpace without losing any parameters with DBMS_METADATA.GET_DDL?

I'm still pending.

Cordially.

---

Buen día,

Quiciera saber, cómo obtener el DDL completo de un TableSpace. He venido notando que al ejecutar:

SELECT DBMS_METADATA.get_ddl ('TABLESPACE', tablespace_name) as DDL

FROM dba_tablespaces

WHERE tablespace_name = '&tbs';

No se obtiene el DDL completo, por ejemplo, obtengo algo como:

DDL

--------------------------------------------------------------------------------

CREATE TABLESPACE "MI_TBS" DATAFILE

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 104857600

AUTOEXTEND ON NEXT 524288000 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M,

SIZE 10737418240

AUTOEXTEND ON NEXT 1073741824 MAXSIZE 32767M

LOGGING ONLINE PERMANENT BLOCKSIZE 8192

EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT

NOCOMPRESS SEGMENT SPACE MANAGEMENT MANUAL;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.350.964344761' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.441.964344855' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.442.964344863' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.443.964344865' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.256.964344869' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.257.964344875' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.516.964344883' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.517.964344893' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.518.964344895' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.519.964344899' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.909.974451679' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.985.990978729' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.986.990980825' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.987.990980833' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.990.991583059' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.991.991583123' RESIZE 34359721984;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.1137.1008403559' RESIZE 34358689792;

ALTER DATABASE DATAFILE

'+DATAC3/miInstance/DATAFILE/MI_TBS.1136.1008403577' RESIZE 34358689792;

oracle@miInstance1>

Pero, si hago uso del Toad obtengo un DDL como:

CREATE TABLESPACE MI_TBS DATAFILE

'+DATAC3' SIZE 33554416K AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED,

'+DATAC3' SIZE 33554416K AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,

'+DATAC3' SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED,

'+DATAC3' SIZE 32767M AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED

LOGGING

DEFAULT

NO INMEMORY

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT MANUAL

FLASHBACK ON;

Note la diferencia en el DDL, en el primero, aparece después del parámetro DATAFILE la especificación del diskgroup '+DATAC3', en cambio en el DDL del Toad, sí aparece la especificación del grupo de discos '+DATAC3'.

Entonces, mi pregunta es, ¿cómo se puede obtener el DDL completo de un TableSpace sin perder ningún parámetro con DBMS_METADATA.GET_DDL?

Quedo atento estimados.

Cordialmente.

Added on May 20 2019

1 comment

11,400 views