Thanks a lot Richard... I'm right now cloning test db
to development db to see how it goes. I'll try
creating views as per your suggestion but another
easier way would be to create an empty test schema(
testctl) and give it rights to alter all objects of
prodctl. This way testctl will have full acess to
prodctl objects. How is that? Will this not be simpler
& easier?

thanks for the help so far..

--- Richard Quintin <[Email Address Removed] wrote:
> You are correct that they will not occupy space
> (other than to store the
> definition of the view).
>
> For the alter table/index problem you could grant
> alter any table and alter
> any index to the test schemas.
>
> Richard Quintin
> VTLS, Inc.
> http://www.vtls.com/
>
> -----Original Message-----
> From: Ashish Saxena [mailto:[Email Address Removed] Sent: Thursday, October 31, 2002 11:54 AM
> To: LazyDBA.com Discussion
> Subject: RE: How to rename a schema without dropping
> it?
>
>
> Thanks Richard. I don't think creating views will
> occupy extra space as views are just windows to the
> tables. Correct? But what will happen to the
> indexes??? They will still by owned by PRODCTL &
> PRODDTA.
>
> OK.. How about this? What if I keep all tables &
> indexes in prodctl schema but give all DML
> privileges
> to testctl? Will it let testctl schema to
> access,change?? It will not let testctl schema to
> alter prodctl tables or create indexes....
>
> --- Richard Quintin <[Email Address Removed] wrote:
> > 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/
>
> =====
> 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] To subscribe: send a blank email to
> oracledba-[Email Address Removed] Visit 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 documentation is here:
> http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
> To unsubscribe: send a blank email to
> oracledba-[Email Address Removed] To subscribe: send a blank email to
> oracledba-[Email Address Removed] Visit 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
>


=====
Ashish Saxena
Oracle DBA

__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
Oracle LazyDBA home page