Home » RDBMS Server » Server Administration » Temporary tablespace not seen on user_tablespaces (Oracle 11g on Linux)
icon4.gif  Temporary tablespace not seen on user_tablespaces [message #567979] Mon, 08 October 2012 22:03 Go to next message
armahalma
Messages: 4
Registered: October 2012
Junior Member
Hi All,

I have a requirement to get create a separate temporary tablespace for application user, the tablespace name is OPC_TEMP on Oracle 11g. During the application installation we are expecting this tablespace to show up under the user_tablespaces but it doesn't. When I tried to check with the DBA, they mentioned that it is possible for this tablespace to be listed under the user_tablespaces because to have this, the quota has to be assigned to the tablespace and it is not possible to assign quota to temporary tablespace on oracle 10.2 and above due to certain limitation (oracle metalink ID 331657.1).

I am new to oracle and would like to know if this is true? Appreciate any advise,

Thanks,
Oswin
Re: Temporary tablespace not seen on user_tablespaces [message #567980 is a reply to message #567979] Mon, 08 October 2012 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 22495
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

SQL> SHOW USER
USER is "SYS"
SQL> CREATE USER USER2 IDENTIFIED BY user2 default tablespace users;

User created.

SQL> grant create session to user2;

Grant succeeded.

SQL> connect user2/user2
Connected.
SQL> select tablespace_name from user_tablespaces;

no rows selected


[Updated on: Mon, 08 October 2012 22:17]

Report message to a moderator

Re: Temporary tablespace not seen on user_tablespaces [message #567988 is a reply to message #567979] Mon, 08 October 2012 23:17 Go to previous messageGo to next message
pvsarat
Messages: 8
Registered: October 2012
Location: CHENNAI
Junior Member
this is new in 10gR2 . you cannot grant quota on temporary tablespace

it used to work in 10gR1 and before
Re: Temporary tablespace not seen on user_tablespaces [message #567989 is a reply to message #567988] Mon, 08 October 2012 23:28 Go to previous messageGo to next message
BlackSwan
Messages: 22495
Registered: January 2009
Senior Member
pvsarat wrote on Mon, 08 October 2012 21:17
this is new in 10gR2 . you cannot grant quota on temporary tablespace

it used to work in 10gR1 and before


V10, neither V10.1 *NOR* V10.2, is no longer supported so any observations regarding V10 are moot.
Re: Temporary tablespace not seen on user_tablespaces [message #567990 is a reply to message #567989] Mon, 08 October 2012 23:42 Go to previous messageGo to next message
armahalma
Messages: 4
Registered: October 2012
Junior Member
Hi All,

Thanks for the replies,

This is the output we have so far, the user is "opc_op" and the temp tablespace is "opc_temp"

Run as opc_op, opc_temp not listed in TABLESPACE_NAME:
SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
OPC_1
OPC_2
OPC_3
OPC_4
OPC_5
OPC_6
OPC_7
OPC_8
OPC_9
OPC_10
OPC_INDEX1
OPC_INDEX2
OPC_INDEX3

13 rows selected.

Run as sysdba, opc_op uses opc_temp as default temporary tablespace and opc_5 as default tablespace:
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ --------------------------------------------------
SYSTEM SYSTEM TEMP
SYS SYSTEM TEMP
OPC_OP OPC_5 OPC_TEMP

Can I confirm that this "feature" is not valid anymore in 11g? And sorry if it sounds stupid, but how do we normally get the tablespace to be listed under the user_tablespaces? We are working with other DBA on this, and they insist that quota clause must be applied to this temporary tablespace before it can appear under the user_tablespaces.
Re: Temporary tablespace not seen on user_tablespaces [message #567992 is a reply to message #567990] Tue, 09 October 2012 00:46 Go to previous messageGo to next message
John Watson
Messages: 4379
Registered: January 2010
Location: Global Village
Senior Member
You can trust your DBA:
orcl> create user fred identified by fred
  2  default tablespace users temporary tablespace temp;

User created.

orcl> alter user fred quota unlimited on users;

User altered.

orcl> alter user fred quota unlimited on temp;
alter user fred quota unlimited on temp
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace


orcl> grant create session to fred;

Grant succeeded.

orcl> conn fred/fred
Connected.

Session altered.

orcl> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
USERS

orcl> select * from v$version;

BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

orcl>
Re: Temporary tablespace not seen on user_tablespaces [message #568001 is a reply to message #567990] Tue, 09 October 2012 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
they insist that quota clause must be applied to this temporary tablespace before it can appear under the user_tablespaces.

The definition of user_tablespaces is the following one:
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
          decode(bitand(ts.flags, 3), 1, to_number(NULL),
                        ts.blocksize * ts.dflincr),
          ts.dflminext,
          decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
          decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
          ts.blocksize * ts.dflminlen,
          decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
                 4, 'READ ONLY', 'UNDEFINED'),
          decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
                 'PERMANENT')), 1, 'TEMPORARY'),
          decode(bitand(ts.dflogging, 1), 0, 'NOLOGGING', 1, 'LOGGING'),
          decode(bitand(ts.dflogging, 2), 0, 'NO', 2, 'YES'),
          decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
          decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
                 'UNDEFINED'),
          decode(bitand(ts.flags,32), 32,'AUTO', 'MANUAL'),
          decode(bitand(ts.flags,64), 64,'ENABLED', 'DISABLED'),
          decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
                 'GUARANTEE', 'NOGUARANTEE')), 'NOT APPLY'),
          decode(bitand(ts.flags,256), 256, 'YES', 'NO')
from sys.ts$ ts
where ts.online$ != 3
and bitand(flags,2048) != 2048
      and (   exists (select null from sys.tsq$ tsq
                 where tsq.ts# = ts.ts#
                   and tsq.user# = userenv('SCHEMAID') and
                   (tsq.blocks > 0 or tsq.maxblocks != 0))
           or exists
              (select null
              from sys.v$enabledprivs
              where priv_number = -15 /* UNLIMITED TABLESPACE */))

The part in red shows that it must exist a row for the user and tablespace in sys.tsq$ which is the table containing the quotas.
So your DBA is right, you can see a tablespace in user_tablespaces only if you have some quota on it (Note I did this on a 10.2 database).

Regards
Michel
Re: Temporary tablespace not seen on user_tablespaces [message #568014 is a reply to message #568001] Tue, 09 October 2012 03:13 Go to previous messageGo to next message
armahalma
Messages: 4
Registered: October 2012
Junior Member
Turns out the temporary tablespace is not listed because they have not assigned "resource" role to the user.. Thanks for all the suggestions and help!
Re: Temporary tablespace not seen on user_tablespaces [message #568015 is a reply to message #568014] Tue, 09 October 2012 03:19 Go to previous messageGo to next message
John Watson
Messages: 4379
Registered: January 2010
Location: Global Village
Senior Member
Oswin, no real DBA will ever grant RESOURCE to anyone: it is extremely dangerous, because it includes an implicit grant of UNLIMITED TABLESPACE.
Re: Temporary tablespace not seen on user_tablespaces [message #568017 is a reply to message #568015] Tue, 09 October 2012 03:24 Go to previous messageGo to next message
armahalma
Messages: 4
Registered: October 2012
Junior Member
That's right, it's just that we have stated that we would only need them temporarily during installation and should be revoked afterwards.
Re: Temporary tablespace not seen on user_tablespaces [message #568020 is a reply to message #568015] Tue, 09 October 2012 03:26 Go to previous message
Michel Cadot
Messages: 58545
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... I'd even add do NOT grant one of the predefined roles (unless it is mandatory for an Oracle feature).

Regards
Michel
Previous Topic: ARC file size too small
Next Topic: core dump under $ORACLE_HOME/dbs
Goto Forum:
  


Current Time: Thu Jul 24 23:08:06 CDT 2014

Total time taken to generate the page: 0.06260 seconds