Xref: alice comp.databases.oracle.server:83117
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!nntp2.deja.com!nnrp1.deja.com!not-for-mail
From: Ed Stevens <Ed.Stevens@nmm.nissan-usa.com>
Newsgroups: comp.databases.oracle.server
Subject: Relocating SYSTEM’s objects.
Date: Wed, 02 Feb 2000 20:45:07 GMT
Organization: Deja.com - Before you buy.
Lines: 58
Message-ID: <87a50h$gb$1@nnrp1.deja.com>
X-Article-Creation-Date: Wed Feb 02 20:45:07 2000 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 4.01; Windows NT)
X-Http-Proxy: 1.0 NMMSIT02, 1.0 x39.deja.com:80 (Squid/1.1.22) for client 209.64.87.68
X-MyDeja-Info: XMYDJUIDnmmc

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.