Thanks for the question.

Asked: June 29, 2017 - 9:28 am UTC

Last updated: May 27, 2019 - 6:19 am UTC

Version: 12.1.0

Viewed 10K+ times! This question is

You Asked

Hi all,

I would like to get DDL for a table with just tablespace info (without the remaining "Segment attributes" info).
For example,

CREATE TABLE "SQL_ZRRMMMSKTDVROYPXNSHFKJXCB"."MY_TABLE" 
   ( "COLUMN1" VARCHAR2(1), 
 "COLUMN2" NUMBER(1,0)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "LIVESQL_USERS"

I would like to remove everything except "Tablespace" info:

CREATE TABLE "SQL_ZRRMMMSKTDVROYPXNSHFKJXCB"."MY_TABLE" 
   ( "COLUMN1" VARCHAR2(1), 
 "COLUMN2" NUMBER(1,0)
   ) TABLESPACE "LIVESQL_USERS"

I send a LiveSQL Link with a script that I created for my example.

Thanks.

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/e7c86bri65zsodai11gknrj9h

and Connor said...

Is the sufficient for you ?

SQL> set long 5000
SQL> BEGIN
  2     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
  3     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
  4     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', true);
  5     DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT DBMS_METADATA.get_ddl ('TABLE', 'T', user) from dual;

DBMS_METADATA.GET_DDL('TABLE','T',USER)
--------------------------------------------------------------------------------

  CREATE TABLE "MCDONAC"."T"
   (    "OWNER" VARCHAR2(128),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(128),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(23),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(128),
        "SHARING" VARCHAR2(18),
        "EDITIONABLE" VARCHAR2(1),
        "ORACLE_MAINTAINED" VARCHAR2(1),
        "APPLICATION" VARCHAR2(1),
        "DEFAULT_COLLATION" VARCHAR2(100),
        "DUPLICATED" VARCHAR2(1),
        "SHARDED" VARCHAR2(1),
        "CREATED_APPID" NUMBER,
        "CREATED_VSNID" NUMBER,
        "MODIFIED_APPID" NUMBER,
        "MODIFIED_VSNID" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

Rating

(2 ratings)

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

Comments

Remove extra info about SEGMENT

Susana Oliveira, June 30, 2017 - 8:10 am UTC

Thanks for your answer.

However, you showed what I have and not what I would like to have.

I would like to remove all the content before "TABLESPACE" and after the closing parentheses:

SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING

I would like to have the following result:

CREATE TABLE "MCDONAC"."T"
   (    "OWNER" VARCHAR2(128),
        "OBJECT_NAME" VARCHAR2(128),
        "SUBOBJECT_NAME" VARCHAR2(128),
        "OBJECT_ID" NUMBER,
        "DATA_OBJECT_ID" NUMBER,
        "OBJECT_TYPE" VARCHAR2(23),
        "CREATED" DATE,
        "LAST_DDL_TIME" DATE,
        "TIMESTAMP" VARCHAR2(19),
        "STATUS" VARCHAR2(7),
        "TEMPORARY" VARCHAR2(1),
        "GENERATED" VARCHAR2(1),
        "SECONDARY" VARCHAR2(1),
        "NAMESPACE" NUMBER,
        "EDITION_NAME" VARCHAR2(128),
        "SHARING" VARCHAR2(18),
        "EDITIONABLE" VARCHAR2(1),
        "ORACLE_MAINTAINED" VARCHAR2(1),
        "APPLICATION" VARCHAR2(1),
        "DEFAULT_COLLATION" VARCHAR2(100),
        "DUPLICATED" VARCHAR2(1),
        "SHARDED" VARCHAR2(1),
        "CREATED_APPID" NUMBER,
        "CREATED_VSNID" NUMBER,
        "MODIFIED_APPID" NUMBER,
        "MODIFIED_VSNID" NUMBER
   ) 
  TABLESPACE "USERS" ;

In order to set the parameter "TABLESPACE" to true:

DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'TABLESPACE', true);

I also need to set the parameter "SEGMENT_ATTRIBUTES" to true:

DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', true);

And that brings that extra information about "SEGMENT" that I wouldn't like to have. I just want the "TABLESPACE".

Thank you.

July 01, 2017 - 2:51 am UTC

Sorry, I misread your post - I thought you had all the *other* information as well.

Can you elucidate on why you want to get rid of the remaining information ? It seems fairly innocuous to me.

My reason

Thomas Brotherton, May 23, 2019 - 7:28 pm UTC

My reason for wanting to include tablespace but not the segment creation/pctfree/pctused/initrans/maxtrans/nocompress/logging options is because I'm using set_remap_param to change the tablespace but want to inherit the segment attributes from the new tablespace.

Since the clauses are allowed to be separate in the DDL, they should be allowed to be separate in the transform parameters, or at least an option to exclude segment attributes that match the tablespace defaults.

Using your example, add:

exec dbms_metadata.set_remap_param( DBMS_METADATA.session_transform, 'REMAP_TABLESPACE', 'USERS', 'MCDONAC');

May 27, 2019 - 6:19 am UTC

Yeah I think you're out of luck there.

Perhaps you could do:

a) set default tablespace for user before import to the target on
b) remove *all* the clauses

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Remove extra info about SEGMENT

My reason

More to Explore

PL/SQL demos

PL/SQL docs

Get DDL table without segment attributes but with tablespace

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Remove extra info about SEGMENT

My reason

More to Explore

PL/SQL demos

PL/SQL docs