| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Temporary tablespace for SYS
Hi Steve,
I've just done a test on both 7.3.4 and 8.1.5 and and have been able to confirm that you CAN drop a tablespace that has previously been the temporary tablespace for SYS. So I don't know what version of Oracle this theory may have come from.
Rgs,
Leng.
H815.SYS> alter user sys temporary tablespace OEM_REPOSITORY;
User altered.
select username, default_tablespace, temporary_tablespace from dba_users
where username = 'SYS';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
User altered.
H815.ORACLE> alter tablespace OEM_REPOSITORY offline;
Tablespace altered.
H815.ORACLE> drop tablespace OEM_REPOSITORY;
Tablespace dropped.
H815.ORACLE> !rm /opt/app/oracle/hubu/oradata/H815/oemrep01.dbf
H815.ORACLE> select username, default_tablespace, temporary_tablespace from dba_users
where username = 'SYS';
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
From: "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
Date: Tue, 12 Sep 2000 21:55:39 -0700
Subject: RE: Temporary tablespace for SYS
I believe the claim was that this was true sometime in the past. It is certainly not true now. About a week ago some developers working on a nascent database called to say they had dropped too many tables. I performed a point-in-time recovery, but forgot that locally managed temporary tabespaces are not included in hot backups. The database opened without incident, but very shortly afterwards my pager was receiving messages, "Can't lock file ...". The old temporary tablespace file had been deleted. Dropping and recreating the temporary tablespace fixed the problem. This was sys's temporary tablespace.
Luckily, the first attempted use of the tablespace was by one of my database monitoring scripts. The developers never knew the problem existed. Although, I was pretty sure droppping and recreating the tablespace would fix the problem, I was very glad I had performed a cold backup whih included the "temp_file" before I started the point-in-time recovery.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_slac.stanford.edu
-----Original Message-----
Sent: Tuesday, September 12, 2000 7:27 PM
To: 'Steve Adams'
Cc: 'Oracle List I'; 'Oracle List II'
i just did it... but it did not change the TEMPORARY_TABLESPACE info in dba_users for SYS !!!
> ----------
> From: Steve Adams[SMTP:steve.adams_at_ixora.com.au]
> Sent: Tuesday, September 12, 2000 7:29 PM
> To: main oracle list; oracle dba list
> Subject: Temporary tablespace for SYS
>
> Hi All,
>
> I've got an email from someone who says that it used not to be possible to
> drop
> a tablespace that had ever been the temporary tablespace for SYS. This is
> news
> to me. Has anyone ever heard of this before?
>
> @ Regards,
> @ Steve Adams
> @ http://www.ixora.com.au/
> @ http://www.christianity.net.au/
> @
> @ Going to OpenWorld?
> @ Catch the Ixora performance tuning seminar too!
> @ See http://www.ixora.com.au/seminars/ for details.
Leng.
Tel: 9843-8440 Fax: 9843-8590 ----------------------------------------------------------------------- **********************************************************************This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.
This footnote also confirms that this email message has been swept by Received on Thu Sep 14 2000 - 00:07:50 CDT
 |
 |