1 2 Previous Next 18 Replies Latest reply: Mar 7, 2013 2:23 AM by Nitesh. RSS

    Need a query..

    Nitesh.
      My oracle version is 11.

      I want to prepare a DDL scripts for all the below mentioned objects ..

      1. Tables with all options

      2. Views

      3. Types

      4. Indexes

      5. Packages / Procedures / Functions/ Triggers – Only object name

      6. Triggers – Trigger Source

      7. Grants to other schemas – other than PSUSR, PSQRYUSR,PSAPEX & PSBROWUSR

      8. Materialized Views

      9. Sequences

      10. Directories

      11. JAVA Objects

      12. Schedulers List



      I have tried package select dbms_metadata.get_ddl(table_name) from dual; which fetches single table and in which i have to mention each table name but i even tried like this
      select dbms_metadata.get_ddl('a.table_name) from user_tables but its not working i dont know as where i am going wrong and its throwing this error ..


      ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_DDL'
      06553. 00000 - "PLS-%s: %s"
      *Cause:   
      *Action:

      Kindly give your ideas.. Thanx too ..

      For other objects like triggers its i know as i have to use get_dependant_ddl but its not just one object its i have to fetch all objects which are available in the particular schema ..

      Rgds,
      Nitesh pareek.
        • 1. Re: Need a query..
          Nitesh.
          I even tried as SELECT DBMS_METADATA.get_ddl ('TABLE', 'a.table_name') from user_tables a but no use...

          some one give their suggestions in this same query..
          Rgds,
          Nitesh pareek.
          • 2. Re: Need a query..
            Manik
            How about export and import features of oracle? Think.

            Also look into :
            EXPDP
            That can produce a full export of all the objects (with or without data) in a schema.

            http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm
            http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php

            Cheers,
            Manik.

            Edited by: Manik on Mar 7, 2013 10:11 AM
            • 3. Re: Need a query..
              Nitesh.
              Tel manik as how to do that so .. ,Lets try that then because i never worked with those features .. Give me some link or any query ..

              Rgds,
              Nitesh pareek.
              • 4. Re: Need a query..
                sb92075
                Niteshprk wrote:
                My oracle version is 11.

                I want to prepare a DDL scripts for all the below mentioned objects ..

                1. Tables with all options

                2. Views

                3. Types

                4. Indexes

                5. Packages / Procedures / Functions/ Triggers – Only object name

                6. Triggers – Trigger Source

                7. Grants to other schemas – other than PSUSR, PSQRYUSR,PSAPEX & PSBROWUSR

                8. Materialized Views

                9. Sequences

                10. Directories

                11. JAVA Objects

                12. Schedulers List



                I have tried package select dbms_metadata.get_ddl(table_name) from dual;
                spool make_sql.sql
                SELECT 'SELECT DBMS_METADATA.GET_DDL(' ||CHR(39) || TABLE_NAME || CHR(39) || ') FROM DUAL;' FROM USER_TABLES;
                SPOOL OFF
                @make_sql.sql
                • 5. Re: Need a query..
                  Purvesh K
                  It works for me this way:
                  create table test_table (col1 number primary key, col2 varchar2(10));
                  create table test_table1 (col12 number primary key, col22 varchar2(10));
                  create table test_table2 (col13 number primary key, col23 varchar2(10));
                  create table test_table3 (col14 number primary key, col24 varchar2(10));
                  create table test_table4 (col15 number primary key, col25 varchar2(10));
                  create table test_table5 (col16 number primary key, col26 varchar2(10));
                  create table test_table6 (col17 number primary key, col27 varchar2(10));
                  create table test_table7 (col18 number primary key, col28 varchar2(10));
                  create table test_table8 (col19 number primary key, col29 varchar2(10));
                  create table test_table9 (col10 number primary key, col20 varchar2(10));
                  
                  
                  select dbms_metadata.get_ddl('TABLE', table_name) from user_tables where table_name like 'TEST%';
                  
                  "
                    CREATE TABLE "XXX"."TEST_TABLE" 
                     (     "COL1" NUMBER, 
                       "COL2" VARCHAR2(10), 
                        PRIMARY KEY ("COL1")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE1" 
                     (     "COL12" NUMBER, 
                       "COL22" VARCHAR2(10), 
                        PRIMARY KEY ("COL12")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE2" 
                     (     "COL13" NUMBER, 
                       "COL23" VARCHAR2(10), 
                        PRIMARY KEY ("COL13")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE3" 
                     (     "COL14" NUMBER, 
                       "COL24" VARCHAR2(10), 
                        PRIMARY KEY ("COL14")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE4" 
                     (     "COL15" NUMBER, 
                       "COL25" VARCHAR2(10), 
                        PRIMARY KEY ("COL15")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE5" 
                     (     "COL16" NUMBER, 
                       "COL26" VARCHAR2(10), 
                        PRIMARY KEY ("COL16")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE6" 
                     (     "COL17" NUMBER, 
                       "COL27" VARCHAR2(10), 
                        PRIMARY KEY ("COL17")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE7" 
                     (     "COL18" NUMBER, 
                       "COL28" VARCHAR2(10), 
                        PRIMARY KEY ("COL18")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE8" 
                     (     "COL19" NUMBER, 
                       "COL29" VARCHAR2(10), 
                        PRIMARY KEY ("COL19")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  "
                    CREATE TABLE "XXX"."TEST_TABLE9" 
                     (     "COL10" NUMBER, 
                       "COL20" VARCHAR2(10), 
                        PRIMARY KEY ("COL10")
                    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX"  ENABLE
                     ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
                    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
                    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
                    TABLESPACE "XXX" 
                   "
                  • 6. Re: Need a query..
                    Purvesh K
                    Niteshprk wrote:
                    I even tried as SELECT DBMS_METADATA.get_ddl ('TABLE', 'a.table_name') from user_tables a but no use...

                    some one give their suggestions in this same query..
                    Rgds,
                    Nitesh pareek.
                    Remove the Single Quotes before and after a.table_name. With Quotes, Oracle will consider it to be the Table Name and might return an error that Table/Object Does not exist. See the example I posted.
                    • 7. Re: Need a query..
                      Nitesh.
                      You are using where clause but i dont want to filter anything as i require all the tables so there is no need of any where clause and i am getting this error while executed this query after correcting it ..



                      select dbms_metadata.get_ddl('TABLE', a.table_name) from user_tables a



                      ORA-31603: object "SYS_IOT_OVER_743606" of type TABLE not found in schema "PSDBA"
                      ORA-06512: at "SYS.DBMS_METADATA", line 5088
                      ORA-06512: at "SYS.DBMS_METADATA", line 7589
                      ORA-06512: at line 1
                      • 8. Re: Need a query..
                        Purvesh K
                        Niteshprk wrote:
                        You are using where clause but i dont want to filter anything as i require all the tables so there is no need of any where clause and i am getting this error while executed this query after correcting it ..



                        select dbms_metadata.get_ddl('TABLE', a.table_name) from user_tables a



                        ORA-31603: object "SYS_IOT_OVER_743606" of type TABLE not found in schema "PSDBA"
                        ORA-06512: at "SYS.DBMS_METADATA", line 5088
                        ORA-06512: at "SYS.DBMS_METADATA", line 7589
                        ORA-06512: at line 1
                        Yes, I used the Where clause to filter some few table from my environment which contains thousands of tables.

                        Use following to by-pass IOT's:
                        SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
                             FROM USER_TABLES u
                             WHERE u.nested='NO' 
                             AND (u.iot_type is null or u.iot_type='IOT');
                        • 9. Re: Need a query..
                          Vite DBA
                          Hi Nitesh,

                          from a learning point of view, everything these guys have told you is valuable. From a do it the easy way point of view, install a copy of SQL Developer (if you don't already have it) and go to Tools/Database Export.

                          From there follow the prompts in the wizard.

                          If you have some other tool such as TOAD or PLSQL Developer, then I'm sure you can find the equivalent functionality.

                          Regards
                          Andre
                          • 10. Re: Need a query..
                            Nitesh.
                            Tq very much frdsi have got it finally and for dependant objects i might come back here if i require any help means...

                            Rgds,
                            Nitesh.
                            • 11. Re: Need a query..
                              Nitesh.
                              Hey for

                              Materialized Views
                              Directories
                              JAVA Objects
                              Schedulers List

                              Its dbms_metadata is not supporting as showing error as materialized view ..

                              ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in function GET_DDL
                              ORA-06512: at "SYS.DBMS_METADATA", line 5088
                              ORA-06512: at "SYS.DBMS_METADATA", line 7589
                              ORA-06512: at line 1
                              31600. 00000 - "invalid input value %s for parameter %s in function %s"
                              *Cause:    A NULL or invalid value was supplied for the parameter.
                              *Action:   Correct the input value and try the call again.



                              I mean should i consider any other way for getting scripts for this objects ..


                              select DBMS_METADATA.get_ddl ('MATERIALIZED VIEW', view_name) from user_views a
                              /
                              SELECT DBMS_METADATA.get_ddl ('DIRECTORY', object_name)
                              FROM user_objects
                              /

                              I have tried in above way but its not working ..

                              Thanx a lot ...

                              Nitesh..
                              • 12. Re: Need a query..
                                jeneesh
                                The type should be MATERIALIZED_VIEW

                                DBMS_METADATA: Object Types
                                select dbms_metadata.get_ddl ('MATERIALIZED_VIEW', mview_name) 
                                from user_mviews a;
                                • 13. Re: Need a query..
                                  Nitesh.
                                  Hey thanx yes its worked .. What about grants ddl then i tried this one but not helping dude..

                                  SELECT DBMS_METADATA.get_granted_ddl('TABLE',GRANTEE)
                                  FROM user_tab_privs_made
                                  WHERE grantee NOT IN ('PSUSR', 'PSQRYUSR', 'PSAPEX', 'PSBROWUSR');

                                  Thankssss.
                                  • 14. Re: Need a query..
                                    Manik
                                    I dont think this approach will work for dependant objects.. I still stick on to my solution of Data Pump Export.

                                    http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm

                                    Cheers,
                                    Manik.
                                    1 2 Previous Next