System Tablespace Grows on Import

[sukimac]

Hi,

We're using export/import as our backup/recovery strategy for the moment. We run an full export, as the sys user, off one box and a user import as sys into another box. I checked the import log yesterday saw a unable to extend on the System tablespace error message. Sure enough the System tablespace was full - is this b/c we're importing as the Sys user? Any suggested fixes for this are appreciated.

[marist89]

[QUOTE][i]Originally posted by sukimac [/i]
[B]Hi,

We're using export/import as our backup/recovery strategy for the moment.[/B][/QUOTE]
Big mistake. You can lose too much data and there are too many bugs with imp/exp to use as a backup methodology.

That being said, your data dictionary will get updated with any new information from the objects in the import. If you are importing a large number of tables,views, etc., you will consume some space. However, if you have adequate space in your system tablespace, it should not fill up. I like to keep about 64M free in my system tablespace.

Some things to check:
1. make sure that your objects are not being created in the SYSTEM tablespace.
2. check to make sure SYS's temporary tablespace is not SYSTEM.
3. check to make sure your RBS are not in the SYSTEM tablespace.
4. check to make sure other users don't have SYSTEM as their default and temporary tablespace.

[jmodic]

No, it has nothing to do with the user that is performing the imp.

There are two possible reasons while your system tablespace is too small:

1. Your system TS was simply created not large enough to hold the database dictionary. With each new object that has been imported (users, tables, indexes, views, constraints, procedures, packages, .....) new informations have about this objects have to be written to the dictionary in the system tablespace. If the growing dictionary tablesand indexes can not find any additional free space in the system tablespace your imp will bombed out. Simply enlarge your SYSTEM tablespace.

2. Your schemas you are importing to have default tablespace set to SYSTEM and do not have any quota on the originaly_named tablespaces where tables were exported from. If this is the case you should recreate those users and assign them different tablespace.

HTH,

[sukimac]

Sys is the only user whose default ts is System. The System dbf was 275M - we've been doing the exp/imp script since October (every 10 minutes) so I suppose its no surprise that System filled up. This is an interim backup strategy - we're waiting for development to finish and then we're going with Standby DB.