Relocating SYSTEM’s objects.

This is a follow on to previous msgs regarding default tablespaces for user SYSTEM, wherein we had allowed SYSTEM to have the system tablespace as his default. Now I’m am practicing for a migration effort to move all of his objects from the system tablespace to the recommended TOOLS. This is a bit lengthy, but please bear with me.

Running Oracle Standard Edition 8.0.5 on an NT 4.0 server.

I performed an export with OWNER=SYSTEM and ROWS=N, then did an import on the resulting file, with the INDEXFILE option. When I looked at THAT file I saw some sql syntax that didn’t look kosher to me (wouldn’t be the first time Oracle tools have built unexecutable code!). So I ‘unremarked’ the first DEFINE TABLE command in it, deleted the rest of the file, changed the table name to EDS_TEST, and ran it. It worked, so I tried to drop EDS_TEST. That returned “ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables.” So I read the docs on DBMS_AQADM.DROP_QUEUE_TABLE and tried that.

The syntax “exec dbms_aqadm.drop_queue_table(eds_test)” returned PLS- 00357: Table,View Or Sequence reference 'EDS_TEST' not allowed in this context

The syntax “exec dbms_aqadm.drop_queue_table(“eds_test”)” returned PLS- 00201: identifier 'eds_test' must be declared

So, what do I have to do to drop this table to clean up after myself.

And after that is done, what do I have to do to successfully migrate SYSTEM’s objects from the system tablespace to TOOLS? My plan was as follows:

1. cold full backup.
2. Export OWNER=SYSTEM, ROWS=N to “system_structure.sql”
3. Export OWNER=SYSTEM, ROWS=Y to “system_data.dmp”
4. Use Schema Mgr to drop everything owned by SYSTEM
5. Alter user SYSTEM to default tablespace TOOLS; also insure he has zero quota on system tablespace.
6. Unremark all “REM”ed definitions in system_structure.sql
7. Change all references to tablespace SYSTEM to tablespace to TOOLS in system_structure.sql
8. Execute system_structure.sql
9. Import from system_data.dmp

But now I have a few more worries (can you tell I’ve never done this before?). The first is the difficulty of dropping all of SYSTEM’s objects, based on the first problem described above, trying to drop a table I created. The second is, am I stepping on myself (or anyone else) by even for just a few minutes, dropping all of SYSTEM’s objects? Can I even do this while logged on as SYSTEM? The third concern is this quote from the docs:

“Note: Since Release 7.1, the commented CREATE TABLE statement in the indexfile DOES NOT include primary/unique key clauses” (Emphasis mine)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Feb 02 2000 - 14:45:07 CST