Re: [Q] way to shrink temporary tablespace ??
- From: Paul Drake <bdbafh@xxxxxxxxx>
- To: Joseph Amalraj <joseph@xxxxxxxxxxxxxx>
- Date: Thu, 19 Jan 2006 13:51:47 -0500
On 1/19/06, Joseph Amalraj <joseph@xxxxxxxxxxxxxx> 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: 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@mydb> alter user me quota 1024M on temp;
User altered.
SYSTEM@mydb> 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@mydb> select version from v$instance;
VERSION
-----------------
10.1.0.4.0
SYSTEM@mydb> select platform_name from v$database;
PLATFORM_NAME
------------------------------------------
Microsoft Windows IA (32-bit)
me@mydb> 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@xxxxxxxxx>* wrote:
>
> On 1/19/06, Joseph Amalraj <joseph@xxxxxxxxxxxxxx> 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
>
>
Other related posts:
