way to shrink temporary tablespace ??

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

way to shrink temporary tablespace ??

way to shrink temporary tablespace ??

2006-01-19 - By Paul Drake

Reply: 1 2 3 4 5 6 7 8 9 10

On 1/19/06, Joseph Amalraj <joseph@(protected)> wrote:
>
> I got the following error:
>
> SQL> alter user scott quota 1M on temp;
> alter user scott quota 1M on temp
> *
> ERROR at line 1:
> ORA-30041 (See ORA-30041.ora-code.com): Cannot grant quota on the tablespace
>
>
> See Metalink Doc ID: 331657.1
>
> Regards
>
> Joseph Amalraj
>

Joseph,

Fascinating. What OS/version/patch is that environment?
I'm not fabricating this - its from a db on a server here at work - 10G R1
Standard Edition, 10.1.0.4 patch 9 (CPUJan2006) on w2k3 server standard
edition.

Ah - from the note that you reference:

"Infact this was a bug in earlier releases of Oracle -- Oracle9.2 &
10gR1, which allowed the above commands to work. After this is fixed in 10.2,
we correctly get the expected error message."

So your environment is 10.2.
No wonder why I haven't seen this.
I guess I would have discovered it when upgrading a 10.1 database to 10.2.
Thanks for the info - that was helpful.

Paul

SYSTEM@(protected)> alter user me quota 1024M on temp;

User altered.

SYSTEM@(protected)> select * From dba_tablespaces where tablespace_name='TEMP';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
-- ---- ---- ---- -- -- ---- -- -- ---- ------ -- ---- --- -- ---- ---
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR
EXTENT_MAN
-- ---- --- -- ---- ---- -- ---- -- -- ------ -- ------ -- ------ ---
-- ---- --
ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
-- ------ --- -- --- -- ----- -- ---- --- ---
TEMP 8192 1048576 1048576 1
0 1048576 ONLINE TEMPORARY NOLOGGING NO LOCAL
UNIFORM NO MANUAL DISABLED NOT APPLY NO

SYSTEM@(protected)> select version from v$instance;

VERSION
-- ---- ---- ----
10.1.0.4.0

SYSTEM@(protected)> select platform_name from v$database;

PLATFORM_NAME
-- ---- ---- ---- ---- ---- ---- ---- ----
Microsoft Windows IA (32-bit)

me@(protected)> select * From user_ts_quotas;

TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS
-- ---- ---- ---- -- -- ---- -- -- ---- -- -- ---- -- -- ---- --
TEMP 0 1073741824 0 131072
TS1 0 268435456 0 32768
TS2 21168128 268435456 2584 32768

*Paul Drake <bdbafh@(protected)>* wrote:
>
> On 1/19/06, Joseph Amalraj <joseph@(protected)> wrote:
> >
> >
> > Probably 2 temporary tablespaces can be created. Users writing poor
> queries could be assigned to one of them, and critical applications use the
> other temporary tablespace.
> >
> > Unfortunately Oracle does not allow quotas on temporary tablespace.
> >
> > Regards
> >
> > Joseph Amalraj
>
>

On 1/19/06, <b class="gmail_sendername">Joseph Amalraj</b> <<a href="mailto
:joseph@(protected)">joseph@(protected)</a>> wrote:<div><span class=
"gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px
solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>I got the following error: </div> <div> </div> <div>SQL> alter
user scott quota 1M on temp;<br>alter user scott quota 1M on temp<br>*<br
>ERROR at line 1:<br>ORA-30041 (See ORA-30041.ora-code.com): Cannot grant quota on the tablespace</div>
<div> </div> <div> </div> <div>See Metalink Doc ID: 331657.1</div>
<div> </div> <div>Regards</div> <div> </div> <div>Joseph Amalraj<
/div></blockquote><div><br>
Joseph,<br>
<br>
Fascinating. What OS/version/patch is that environment?<br>
I'm not fabricating this - its from a db on a server here at work - 10G
R1 Standard Edition, <a href="http://10.1.0.4">10.1.0.4</a> patch 9 (CPUJan2006
) on w2k3 server
standard edition.<br>
<br>
Ah - <font face="helvetica">from the note that you reference:<br>
<br>
"Infact this was a bug in earlier releases of 
Oracle  --  Oracle9.2 &  10gR1, which allowed the above
commands to
work. After this is fixed in 10.2, we correctly get the expected error
message."<br>
<br>
So your environment is 10.2. <br>
No wonder why I haven't seen this. <br>
I guess I would have discovered it when upgrading a 10.1 database to 10.2.<br>
Thanks for the info - that was helpful.<br>
</font><br>
Paul<br>
<br>
<br>
<span style="font-family: courier new,monospace;">SYSTEM@(protected)> alter user me
quota 1024M on temp;</span><br style="font-family: courier new,monospace;">
<br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">User altered.</span><br style
="font-family: courier new,monospace;">
<br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">SYSTEM@(protected)></span><span
style="font-family: courier new,monospace;"> select * From dba_tablespaces
where tablespace_name='TEMP';</span><br style="font-family: courier new
,monospace;">

<br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">TABLESPACE_NAME  
    BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS</span><br
style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">-- ---- ---- ---- -- -- -----
-- -- ---- ------ -- ---- --- -- ---- ---</span><br style="font-family: courier
new,monospace;">
<span style="font-family: courier new,monospace;">MAX_EXTENTS PCT_INCREASE MIN
_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT
_MAN</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">-- ---- --- -- ---- ---- ----
-- --- -- ------ -- ------ -- ------ --- -- ---- --</span><br style="font-family
: courier new,monospace;">
<span style="font-family: courier new,monospace;">ALLOCATIO PLU SEGMEN DEF_TAB_
RETENTION   BIG</span><br style="font-family: courier new,monospace;"
>
<span style="font-family: courier new,monospace;">-- ------ --- -- --- -- -----
-- ---- --- ---</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">TEMP    
     
           
8192       
1048576    
1048576           1</span><br
style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">    &nbsp
;             
    
0    1048576 ONLINE    TEMPORARY
NOLOGGING NO  LOCAL</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">UNIFORM   NO 
MANUAL DISABLED NOT APPLY   NO</span><span style="font-family:
courier new,monospace;"></span><span style="font-family: courier new,monospace;
"><br>
<br>
</span><span style="font-family: courier new,monospace;">SYSTEM@(protected)></span>
<span style="font-family: courier new,monospace;"> select version from v
$instance;<br>
<br>
VERSION<br>
-- ---- ---- ----<br>
10.1.0.4.0<br>
<br>
</span><span style="font-family: courier new,monospace;">SYSTEM@(protected)></span>
<span style="font-family: courier new,monospace;"> select platform_name from v
$database;</span><span style="font-family: courier new,monospace;">
</span><span style="font-family: courier new,monospace;"><br>
<br>
PLATFORM_NAME<br>
-- ---- ---- ---- ---- ---- ---- ---- ----<br>
Microsoft Windows IA (32-bit)<br>
<br>
</span><span style="font-family: courier new,monospace;">me@(protected)></span>
<span style="font-family: courier new,monospace;"> select * From user_ts_quotas;
<br>
<br>TABLESPACE_NAME          
BYTES  MAX_BYTES     BLOCKS MAX_BLOCKS<br>
-- ---- ---- ---- -- -- ---- -- -- ---- -- -- ---- -- -- ---- --<br>
TEMP          
             
 
0 1073741824         
0     131072<br>
TS1       
             
     
0  268435456         
0      32768<br>
TS2      
            
21168128  268435456      
2584      32768<br>
<br>
<br>
</span><span style="font-family: courier new,monospace;"><br>
</span><br>
 <br>
</div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204
, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div><span class="q"
><b><i>Paul Drake <<a href="mailto:bdbafh@(protected)" target="_blank" onclick=
"return top.js.OpenExtLink(window,event,this)">
bdbafh@(protected)</a>></i></b> wrote:</span></div><div><span class="e" id="q
_108e3f523dc5a2ba_3"> <blockquote style="border-left: 2px solid rgb(16, 16, 255
); padding-left: 5px; margin-left: 5px;">On 1/19/06, Joseph Amalraj <
<a href="mailto:joseph@(protected)" target="_blank" onclick="return top.js
.OpenExtLink(window,event,this)">joseph@(protected)</a>> wrote:<br>>
<br>> <br>>
Probably 2 temporary tablespaces can be created. Users writing poor
queries could be assigned to one of them, and critical applications use
the other temporary tablespace.  <br>>    <br>
> Unfortunately Oracle does not allow quotas on temporary
tablespace.  <br>>    <br>> Regards 
 <br>>    <br>> Joseph Amalraj <br><br><
/blockquote>
</span></div></blockquote></div><br>