Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Moving tables from a tablespace
will this help if the tables to be moved are system tables present in the user
tablespace and you want them to move into user table space
Anurag
also reply at
anurag_at_synergy-infotech.com
Steve McDaniels wrote:
> in the destination tablespace, create a user to "receive" the tables.
> in the source tablespace, grant select on the tables to this user.
> in the source tablespace, create yourself a script something like
>
> select 'create table ' || table_name || ' as select * from ' || owner || '.'
> || table_name from all_tables
>
> this script produces something like this:
>
> 'CREATETABLE'||TABLE_NAME||'ASSELECT*FROM'||OWNER||'.'||TABLE_NAME
> ----------------------------------------------------------------------------
> --
> create table DUAL as select * from SYS.DUAL
> create table SYSTEM_PRIVILEGE_MAP as select * from SYS.SYSTEM_PRIVILEGE_MAP
> create table TABLE_PRIVILEGE_MAP as select * from SYS.TABLE_PRIVILEGE_MAP
> create table STMT_AUDIT_OPTION_MAP as select * from
> SYS.STMT_AUDIT_OPTION_MAP
> create table AUDIT_ACTIONS as select * from SYS.AUDIT_ACTIONS
>
> SPOOL this to a file on your client.
>
> login as the user (into the detination tablespace)
>
> START this file.
>
> <raju_pillai_at_yahoo.com> wrote in message news:7voea8$4og$1_at_nnrp1.deja.com...
> > We have a bunch of tables in a single tablespaces and I would like to
> > move them to different tablespaces. I have already created the necessary
> > tablespaces for those tables including tablespaces for their indexes.
> > Now I am ready to move the tables and the data. How can I do this in the
> > most efficient way. I also need the indexes and contraints for those.
> > Can anybody suggest me the best way to accomplish this.
> >
> > Any input is greatly appreciated.
> > Thanks
> >
> > Raju
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
Received on Thu Nov 11 1999 - 03:01:22 CST