TOAD 10.1 does not show tablespace info when a tablespace is locked. This is
wrong. Since the tablespace still exists Toad should show the stats on it
– unless of course Oracle does not have that info.
When tablespace is locked (see diagram below) it shows no usage
When tablespace is not locked it shows space used.
When I try that here, I still see usage info.
It’s not offline and read-only, is it? That info is not available when the
tablespace is offline.
I don’t think so. Because when I went to OBJECTS I could see all the
objects. It possibly was READ-ONLY.
Do a spool sql to get the query. It it looks like it is trying to get capacity
info, then try it in the editor.
Maybe there is some other reason that it’s not available.
-John
i ran this script but was unable to locate any data entity information about
“lock on tablespace”?
select sid,
DECODE(TYPE,
‘BL’,‘Buffer hash table’,
‘CF’,‘Control File Transaction’,
‘CI’,‘Cross Instance Call’,
‘CS’,‘Control File Schema’,
‘CU’,‘Bind Enqueue’,
‘DF’,‘Data File’,
‘DL’,‘Direct-loader index-creation’,
‘DM’,‘Mount/startup db primary/secondary instance’,
‘DR’,‘Distributed Recovery Process’,
‘DX’,‘Distributed Transaction Entry’,
‘FI’,‘SGA Open-File Information’,
‘FS’,‘File Set’,
‘IN’,‘Instance Number’,
‘IR’,‘Instance Recovery Serialization’,
‘IS’,‘Instance State’,
‘IV’,‘Library Cache InValidation’,
‘JQ’,‘Job Queue’,
‘KK’,‘Redo Log “Kick”’,
‘LS’,‘Log Start/Log Switch’,
‘MB’,‘Master Buffer hash table’,
‘MM’,‘Mount Definition’,
‘MR’,‘Media Recovery’,
‘PF’,‘Password File’,
‘PI’,‘Parallel Slaves’,
‘PR’,‘Process Startup’,
‘PS’,‘Parallel Slaves Synchronization’,
‘RE’,‘USE_ROW_ENQUEUE Enforcement’,
‘RT’,‘Redo Thread’,
‘RW’,‘Row Wait’,
‘SC’,‘System Commit Number’,
‘SH’,‘System Commit Number HWM’,
‘SM’,‘SMON’,
‘SQ’,‘Sequence Number’,
‘SR’,‘Synchronized Replication’,
‘SS’,‘Sort Segment’,
‘ST’,‘Space Transaction’,
‘SV’,‘Sequence Number Value’,
‘TA’,‘Transaction Recovery’,
‘TD’,‘DDL enqueue’,
‘TE’,‘Extend-segment enqueue’,
‘TM’,‘DML enqueue’,
‘TS’,‘Temporary Segment’,
‘TT’,‘Temporary Table’,
‘TX’,‘Transaction’,
‘UL’,‘User-defined Lock’,
‘UN’,‘User Name’,
‘US’,‘Undo Segment Serialization’,
‘WL’,‘Being-written redo log instance’,
‘WS’,‘Write-atomic-log-switch global enqueue’,
‘XA’,‘Instance Attribute’,
‘XI’,‘Instance Registration’,
decode(substr(TYPE,1,1),
‘L’,‘Library Cache (’||substr(TYPE,2,1)||’)’,
‘N’,‘Library Cache Pin (’||substr(TYPE,2,1)||’)’,
‘Q’,‘Row Cache (’||substr(TYPE,2,1)||’)’,
‘???’)) TYPE,
id1,id2,
decode(lmode,
0,‘None(0)’,
1,‘Null(1)’,
2,‘Row Share(2)’,
3,‘Row Exclu(3)’,
4,‘Share(4)’,
5,‘Share Row Ex(5)’,
6,‘Exclusive(6)’) lmode,
decode(request,
0,‘None(0)’,
1,‘Null(1)’,
2,‘Row Share(2)’,
3,‘Row Exclu(3)’,
4,‘Share(4)’,
5,‘Share Row Ex(5)’,
6,‘Exclusive(6)’) request1,
ctime, block
from
v$lock;
is this a new feature of Oracle 11?
Martin Gainty
Note de déni et de confidentialité
Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l’information seulement et n’aura pas n’importe
quel effet légalement obligatoire. Étant donné que les email peuvent
facilement être sujets à la manipulation, nous ne pouvons accepter aucune
responsabilité pour le contenu fourni.
We are on 11g. And I noticed it today because one of the tablespaces showed the
lock icon and no info for DATAFILES tab. But when I went into OBJECTS tab it
showed the objects. I asked them to unlock it and then it showed info in the
TABLESPACE tab also. Not being a DBA I have no idea what they did to the
tablespace in question. If it happens again I will try to remember to look at
the SQL executed and let you know.
The lock actually indicates that the tablespace is read-only. That’s
been around for a long time.
Thanks Erwin!
Martin–
Note de déni et de confidentialité
Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l’information seulement et n’aura pas n’importe
quel effet légalement obligatoire. Étant donné que les email peuvent
facilement être sujets à la manipulation, nous ne pouvons accepter aucune
responsabilité pour le contenu fourni.
