Could you use views from test schema to prod schema?
begin
for x in (select table_name from dba_tables where owner = 'PRODCTL') loop
execute immediate 'create view '||x.table_name||' as select * from
prodctl.'||x.table_name;
end loop;
end;
Richard Quintin
VTLS, Inc.
http://www.vtls.com/
-----Original Message-----
From: Ashish Saxena [mailto:[Email Address Removed] Thursday, October 31, 2002 11:40 AM
To: LazyDBA.com Discussion
Subject: RE: How to rename a schema without dropping it?
Thanks Anil but my problem is that how do I change the
names of the production schemas on the newly cloned
test database to the test schema names???????
After cloning, on the test database, this is what I
want: Rename proddta to testdta and prodctl to
testctl.
Ashish
"Dongare, Anil" <anil.[Email Address Removed] Perfect plan. But u don't need step 7. i.e. Recover
> DB by applying logfiles.
> B'coz u r shutting down production DB and then
> copying the datafiles. just
> like cold backup.
>
> -anil
>
> > -----Original Message-----
> > From: Ashish Saxena [SMTP:[Email Address Removed] > Sent: Thursday, October 31, 2002 4:29 PM
> > To: LazyDBA.com Discussion
> > Subject: How to rename a schema without dropping
> it?
> >
> > Dear fellow DBAs,
> >
> > I have a monthly export import process that
> refreshes
> > a test database(no archive mode) with production
> > database(archive log mode). Production database
> size
> > is 45GB & data is 32GB. So really 32GB of data
> gets
> > imported to the test database(on same box)There
> are
> > only 2 application schemas that are refreshed as
> they
> > constitute 99% of data.
> >
> > My issue is that inspite of fine tuning the import
> > process (data 1st,nologging in index
> creation,noarch
> > mode and all that good stuff), it takes around 36
> > hours to import 32 GB data & that comsumes my
> whole
> > weekend. I'm striving to reduce this time.
> >
> > Following is my plan. Its a cloning cum db name
> change
> > process by re-creating controlfile. Please advise
> &
> > correct me if this is not possible.
> >
> > 1. On prod db, bkp controlfile to trace.
> > 2. Shutdown production database and test database
> &
> > delete all control,redo & datafiles of test
> database.
> > 3. Copy all prod datafiles to where I want the
> test db
> > files to reside. Startup the production database
> for
> > normal use.
> > 4. Edit the controlfile bkp taken in step1 with
> the
> > redolog file, datafile locations specified in
> step3.
> > Also change the database name in the bkp ctl
> > file(create controlfile set database "test")
> > 5. Change oracle sid to "test".
> > 6. With startup nomount mode, run the edited
> > controlfile created in step 4.
> > 7. Recover the database by applying online
> redologs(if
> > required)
> > 8. Open the dtaabase with resetlogs.
> >
> > This clones the production database to test
> database &
> > will take less than 2 hours.
> >
> > But my problem is that the prod schemas
> > prodctl,proddta) retain their names in the test
> > database & cusotmer doesn't want that. AFTER
> CLONING
> > PROD DATABASE & CHANGING DB NAME, I WANT TO CHANGE
> THE
> > PROD SCHEMAS IN TEST DATABASE FROM prodctl,proddta
> to
> > testctl,testdta. Is there a way to do this????
> >
> > Please advise if someone has done this before or
> has a
> > better plan to reduce the data refresh times.
> >
> > Thanks in advance!
> > Ashish
=====
Ashish Saxena
Oracle DBA
__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page