Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Create a SQL script used to create a new copy of a user's schema.

Re: Create a SQL script used to create a new copy of a user's schema.

From: Bill Hockridge <beh_inc_at_ix.netcom.com>
Date: 1997/06/11
Message-ID: <339EB3B2.1CD9082C@ix.netcom.com>#1/1

No.
The problem with that approach is that the initial extents in the storage clause are gigantic. Even though there is no data, the import tries to allocate the initial extents during table creation in the new schema. I don't have 12gb of space on my NT so the first or second tables get created, consume all of the tablespace I have, and the remaining 200+ tables creations fail.

I have tried to create a SQL script using the IMP argument indexfile=<filename>, edit the file to uncomment the create table commands, and then remove the storage clause from the create table and create index commands. The SQL script ran OK and the tables were created but when I re-ran the IMP command with the argument ignore=yes, there were some "alter table add constraint <constraintname> primary key..." commands which also had storage clauses on them (which blew up because of no space available).

When I worked at Northern Telecom, they had a UNIX executable which would extract any or all information from the data dictionary and build a SQL script that would reproduce the information requested. I guess I am looking for something simular in the public domain.

Thanks for your input Gerard,
Bill

Gerard H. Pille wrote:

> An export without data should do the trick, no?
>
> Bill Hockridge <beh_inc_at_ix.netcom.com> schreef in artikel
> <339D7D2D.26FCAABE_at_ix.netcom.com>...
> > Does anyone have a script, executable, process, etc. that will read
 an
> > existing user's schema and create a SQL script that, if run, would
> > create a new copy of the schema.
> >
> > I need to downsize an existing schema (storage parameters are
 gigantic)
> > into a baseline schema that will be used for regression testing.
> >
> > Thanks in advance for any help provided,
> >
> > Bill Hockridge - Independent Oracle DBA.
> > beh_inc_at_ix.netcom.com
> >
> >
Received on Wed Jun 11 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US