Re: Newbie needs rescue please!

Vince Laurent <vlaurent_at_networkusa.net> wrote:

>I learned after the fact NOT to use the default Oracle install. But
>that is history...Now I have a SYSTEM tablespace with MANY tables that
>need to be pulled out and put into thier own datafile. The tables
>are all named TLINKDBA.<something>. What I would like to do is:
>
>(a) create a new 1G datafile
>(b) export the tables from the SYSTEM tablespace
>(c) import them into the new datafile/tablespace
>(d) remove them from the SYSTEM table.
>
>Does anyone have any idea what the exact steps might be? Or can you
>point me in the direction of the repair?
>
>Thanks!

From the name I assume that the tables you want to move are in TLINKDBA's schema so here is one method:

Export ( in USER mode) all of TLINKDBA's tables.

Create a new tablespace and create your data files ( I would use more than 1 for 1GiG, so I could span several devices, but its your choice)

Grant unlimited quota on that tablespace to TLINKDBA REVOKE any rights to SYSTEM tablespace from TLINKDBA ( set Quota to 0)

Import ( USER mode) TLINKDBA's objects...( FromUser=TLINKDBA touser=TLINKDBA can be used)

After checking to be sure they all came in OK, write a sql script to delete tables where owner = 'TLINKDBA' and tablespace_name = 'SYSTEM'
( something like this:
  */ IN SqlPLus */
   set heading off
   set feedback off
   set linesize 1000
  set pagesize 0
  set pause off
 spool droptest.sql
 select 'drop table TLINKDBA.'||table_name ||';' from sys.dba_tables where owner = 'TLINKDBA' and tablespace_name = 'SYSTEM' /
spool off
** This should create a script 'droptest.sql' which you can run to drop the unwanted tables ( it may, and probably will, need editing first, so be careful

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =----- Received on Tue Jun 26 2001 - 11:07:33 CDT