Tablespaces again please

[amolso]

I have Following problems with tablespaces

1) Earlier a programmer before me created a single tablespace with a single dbf file of size 3 GB on a drive of our PC server.Now this tablespace contains a some valid data which i dont want to delete and i am also in the process of hosting data on a new server.

Can I create 4 seperate tablespaces in new server for the same tables and import data over there in those multiple tablespaces from current single tablespace.(As I know it is suggested that in one tablespace definition you should have multiple dbf files created therefore can I create multiple dbf in new tablespace)

As I observed due to the current tablespace specification i.e. 3 GB single dbf and also table created directly without specifying storage initial next and pctincrease parameters, each table occupies 20MB initial space which is absolutely not required for many tables where only 10 to 100 records are there( info obtained from sys.dba_extents). Can I reduce stoarage parameters in new server data import without affecting data.
How can I do it ? By import export or by sqlloader.
I have only single database which is default.
Do I have to rename tables in the current db and then load/import it in new server and then create old tables again with new tablespace and storage parameter specification and insert data from renamed tables to newly created tables ? OR
Do I have to make flat files of existing data and load it new tables after creation of database tables in new server OR
Is there any way in Import Export Facility which will satisfy my storage needs.
I have gone thru DBA books but it does not give any infon. on this issue. [sig][/sig]

 

[MikeLacey]

Ok -- one way to do it:

Create your new DB on your new server.

Create appropriate tablespaces in the new DB.

Create a database link from the New DB to the Old DB, call the link something like OldDB.

Copy the tables across using:

Create Table properly_sized_table ...... as Select * from really_big_table@OldDB;

Disadvantage of this method, of course, is that you have to repeat it for each table -- so if there are many it would be a pain.

Does anyone else know of a more elegant (easier I mean) way to do it? [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]

 

[amolso]

Thank you mike
Still my db server is not yet finalised but it will be hosted at some different place than my office. I don't know much about how database links are used to import database from two physically different hard disk.
If All of you can go through the suggestions mentioned by me then at least tell me what can be the problem that will occur and what is the alternate way solve the problem
Thanks
Amol
[sig][/sig]

 

[matrixcd]

There several ways to move data from one tablespace to another.
1. You can create the new tablespace and rebuild the table to go into that particular tablespace. With the rebuild rebuild table storage(initial next pctincrease maxextents)
tablespace

2. Then after rebuilding the table you can then export the table by owner if they are the same owner or use the select
option the is now available in 8i version assuming you are
using 8.0.5 and above.

3. Before import the table create the table and size it
per your requirements. Then import with the option
ingore=y to omit the error if the table is already there
ingore=y
fromuser=
touser=
All of this is assuming that you are familar with Oracle...

Also if is not imperative that you must have they table in the same tablepace I suggest that just rebuild them and then
export the tables and re-import them into your new Database.

I hope this help [sig][/sig]

 

[amolso]

Thank you Matrixcd
Well I didn't read this thread first and read the earlier one first. Your Information is really helpful for me. Please if you could give me infon. about Contention and RAID
Amol [sig][/sig]

 

[connectxyzzy]

BTW, doesn't anyone use the SQL*Plus copy command anymore? With that DBlink, I think a copy works better, especially if copycommit is set to limit the potential for using up your rollback segment space... [sig][/sig]

 

[MikeLacey]

Contention and RAID are two substantial issues..... You want to start another couple of threads on these Amol? [sig]<p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>Making mistakes, so you don't have to. &lt;grin&gt;[/sig]