6 Replies Latest reply: Oct 15, 2012 4:34 AM by Osama_Mustafa RSS

    User and Tablespace info

    945592
      Hi,

      I have to create some scripts to create a new and empty DB ready for a new install of our apps on a new server.

      I am easily getting the tables, views etc info from SQL Developers Export facility (without the data of course), but I have a problem where I need to define the userid and the TableSpace.

      I realise these are quite straight forward, but what I would like to know is how the original ones are defined on our current DB, as the original scripts (and info) are no longer available, and I need to make sure I create them correctly with the new scripts.

      So how do I display all the required info for a userid and for a TableSpace so that I can use this info in the create commands in the scripts ?

      It may be helpful for you to know this is my first attempt at scripting and only my 2nd attempt at and interfacing with Oracle, so I have limited knowledge in this field, hence the simple request. :)

      Any help or advice would be most appreciated.

      Cheers, George.
        • 1. Re: User and Tablespace info
          vlethakula
          dbms_metadata.get_ddl is the package, you can use to get metadata of users definitions and objects owned;

          For users definition(say test in this example)

          set long 1000000

          select dbms_metadata.get_ddl('USER','TEST') from dual;
          • 2. Re: User and Tablespace info
            sb92075
            942589 wrote:
            Hi,

            I have to create some scripts to create a new and empty DB ready for a new install of our apps on a new server.

            I am easily getting the tables, views etc info from SQL Developers Export facility (without the data of course), but I have a problem where I need to define the userid and the TableSpace.

            I realise these are quite straight forward, but what I would like to know is how the original ones are defined on our current DB, as the original scripts (and info) are no longer available, and I need to make sure I create them correctly with the new scripts.

            So how do I display all the required info for a userid and for a TableSpace so that I can use this info in the create commands in the scripts ?

            It may be helpful for you to know this is my first attempt at scripting and only my 2nd attempt at and interfacing with Oracle, so I have limited knowledge in this field, hence the simple request. :)

            Any help or advice would be most appreciated.

            Cheers, George.
            DBMS_METADATA.GET_DDL

            or just export (expdp) & import (impdp)

            Read The Fine Manuals found at http://docs.oracle.com
            • 3. Re: User and Tablespace info
              Osama_Mustafa
              Oracle Database provides a PL/SQL package that enables you to determine the DDL that created an object and data dictionary views that you can use to display information about schema objects

              Use
              DBMS_METADATA.GET_DDL
              Read
              http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm
              • 4. Re: User and Tablespace info
                jgarry
                dbconsole has show metadata commands, useful if you just have a few objects to worry about.
                • 5. Re: User and Tablespace info
                  945592
                  Hi all, thanks for the info, the dbms_metadata.get_ddl def did the trick :)
                  • 6. Re: User and Tablespace info
                    Osama_Mustafa
                    Please mark this thread answered .