| 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.
|
|
|
|
|
|
| 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.
|
|
|
|
| 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.
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
