Monday, July 19, 2021

Generate Table, Index or tablespace DDLs in oracle

When migrating database from one server to another you need Tablespace creation DDL. Also it is very useful when copying specific table or index or exp/imp from one database to databases.

Here are few ways to generate the tablespace script from source database.

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL>SPOOL TBS_DDL.SQL
SQL>SELECT DBMS_METADATA.GET_DDL('TABLESPACE',DBA_TABLESPACES.TABLESPACE_NAME) FROM DBA_TABLESPACES;
SQL>SPOOL OFF

You can see below command if you want the TABLESPACE creation script specific to one Table space.

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','&TABLESPACE_NAME') FROM dual;

You can use below command to generate specific TABLE, INDEX Script from a user

Syntax:-
select dbms_metadata.get_ddl('TABLE','<TABEL NAME>','<SCHEMA>') from dual;
select dbms_metadata.get_ddl('INDEX','<INDEX NAME>','<SCHEMA>') from dual;


Ex:-
select dbms_metadata.get_ddl('TABLE','EMP_SAL','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','EMPNO_IDX','EMP') from dual;


You can use below command to all TABLE, INDEX Script from a user. First, connect to user

SQL>SET ECHO OFF;
SQL>SET HEADING OFF;
SQL>SET LINESIZE 1000;
SQL> SET LONG 60000;
SQL> SET FEEDBACK OFF;
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE',U.TABLE_NAME)
FROM USER_TABLES U;
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)
FROM USER_INDEXES U;  

In case if you need a View DDL, you can run below command;
SQL>select DBMS_METADATA.GET_DDL('VIEW',<View_Name>’) from dual;
or
SQL>select DBMS_METADATA.GET_DDL('VIEW','<view_name>','<schema_name>') from DUAL;

You can run below command to generate the DDL statements of a SCHEMA
set pages 30000
set linesize 1000
set lines 500
SQL> SELECT DBMS_METADATA.GET_DDL('USER','<schema_name>') FROM dual;

You can run below command to generate DDL statements for more than one schema

SQL> SELECT DBMS_METADATA.GET_DDL('USER',U.USERNAME) FROM DBA_USERS U WHERE USERNAME IN ('USER1','USER2');

You can run below command to generate DDL statement of the System Grant/ role granted/object granted to a schema owner

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema_name>') from dual;
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',<Schema_Name>’) from dual;

You can run below command to generate DDL statement of the role
SQL> SELECT DBMS_METADATA.GET_DDL('ROLE','RESOURCE') from dual;

You can also use SQL Developer to get the DDL script for a specific object.

You can refer oracle documentation for more details on DBMS_METADATA

Thanks & Regards
http://oracleracexpert.com






11 comments:

  1. Adobe Premiere Rush Crack With this all-in-one app for creating and sharing videos online, you can provide your channels with a steady stream of cool content – quickly and easily.
    Little Alterboy Crack
    IK Multimedia Sampletank Mac Crack
    Pro Evolution Soccer 2021 PC Game Download

    ReplyDelete
  2. I’m glad you got value from the post!
    Reference: https://v8web.com/

    ReplyDelete
  3. Within this webpage, you'll see the page, you need to understand this data. 토토커뮤니티

    ReplyDelete
  4. Thanks for your article. One other thing is that if you are disposing your property by yourself, one of the difficulties you need to be conscious of upfront is how to deal with household inspection reviews. As a FSBO vendor, the key concerning successfully moving your property plus saving money about real estate agent commissions is understanding. The more you recognize, the better your property sales effort will be. One area in which this is particularly essential is inspection reports. 토토커뮤니티

    ReplyDelete
  5. Profit primarily prime quality items -- you can understand them all within: 먹튀검증사이트

    ReplyDelete
  6. It is somewhat fantastic, and yet check out the advice at this treat. 먹튀검증사이트

    ReplyDelete
  7. I read this article. I think You put a great deal of exertion to make this article. I like your work. 토토커뮤니티

    ReplyDelete
  8. Beaver says I also have such interest, you can read my profile here: https://voyance-tel-avenir.com

    ReplyDelete
  9. Minister of Education, Bangladesh Mr. Nahid Hasan has announced this year PSC Result 2022 will be announced in the last week of December PSC Result 2022 Dhaka Board 2022 with the total or full mark sheet of the Primary School Certificate Exam, based on previous years schedule, once the official date is confirmed we will update here with timings.

    ReplyDelete
  10. SCERT Odisha 2nd Class Book
    Odisha 1st, 2nd, 3rd, 4th, 5th Class Textbook 2023 Should be Followed as the Prime Resource Throughout the year to clear All doubts and Strengthen your knowledge, SCERT Odisha Provides easy Explanation for Various concepts in Curriculum. It Elaborates each Concept with the help of a Number of Problems and examples. SCERT Odisha 2nd Class Book we will update the Information on this page. SCERT Odisha 1st, 2nd, 3rd, 4th, 5th e-Books 2023 Latest Vertion to be Upload Every Year in Online Mode. Textbooks are Very Important to Students on the Preparations of the Monthly and Final Examination.

    ReplyDelete