We are using oracle 8i std edition on solaris. We want to create a locally
managed tablespace for a particular schema. The objects of this schema
will be imported every day from the same schema on another same database server.
The existing schema tablespace is locally managed with system allocation type.
1.We want to know what are the points to be kept in mind while creating a locally
managed tablespace in the new db.
2. Should the tablespace have a single datafile or more than one(The existing one has
3 datafiles)? Which one is better? (There will be no activity on this new db as it is
for standby only. If the existing one fails, this db will be used.
4. Since we are going to export from the existing one and import into the new db schema
every day ( the schema in the new db will be dropped and recreated daily before import),
will there be any problems that we will encounter by doing so?
Please let me know all the information so that we do not face any problem after
Note: Currently the failover schema tablespace is dictionary managed and we plan to
entirely drop this tablespace and create a new one by the same name but locally managed
this time. So what steps to be taken while dropping the tablespace and creating a new one
by the same name? Will there be any problems because of this? Is a db restart required
after this? Kindly enlist the points and steps.
1. Rather a broad question, but you must lose some of your hangups from the DMT days. It's not bad to have a lot of extents per segment (up to 3000, say) for example.
2. There are various reasons for having multiple datafiles ...
a) load balancing -- placing a file on each i/o device allows the load to be spread
b) avoiding too-large files -- for convenience in file-based operations
If you have multiple arrays over which you want to spread the tablespace i/o, then use an LMT with a pretty small (eg. 1Mb) uniform extent size and one, two etc. files per array. This is a poor man's striping methodology that works pretty well.
4. I wouldn't expect any problems with that.
I don't think that a restart will be required.
I'm inclined to wonder whether export/import is the best methodology for this operation. Have you considered taking a full backup to clone the database, or some other technique that would be faster, and probably more reliable? (I'm not a big fan of export/import)
Dropping the TS is just a matter of running the "DROP TABLESPACE" command -- see the SQL Reference for syntax and options.
I'm not sure what problems you might anticipate with exp/imp, other than performanceon the import. I would say you should just try the process and monitor the it for any issues, dealing with them as they turnup.
What constraints prevent you from considering other methodologies?