Home » SQL & PL/SQL » SQL & PL/SQL » Storage doubt
Storage doubt [message #222217] Fri, 02 March 2007 06:08 Go to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,
In which tablespace can I find DATABASELINK, DIRECTORY, PROCEDURE and all other objects? Actually where do they reside.
These are not available in DBA_SEGMENTS.

Actually I need to find the tablespace for all the following.

CLUSTER
CONSUMER GROUP
DATABASE LINK
DIRECTORY
EVALUATION CONTEXT
FUNCTION
INDEX
INDEX PARTITION
LIBRARY
LOB
MATERIALIZED VIEW
OPERATOR
PACKAGE
PACKAGE BODY
PROCEDURE
QUEUE
RESOURCE PLAN
SEQUENCE
SYNONYM
TABLE
TABLE PARTITION
TRIGGER
TYPE
TYPE BODY
VIEW

Thanks in Advance.
Re: Storage doubt [message #222221 is a reply to message #222217] Fri, 02 March 2007 06:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, of all that list, only Tables (normal and partitioned), LObs (and really, they count as part of tables), Indexes (normal and partitioned) and Materialized Views live in tablespaces.

The name Tablespace is a real giveaway - it's a space to store tables in.

1) Why do you think these other objects live in tablespaces?
2) What are you actually trying to do. If you tell us that, we may be able to help you.
Re: Storage doubt [message #222222 is a reply to message #222221] Fri, 02 March 2007 06:21 Go to previous messageGo to next message
ganeshsv
Messages: 51
Registered: January 2006
Member
Hi,
Iam just looking to get where the STORED PROCEDURES, DATABASELINKS are actually residing in the ORACLE DATABASE.

Thanks in Advance.
Re: Storage doubt [message #222228 is a reply to message #222222] Fri, 02 March 2007 07:05 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
They are stored in internal tables. DBA_TABLES, DBA_OBJECTS, etc DBA%/ALL%/USER% objects are just views owned by sys. For example
SQL> set long 100000
SQL> select TEXT from user_views where VIEW_NAME = 'DBA_TABLES';

TEXT
--------------------------------------------------------------------------------
select u.name, o.name, decode(bitand(t.property,2151678048), 0, ts.name, null),
       decode(bitand(t.property, 1024), 0, null, co.name),
       decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
<skipped>
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
     sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
<skipped>

v$% view definitions (actually look ate gv$ defs, because v$ are just for an instance) you can find in v$fixed_view_definition. Also you may be interested in v$fixed_table.
AFAIK not all of them are actual tables, some are just memory structures.
Anyway you should explore data dictionary either in oracle docs or select * from dict;
And internal tables mostly are stored in system tablespace (physically of course in datafiles attached to this tablespace) and in 10g also in sysaux tablespace.

Gints Plivna
http://www.gplivna.eu

[Updated on: Fri, 02 March 2007 07:05]

Report message to a moderator

Previous Topic: Storage doubt
Next Topic: Understanding of the database
Goto Forum:
  


Current Time: Thu Dec 08 14:15:41 CST 2016

Total time taken to generate the page: 0.07015 seconds