OraFAQ Forum: Security » user_ts_quotas shows removed tablespace info

Home » RDBMS Server » Security » user_ts_quotas shows removed tablespace info

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

user_ts_quotas shows removed tablespace info [message #161374]

Fri, 03 March 2006 10:38

rkl1
Messages: 97
Registered: June 2005

Member

Hi Guys:

Some users were given unlimited tablespace privilege on some tablespaces. Those tablespaces have been dropped however when the users query the user_ts_quotas, it would still show the removed tablespaces. In reality, those tablespace were dropped long time before. Seems like oracle does not maintain dependency between quotas on tablespaces and the tablespaces even the tablespaces does not exist. We have recreated those tablespaces and reduce the quota to 0 and then drop those tablespace. Guess what, oracle still shows the tablespaces with 0 quotas. Is there any way to refresh or workaround so users only see the tablespaces where they have the quotas rather 0 or what ever quotas on tablespaces even if they dont exist.

thanks.

Report message to a moderator

Re: user_ts_quotas shows removed tablespace info [message #161376 is a reply to message #161374]

Fri, 03 March 2006 10:55

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

It is a known good old bug and what you did is the standard fix.
What are the oracle versions? Could you also post what you did?

Edit:
Seems still a bug and a workaround is to create your own local view. Please check metalink.

[Updated on: Fri, 03 March 2006 11:09]

Report message to a moderator

Re: user_ts_quotas shows removed tablespace info [message #161389 is a reply to message #161374]

Fri, 03 March 2006 12:03

rkl1
Messages: 97
Registered: June 2005

Member

Thanks for asking. The oracle version is 9.2.0.7. The database is sitting on HP box. Here is the mad scientist experiment which I did on my laptop database, same oracle version running under window.

--grant unlimited tablespace to user scott on junk1 tablespace

sql>ALTER USER scott QUOTA unlimited ON junk1;

--now go back and grant 0 quota on junk1 tablespace to scott

sql>alter user scott quota 0 on junk1;

--Now drop the tablespace junk1

connect back scott/tiger

sql>select * from user_ts_quotas

and you will see the junk1 tablespace under tablespace_name althouh it is gone and datadictionary if working right should have updated that info and wipe the removed table from the view.

thanks.

Report message to a moderator

Re: user_ts_quotas shows removed tablespace info [message #161392 is a reply to message #161389]

Fri, 03 March 2006 12:32

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

As updated earlier it seems to be still a bug.
Please check metalink, there is a suggestion that you can create your own local view. Check TS$. You can see more anomalies.
ts$.online$ has specific entries to describe the status of tablespace.
1-available and normal
3-dropped
3-errored during creation and never been created.-->??????I have no idea.

Code: [Select all] [Show/ hide]

  1* select name,online$ from ts$
sys@9i > /

NAME                              ONLINE$
------------------------------ ----------
SYSTEM                                  1
UNDOTBS1                                1
TEMP                                    1
INDX                                    1
TOOLS                                   1
USERS                                   1
MYTABLESPACE                            3
mytbs1                                  1
TE                                      3

9 rows selected.

sys@9i > create tablespace mytbs datafile '/u01/app/oracle/oradata/mutation/mytbs.dbf' size 5m;

Tablespace created.

sys@9i >  select name,online$ from ts$;

NAME                              ONLINE$
------------------------------ ----------
SYSTEM                                  1
UNDOTBS1                                1
TEMP                                    1
INDX                                    1
TOOLS                                   1
USERS                                   1
MYTABLESPACE                            3
mytbs1                                  1
TE                                      3
MYTBS                                   1

10 rows selected.

sys@9i > drop tablespace mytbs;

Tablespace dropped.

sys@9i >  select name,online$ from ts$;

NAME                              ONLINE$
------------------------------ ----------
SYSTEM                                  1
UNDOTBS1                                1
TEMP                                    1
INDX                                    1
TOOLS                                   1
USERS                                   1
MYTABLESPACE                            3
mytbs1                                  1
TE                                      3
MYTBS                                   3

10 rows selected.

sys@9i > create tablespace another_mytbs datafile '/u01/app/oracle/oradata/mutation_invalid_directory/mytbs.dbf' size 5m;
create tablespace another_mytbs datafile '/u01/app/oracle/oradata/mutation_invalid_directory/mytbs.dbf' size 5m
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/mutation_invalid_directory/mytbs.dbf'
ORA-27040: skgfrcre: create error, unable to create file
SVR4 Error: 2: No such file or directory


sys@9i > select name,online$ from ts$;

NAME                              ONLINE$
------------------------------ ----------
SYSTEM                                  1
UNDOTBS1                                1
TEMP                                    1
INDX                                    1
TOOLS                                   1
USERS                                   1
MYTABLESPACE                            3
mytbs1                                  1
TE                                      3
MYTBS                                   3
ANOTHER_MYTBS                           3

11 rows selected.

[Updated on: Fri, 03 March 2006 12:47]

Report message to a moderator

Previous Topic:	how to find out
Next Topic:	Audited objects

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 02 01:43:40 CDT 2025