Home » SQL & PL/SQL » Client Tools » Schema Export, Role Export. (Oracle 10g Release 10.2.0.4.0/SQL Developer - 2.1.1.64/Windows XP sp3)
Schema Export, Role Export. [message #493059] Fri, 04 February 2011 08:22 Go to next message
oruorainfo
Messages: 46
Registered: May 2010
Member
Hello, am in the process of splitting an existing schema into several logical parts. This schema is fairly old and was inherited from a previous project. While most of the DDLs are available, the Schema DDLs and Role DDLs are missing. There are at least 3 Schemas and 2 Roles.

Is there any way please to Export the Schema and Roles definitions (in order to build the Create Statements) either through SQL Developer or by running a specific SQL statement to gather the full Schema and Role "Create" statements?

Goal: Essentially i am attempting to reverse engineer the existing Schema Create statements from the database.

Purpose: I will then use these as a model, to build my new Schema Create statements.

For Example am trying to generate from the Database:

CREATE SCHEMA { [ schemaName AUTHORIZATION user-name ] | [ schemaName ] |
[ AUTHORIZATION user-name ] }


Thank You kindly.



Re: Schema Export, Role Export. [message #493061 is a reply to message #493059] Fri, 04 February 2011 08:34 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
...
SQL Developer -> Tools -> Database Export ... Specify Objects.

[Updated on: Fri, 04 February 2011 08:37] by Moderator

Report message to a moderator

Re: Schema Export, Role Export. [message #493068 is a reply to message #493061] Fri, 04 February 2011 09:23 Go to previous messageGo to next message
oruorainfo
Messages: 46
Registered: May 2010
Member
Thank you LKBrwn_DBA, and my apologies for posting to wrong forum.

I did try your suggestion before posting (also tried with version 3), but neither allows the export of the "Schema Definition" or "Create statement" nor the "Roles" in the database.

What it does allow or generate are all the objects "within" the Schema.

What i require is the actual "Create Statement" for the Schema definition and the "Role". Not the contents (i.e. Objects).

Any other suggestions that i might try please?

Many Thanks!
Re: Schema Export, Role Export. [message #493074 is a reply to message #493068] Fri, 04 February 2011 09:59 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
If you are not going to create the schema objects, just use CREATE USER and CREATE ROLE statements.

[Updated on: Fri, 11 February 2011 07:56] by Moderator

Report message to a moderator

Re: Schema Export, Role Export. [message #493075 is a reply to message #493068] Fri, 04 February 2011 10:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the ONLY case I will recommend to use TOAD.
Select your objects (schemas, roles or anything), right-click and choose "Create script".
If you trust TOAD (that is only use the latest version of TOAD on all but the latest versions of Oracle), then it is worth it.

Regards
Michel
Re: Schema Export, Role Export. [message #493076 is a reply to message #493074] Fri, 04 February 2011 10:36 Go to previous messageGo to next message
oruorainfo
Messages: 46
Registered: May 2010
Member
Thank you LKBrwn_DBA....
Re: Schema Export, Role Export. [message #493077 is a reply to message #493075] Fri, 04 February 2011 10:37 Go to previous messageGo to next message
oruorainfo
Messages: 46
Registered: May 2010
Member
Thank you Michel, i will give that a try...
Re: Schema Export, Role Export. [message #493078 is a reply to message #493059] Fri, 04 February 2011 10:51 Go to previous messageGo to next message
John Watson
Messages: 7212
Registered: January 2010
Location: Global Village
Senior Member
You do know that CREATE SCHEMA doesn't actually create a schema? From the docs:Quote:

This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user (see CREATE USER). This statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.
Re: Schema Export, Role Export. [message #493079 is a reply to message #493078] Fri, 04 February 2011 11:06 Go to previous messageGo to next message
oruorainfo
Messages: 46
Registered: May 2010
Member
Thank you John Watson, you are correct.
Re: Schema Export, Role Export. [message #493080 is a reply to message #493075] Fri, 04 February 2011 11:10 Go to previous message
oruorainfo
Messages: 46
Registered: May 2010
Member
OK Michel, Thanks! Have downloaded and installed Toad and yes, the Create Script is significantly more helpful!

This pretty much gets me what i need...

Thanks again.
Previous Topic: Problem in Export in TOAD
Next Topic: SQL Developer migration process
Goto Forum:
  


Current Time: Thu Jan 18 00:56:36 CST 2018

Total time taken to generate the page: 0.01906 seconds