OraFAQ Forum: SQL & PL/SQL » creating table for atemporary tablespace

Home » SQL & PL/SQL » SQL & PL/SQL » creating table for atemporary tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

creating table for atemporary tablespace [message #146971]

Tue, 15 November 2005 00:14

varaprasad_1261
Messages: 34
Registered: September 2005

Member

Hi

What is the command to create a table for a temporary tablespace.

Thanx in advance.

With Regards
Vara Prasad

Report message to a moderator

Re: creating table for atemporary tablespace [message #146972 is a reply to message #146971]

Tue, 15 November 2005 00:26

lazycat
Messages: 57
Registered: November 2005

Member

you can create it like this
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Report message to a moderator

Re: creating table for atemporary tablespace [message #146973 is a reply to message #146971]

Tue, 15 November 2005 00:36

varaprasad_1261
Messages: 34
Registered: September 2005

Member

thanks and how can i create a table for that temporary table space.

With Regards
Vara Prasad

Report message to a moderator

Re: creating table for atemporary tablespace [message #146985 is a reply to message #146973]

Tue, 15 November 2005 01:59

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

what exactly you wants to do?

AS i know you cannot create PERMANENT objects in a TEMPORARY tablespace.

SQL> CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE 'c:\lmtemp01.dbf'
  2  size 10M reuse
  3  extent management local uniform size 5M;

Tablespace created.

SQL> create table n1 (col1 number) 
  2  tablespace lmtemp;
create table n1 (col1 number)
*
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace

Report message to a moderator

Re: creating table for atemporary tablespace [message #146987 is a reply to message #146971]

Tue, 15 November 2005 02:20

varaprasad_1261
Messages: 34
Registered: September 2005

Member

I created a temporary tablespace successfully with size 5m,then i created a temporary table using the command

create global temporary table san1(name varchar2(10));
and the table created successfully

when i am inserting a row into san1 i got the following error

ORA-25153: Temporary Tablespace is Empty

With Regards
Vara Prasad

Report message to a moderator

Re: creating table for atemporary tablespace [message #146997 is a reply to message #146987]

Tue, 15 November 2005 03:29

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

Quote:

ORA-25153 Temporary Tablespace is Empty

Cause: An attempt was made to use space in a temporary tablespace with no files.

Action: Add files to the tablespace using ADD TEMPFILE command.

Did you make it as your default temporary tablespace?

SQL> alter database default temporary tablespace lmtemp;

Database altered.

hope it might help you.

Report message to a moderator

Re: creating table for atemporary tablespace [message #147003 is a reply to message #146971]

Tue, 15 November 2005 04:02

varaprasad_1261
Messages: 34
Registered: September 2005

Member

Thanks tarun, It worked fine

With Regards
Vara Prasad

Report message to a moderator

Re: creating table for atemporary tablespace [message #147051 is a reply to message #146971]

Tue, 15 November 2005 08:59

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Just a note, the sole purpose of a temporary tablespace is not to create global temporary tables. Don't anyone think the two are identical.

Report message to a moderator

Previous Topic:	How do i partition??/
Next Topic:	Types with different datatypes /Cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Feb 18 18:45:17 CST 2025