This content has been marked as final. Show 3 replies
Jim Thompson wrote:There are two things
I am planning to move a 10g Database from a Windows 2003 server to another Windows 2008 server and also roll in a database upgrade to 11gR2. Hence I will use the export / import method of upgrade.
This will also allow me to rearrange some of the storage settings as part of the migration. However I want to check a couple of things
Q1 ) Does datapump not export SYSTEM and SYS owned objects or anything that is in the SYSTEM tablespace ( I have found a couple of objects in the SYSTEM tablespace space that someone naughty has placed in there - I am wondering will the export fail to export these because they are in the SYSTEM tablespace or am I mistaking this for SYSTEM / SYS owned objects ? )
If you own table under SYS users then these wont be exported but this case is not seen with SYSTEM user. You may able to export SYSTEM objects.
So before upgrade/export, move these objects to other schema and then do the export. There are couple of ways achiving this i.e move objects to other user a) use exp b) CTAS
Q2 ) The current database has some tablespaces that have multiple data files. Assuming that the file system / SAN on the new target environment is efficiently created, would I be better consolidating these data files into 1 single data file per tablespace.If there are multiple disk controllers then assign these controllers to disk equally. For e.g 2 diskcontroller for 10 disk which leaves 1 for each 5. Now on those 5 disk you can create n numbers of datafile or just one, result would be same because its the controller which actually do stripping/load balancing.
Best is to use ASM which do stripping/mirroring for you.
Furthermore would it be better to create such tablespaces as type BIGFILE ? what are the pro's / con's of BIGFILE ( I know you can only have 1 such datafile per tablespace )BIGFILE are generlly used for dataware house such as loading unloading lots of data. There are no such pro's/con's for bigdatafile. But its the requirement which suits you best.
Q3) Traditionally when checking the density of my database in the past for Dictionary Managed tablespaces I would have simply sumed the bytes in dba_segments and compared that to the sum of bytes in dba_data_files. However I guess for locally managed tablespaces, this comparison is meaningless as these dictionary views do not contain the storage info for locally managed tablespaces. How would you calculate this for locally managed tablespaces ?To check size of database used:
To check size of complete database( Including Free + Used)
SQL>select sum(bytes/1024/1024/1024) from dba_segments;
To check free space in database
SQL>select sum(bytes/1024/1024/1024) from dba_data_files;
SQl>select sum(bytes/1024/1024/1024) from dba_free_space;