| I want to move out SYSTEM objects from TEMP tablespace [message #52583] |
Tue, 30 July 2002 12:02  |
Azmat
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
Hi,
I have Oracle 8.0.5 database. My TEMP tablespace has PERMANENT status. I want to convert it to TEMPORARY status for optimal performance. But bcz of some SYSTEM objects (tables/indexes) lying into TEMP tablespace permanently, i am unable to convert my TEMP tablespace to TEMPORARY status.
How can i move the SYSTEM objects out of my TEMP tablespace safely so that my database keeps on running without any risk.
Your prompt response will be highly appreciated.
regards
Azmat
|
|
|
|
| Re: I want to move out SYSTEM objects from TEMP tablespace [message #52586 is a reply to message #52583] |
Tue, 30 July 2002 12:36  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
I am not sure about your version but try:
alter table TABLE_NAME move tablespace TABLESPACE_NAME
alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME
This will move those objects if you have the commands available. If not there isn't very many things that run under the SYSTEM user. I only run ORACLE_HOME/sqlplus/admin/pupbld.sql. You can always do a "select owner,segment_name,segment_type from dba_segments where tablespace_name=TEMP" to see what objects need to be moved. Once you have object names you can search the ORACLE_HOME/rdbms/admin directory for the file that creates it and read the script. Most scripts can be run again with no probs. The pupbld.sql wouldn't be a problem. Always do a backup before you make changes like this.
|
|
|
|
| Re: I want to move out SYSTEM objects from TEMP tablespace [message #52608 is a reply to message #52583] |
Wed, 31 July 2002 11:21 |
Azmat
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
Hi,
Thanks for your response. I am using Oracle 8.0.5 and when i query the TEMP tablespace i found following objects:
SYSTEM SQLPLUS_PRODUCT_PROFILE TABLE
SYSTEM AQ$_QUEUE_TABLES TABLE
SYSTEM AQ$_SCHEDULES TABLE
SYSTEM DEF$_ERROR TABLE
SYSTEM DEF$_DESTINATION TABLE
SYSTEM DEF$_CALLDEST TABLE
SYSTEM DEF$_DEFAULTDEST TABLE
SYSTEM DEF$_LOB TABLE
SYSTEM DEF$_TEMP$LOB TABLE
SYSTEM DEF$_PROPAGATOR TABLE
SYSTEM DEF$_ORIGIN TABLE
SYSTEM DEF$_AQCALL TABLE
SYSTEM AQ$_QUEUES TABLE
SYSTEM DEF$_AQERROR TABLE
SYSTEM HELP TABLE
SYSTEM AQ$_QUEUE_TABLES_PRIMARY INDEX
SYSTEM AQ$_SCHEDULES_CHECK INDEX
SYSTEM AQ$_SCHEDULES_PRIMARY INDEX
SYSTEM DEF$_ERROR_PRIMARY INDEX
SYSTEM DEF$_DESTINATION_PRIMARY INDEX
SYSTEM DEF$_CALLDEST_PRIMARY INDEX
SYSTEM DEF$_CALLDEST_N2 INDEX
SYSTEM DEF$_DEFAULTDEST_PRIMARY INDEX
SYSTEM SYS_IL0000000507C00003$ LOBINDEX
SYSTEM SYS_IL0000000507C00004$ LOBINDEX
SYSTEM SYS_IL0000000507C00005$ LOBINDEX
SYSTEM DEF$_LOB_PRIMARY INDEX
SYSTEM DEF$_LOB_N1 INDEX
SYSTEM SYS_IL0000000516C00001$ LOBINDEX
SYSTEM SYS_IL0000000516C00002$ LOBINDEX
SYSTEM SYS_IL0000000516C00003$ LOBINDEX
SYSTEM DEF$_PROPAGATOR_PRIMARY INDEX
SYSTEM SYS_IL0000026312C00025$ LOBINDEX
SYSTEM SYS_IL0000001112C00012$ LOBINDEX
SYSTEM AQ$_QUEUES_CHECK INDEX
SYSTEM AQ$_QUEUES_PRIMARY INDEX
SYSTEM SYS_C002687 INDEX
SYSTEM DEF$_TRANORDER INDEX
SYSTEM SYS_IL0000026320C00025$ LOBINDEX
SYSTEM SYS_C002690 INDEX
SYSTEM HELP_TOPIC_SEQ INDEX
SYSTEM SYS_LOB0000000507C00003$ LOBSEGMENT
SYSTEM SYS_LOB0000000507C00004$ LOBSEGMENT
SYSTEM SYS_LOB0000000507C00005$ LOBSEGMENT
SYSTEM SYS_LOB0000000516C00001$ LOBSEGMENT
SYSTEM SYS_LOB0000000516C00002$ LOBSEGMENT
SYSTEM SYS_LOB0000000516C00003$ LOBSEGMENT
SYSTEM SYS_LOB0000001112C00012$ LOBSEGMENT
SYSTEM SYS_LOB0000026312C00025$ LOBSEGMENT
SYSTEM SYS_LOB0000026320C00025$ LOBSEGMENT
SYS 14.3842 TEMPORARY
SYS 14.55042 TEMPORARY
SYS 14.99842 TEMPORARY
SYS 989.56322 TEMPORARY
I am not sure WHERE and HOW to move these items in 8.0.5 version. And should i do it in off hours??
Do you know any such case discussed at metalink.oracle.com ... please do let me know as a reference.
regards
Azmat
|
|
|
|
| Re: I want to move out SYSTEM objects from TEMP tablespace [message #52623 is a reply to message #52583] |
Thu, 01 August 2002 10:09 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
I have not had to this before. This is what I would try first:
1. FULL backup.
2. Create a temporary tablespace
3. Make system point to the new temp ts
4. Alter the system user to have 0M on the ts that currently has the objects.
5. Set the default ts for system to a permanent ts and grant unlimited quota on it.
6. Restart the DB in restrict mode
7. Run catalog.sql and catproc.sql as SYS
8. Run pupbld.sql as SYSTEM
9. Compile invalid objects
10. check to see if any objects still exist in the ts.
Methos 2
1. Full export
2. Drop database
3. Create database
4. Make sure users have the right ts's and quota
5. Run standard scripts
6. Import
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
