| Switching tablespace from permanent to temporary [message #126656] |
Wed, 06 July 2005 02:34  |
tarundua
Messages: 1080
Registered: June 2005
Location: India
|
Senior Member |
|
|
I wanted to switch a permanent tablespace to a temporary one provied the permanent tablespace doesnt contain any objects in it.
SQL> alter tablespace userdata temporary;
alter tablespace userdata temporary
*
ERROR at line 1:
ORA-03218: invalid option for CREATE/ALTER TABLESPACE
Why cant i do that ? Can anybody help me to come out of this problem ?
Thanks ,
Tarun
|
|
|
|
| Re: Switching tablespace from permanent to temporary [message #126729 is a reply to message #126656] |
Wed, 06 July 2005 07:19  |
Frank Naude
Messages: 4587
Registered: April 1998
|
Senior Member |
|
|
I get the same error (on 10.1.0.2.0), yet, according to the SQL Reference Guide it should be possible:
| Quote: |
Specify PERMANENT to indicate that the tablespace is to be converted from a temporary to a permanent tablespace. A permanent tablespace is one in which permanent database objects can be stored. This is the default when a tablespace is created.
Specify TEMPORARY to indicate that the tablespace is to be converted from a permanent to a temporary tablespace. A temporary tablespace is one in which no permanent database objects can be stored. Objects in a temporary tablespace persist only for the duration of the session.
|
My output:
SQL> ALTER TABLESPACE test_ts1 TEMPORARY;
ALTER TABLESPACE test_ts1 TEMPORARY
*
ERROR at line 1:
ORA-03218: invalid option for CREATE/ALTER TABLESPACE
Looking at the error, it might only work for DICTIONARY MANAGED tablespaces:
$ oerr ora 03218
03218, 00000, "invalid option for CREATE/ALTER TABLESPACE"
// *Cause: invalid option for create/alter tablespace of type locally-managed
// *Action: Specify one of the valid options.
If I were you, I would log a TAR and request support to log a documentation bug.
Best regards.
Frank
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
