| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: TEMPORARY tablespace -- what it really means
Hi Mohan,
You cannot create any object in a tablespace which is of type TEMPORARY in Oracle7 version 7.3.x.
Oracle7 will return an error ORA-02195 if you try to create a table or anyother object in the tablespace whose contents are defined TEMPORARY.
Oracle7 handles this type of tablespaces for optimizing the sorts user during index builds, analyzing tables and order by statements.
This a correction which I just wanted to post and keep you posted.
HTH,
Manoj Jain
Certified Oracle DBA
==================Start of Example=======
SQL> conn system/manager_at_orcl
Connected.
SQL> create tablespace temptest temporary datafile 'E: \DB\DATABASE\temptsd1.dat' size 1M;
Tablespace created.
SQL> select * from dba_tablespaces;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTSMAX_EXTENTS PCT_INCREASE
------------------------------ -------------- ----------- ---------------------- ------------
SYSTEM 10240 10240 1 121 50
USER_DATA 10240 10240 1 121 50
ROLLBACK_DATA 10240 10240 1 121 50
TEMPORARY_DATA 10240 10240 1 121 50
BKS_TEST 10240 10240 1 121 50
TEMPTEST 10240 10240 1 121 50
SQL> create table testtempts_table ( name varchar(25)) tablespace temptest;
create table testtempts_table ( name varchar(25)) tablespace temptest
*
ERROR at line 1:
ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace
SQL>
============End of Example=================
In article <6kv114$ql3$1_at_nnrp1.dejanews.com>,
cmohan_at_iname.com wrote:
>
> If you had done any kind of programming where you had to write a sort
routine,
> you will relate to this much easier. Any SQL that requries a SORT operation
> uses the TEMPORARY tablespace to store intermediate results of the sort.
Some
> SQL / constructs that may use the TEMPORARY tablespace are ORDER BY, CREATE
> INDEX, UNION etc.
> In fact, there is nothing that will stop you from creating a table in
> TEMPORARY tablespace, as well as a non TEMPORARY tablespace may be assigned
as
> a staging area for SORTs, though any DBA would shoot that idea down. The
> classic abuse of SYSTEM tablespace is to let it be the TEMPORARY tablespace
> for a user.
>
> Good luck,
> CM
>
> In article <6kt96a$ji7$2_at_gte1.gte.net>,
> johnvue_at_gte.net wrote:
> >
> > I'm trying to understand more thoroughly the effect of designating a
> > tablespace as TEMPORARY. Besides the surface level knowledge that you
> > can't put permanent objects in TEMPORARY tablespaces what else does
> > Oracle internally do for you?
> >
> > I came across this statement:
> >
> > "Oracle 7.3 introduced a new temporary tablespace type especially
> > designed to tune Oracle sorting. It achieves this by eliminating
> > serialization of space management."
> >
> > What does paragraph mean?
> >
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Jun 01 1998 - 18:29:13 CDT
 |
 |