Oracle Database Objects

Object types

There is quite a list of available object types. The list of object types can be queried by first expanding the SQL statement text of dba_objects …

declare
   c clob;
begin
   dbms_utility.expand_sql_text('select*from"DBA_OBJECTS"', c);
   dbms_output.put_line(c);
end;

… and then finding the relevant decode statement:

decode("A14"."TYPE#",
  0, 'NEXT OBJECT',
  1, 'INDEX'      ,
  2, 'TABLE'
  …
)

This gives me the list of possible object types (19c).

152	ANALYTIC VIEW
61	APPLY	Aka APPLY PROCESS (`catmetviews_mig.sql`)
87	ASSEMBLY
151	ATTRIBUTE DIMENSION
60	CAPTURE	Aka CAPTURE PROCESS (`catmetviews_mig.sql`)
73	CDC CHANGE SET	As per `catmetviews_mig.sql`
79	CHAIN	Aka SCHEDULER JOB CHAIN (`catmetviews_mig.sql`)
3	CLUSTER	A is an object that stores data from one or more tables
48	CONSUMER GROUP	Aka SCHEDULER CLASS (`catmetviews_mig.sql`)
111	CONTAINER
44	CONTEXT
38	CONTEXT POLICY OBJECT	As per `catmetviews_mig.sql`
90	CREDENTIAL
95	CUBE BUILD PROCESS
93	CUBE
92	CUBE DIMENSION
102	CURSOR STATS	As per `catmetviews_mig.sql`
85	DATA SECURITY DOCUMENT	As per `catmetviews_mig.sql`
129	DATABASE VAULT RULE	As per `catmetviews_mig.sql`
130	DATABASE VAULT RULE SET	As per `catmetviews_mig.sql`
101	DESTINATION	Aka JOB SCHEDULER NAMED DESTINATION (`catmetviews_mig.sql`)
43	DIMENSION
23	DIRECTORY	A Directory is an abstraction for a directory/folder on the file system where the Oracle Instance is running. Such directories can be used for example in conjunction with the BFILE data type.
182	DOMAIN	New in 23c
57	EDITION	Aka SECURITY PROFILE (`catmetviews_mig.sql`)
62	EVALUATION CONTEXT	Aka RULE EVALUATION CONTEXT (`catmetviews_mig.sql`)
154	EXPRESSION HEADER	As per `catmetviews_mig.sql`
155	EXPRESSION OBJECT	As per `catmetviews_mig.sql`
81	FILE GROUP	Aka STREAMS FILE GROUP JOB (`catmetviews_mig.sql`)
100	FILE WATCHER	Aka SCHEDULER FILE WATCHER (`catmetviews_mig.sql`)
8	FUNCTION	An editionable type.
108	FUSION XS NAMESPACE TEMPLATES	As per `catmetviews_mig.sql`
97	FUSION XS PARAMETERS	As per `catmetviews_mig.sql`
110	FUSION XS SECURITY ACL	As per `catmetviews_mig.sql`
109	FUSION XS SECURITY CLASS	As per `catmetviews_mig.sql`
150	HIERARCHY
76	HINTSET	As per `catmetviews_mig.sql`
1	INDEX
35	INDEX SUBPARTITION
20	INDEX PARTITION
32	INDEXTYPE
153	INMEMORY TABLE COMPRESSION	As per `catmetviews_mig.sql`
25	IOT	As per `catmetviews_mig.sql`
65	KGL TEST	As per `catmetviews_mig.sql`
132	LABEL SECURITY LABEL-PID CACHE	As per `catmetviews_mig.sql`
131	LABEL SECURITY POCLICY-TABLE CACHE	As per `catmetviews_mig.sql`
89	LIGHTWEIGHT JOBS	As per `catmetviews_mig.sql`
84	LIGHT WEIGHT SESSION	As per `catmetviews_mig.sql`
17	LINK	As per `catmetviews_mig.sql`
63	LOG_BASED REPL SOURCE	As per `catmetviews_mig.sql`
56	JAVA DATA	Aka JAVA SHARED DATA (`catmetviews_mig.sql`)
30	JAVA RESOURCE
28	JAVA SOURCE
29	JAVA CLASS
31	JAVA JAR	As per `catmetviews_mig.sql`
66	JOB	Aka SCHEDULER JOB (`catmetviews_mig.sql`). Compare with programs.
68	JOB CLASS	Aka SCHEDULER CLASS (`catmetviews_mig.sql`)
22	LIBRARY	An editionable type.
41	LOB SUBPARTITION
40	LOB PARTITION
21	LOB
52	LOCATION	Aka LOCATION INFORMATION (`catmetviews_mig.sql`)
148	LOCKDOWN PROFILE	= Application lockdown profile?
42	MATERIALIZED VIEW, REWRITE EQUIVALENCE or MATERIALIZED ZONEMAP	Object type further determined based on value of `xpflags` in joined table `sys.sum$`. A Materialized View stores the result of an SQL query. Associated with a Materialized View is the Materialized View Log. (Aka SUMMARY, see `catmetviews_mig.sql`)
94	MEASURE FOLDER
82	MINING MODEL
144	MINING MODEL PARTITION
163	MLE LANGUAGE	New in 23c (or 21c?)
180	MLE MODULE	New in 23c (or 21c?)
181	MLE ENVIRONMENT	Metadata that complements MLE modules. New in 23c (or 21c?)
70	MULTI-VERSIONED OBJECT	As per `catmetviews_mig.sql`
0	NEXT OBJECT
33	OPERATOR
15	OBJECT	As per `catmetviews_mig.sql`
99	OBJECT ID	As per `catmetviews_mig.sql`
45	OUTLINE	As per `catmetviews_mig.sql`
122	OLS COMPARTMENT NUMBER TO SHORT NAME MAPPINGS	As per `catmetviews_mig.sql`
123	OLS COMPARTMENT SHORT NAME TO NUMBER MAPPINGS	As per `catmetviews_mig.sql`
119	OLS ILABELS	As per `catmetviews_mig.sql`
120	OLS GROUP NUMBER TO SHORT NAME MAPPINGS	As per `catmetviews_mig.sql`
124	OLS LEVEL NUMBER TO SHORT NAME MAPPINGS	As per `catmetviews_mig.sql`
125	OLS LEVEL SHORT NAME TO NUMBER MAPPINGS	As per `catmetviews_mig.sql`
133	OLS READ CONTROL RELATED CACHE	As per `catmetviews_mig.sql`
121	OLS SHORT NAME TO NUMBER MAPPINGS	As per `catmetviews_mig.sql`
128	OLS USERS	As per `catmetviews_mig.sql`
134	OLS$AUDIT OPTIONS	As per `catmetviews_mig.sql`
127	OPTIMIZER DIRECTIVE OWNER	As per `catmetviews_mig.sql`
126	OPTIMIZER FINDING	As per `catmetviews_mig.sql`
50	PENDING SchEDULER CLASS	As per `catmetviews_mig.sql`
49	PENDING SchEDULER PLAN	As per `catmetviews_mig.sql`
16	USER	As per `catmetviews_mig.sql`. While users are objects, roles are not.
135	USER PRIVILEGES	As per `catmetviews_mig.sql`
11	PACKAGE BODY	An editionable type.
9	PACKAGE	An editionable type.
18	PIPE	As per `catmetviews_mig.sql`
111	PLUGGABLE DATABASE	As per `catmetviews_mig.sql`
7	PROCEDURE	An editionable type.
67	PROGRAM	Aka SCHEDULER PROGRAM (`catmetviews_mig.sql`). Compare with jobs.
179	PROPERTY GRAPH	New in 23c (or 21c?)
39	PUB_SUB INTERNAL INFORMATION	As per `catmetviews_mig.sql`
24	QUEUE	A Queue enqueues and dequeues messages and is managed by Oracle Streams Advanced Queueing (AQ). These messages are stored in Queue Tables.
137	QUEUE DURABLE SUBSCRIBERS	As per `catmetviews_mig.sql`
53	REMOTE OBJECTS INFO	As per `catmetviews_mig.sql`
53	REMOTE OBJECT REFERENCE	As per `catmetviews_mig.sql`
54	REPAPI SNAPSHOT METADATA	As per `catmetviews_mig.sql`
37	REPLICATION INTERNAL PACKAGE	As per `catmetviews_mig.sql`
26	REPLICATION OBJECXT GROUP	As per `catmetviews_mig.sql`
27	REPLICATION PROPAGATOR	As per `catmetviews_mig.sql`
78	RESOURCE MANAGER CDB PLAN	As per `catmetviews_mig.sql`
47	RESOURCE PLAN	Aka SCHEDULER PLAN (`catmetviews_mig.sql`), see also Resource Manager.
46	RULE SET	Aka RULESET OBJECT (`catmetviews_mig.sql`)
59	RULE
74	SCHEDULE	Aka SCHEDULER SCHEDULE (`catmetviews_mig.sql`)
77	SCHEDULER ATTRIBUTES	As per `catmetviews_mig.sql`
83	SCHEDULER EVENT QUEUE INFO	As per `catmetviews_mig.sql`
72	SCHEDULER GROUP	Aka SCHEDULER WINDOW GROUP (`catmetviews_mig.sql`)
118	SCHEDULER IN-MEMORY JOBS	As per `catmetviews_mig.sql`
71	SCHEDULER JOB SLAVE	As per `catmetviews_mig.sql`
136	SCHEDULER RESOURCE CONSTRAINT	As per `catmetviews_mig.sql`
86	SECURITY CLASS	As per `catmetviews_mig.sql`
6	SEQUENCE
36	SERVER-SIDE REPAPI	As per `catmetviews_mig.sql`
117	SPATIAL CRS DIMENSION METADATA	As per `catmetviews_mig.sql`
112	SPATIAL GEOM METADATA	As per `catmetviews_mig.sql`
116	SPATIAL FEATURE USAGE	As per `catmetviews_mig.sql`
107	SPATIAL INDEX METADATA	As per `catmetviews_mig.sql`
113	SPATIAL SRID METADATA	As per `catmetviews_mig.sql`
114	SQL TRANSLATION PROFILE	AN SQL Translator converts a non-Oracle SQL dialect to SQL Statements that are understood by Oracle. An editionable type.
75	SQL TUNING BASE	As per `catmetviews_mig.sql`
80	STRACH PAD	As per `catmetviews_mig.sql` (should this be SCRATCH PAD?)
64	STREAM DDL	As per `catmetviews_mig.sql`
96	STREAM PROPAGATION	As per `catmetviews_mig.sql`
88	STUB	As per `catmetviews_mig.sql`
51	SUBSCRIPTION	Aka SUBSCRIPTION INFORMATION (`catmetviews_mig.sql`)
5	SYNONYM	An editionable type.
2	TABLE	A table is the fundamental object to store data in a database.
19	TABLE PARTITION
34	TABLE SUBPARTITION
106	TABLE-INDEX PARTITION CACHE	As per `catmetviews_mig.sql`
103	TEXT MVDATA CACHE	As per `catmetviews_mig.sql`
105	TEXT MVDATA -F	As per `catmetviews_mig.sql`
104	TEXT MVDATA -M	As per `catmetviews_mig.sql`
58	TRANSFORMATION	As per `catmetviews_mig.sql`
12	TRIGGER	An editionable type.
14	TYPE BODY	An editionable type.
13	TYPE	An editionable type.
115	UNIFIED AUDIT POLICY	Aka AUDIT POLICy (`catmetviews_mig.sql`)
4	VIEW	An editionable type.
69	WINDOW	Aka SCHEDULER WINDOW (`catmetviews_mig.sql`)
98	XDB REPOSITORY	As per `catmetviews_mig.sql`
55	XML SCHEMA	Aka IFS DATA (`catmetviews_mig.sql`)

I thought a database link is an object type as well, but apparently is not so.

The set of types that are editionable depends on the value of the the init parameter compatible and can be queried from v$editionable_types.

TODO: Are these objects defined in kgl.h?

Data dictionary

Oracle stores information about database objects in the data dictionary.

A list of all objects is found in dba_objects.

Invalid objects are listed in dba_invalid_objects

OBJ$

Internally, «under the hood», Oracle stores basic object information in sys.obj$.

Ownership

Objects (with the exemption of certain types?) have an owner, which is a user. The objects are found in the user's schema.

Privileges

In order for users to be able to access an Object, they must be granted the respective privileges.

The matrix of grants and objects can be queried through the data dictionary views dba_tab_privs, all_tab_privs and user_tab_privs.

Object names

Every object has a name.

All names must be unique within an object namespace.

The maximum length of the name is 128 bytes if compatible is set to at least 12.2.0 and 30 bytes otherwise.

If names are unquoted, they are silently converted to uppercase. Thus, the following two statements are considered equal:

SELECT col_a, col_b from TAB;
SELECT COL_A, COL_B from TAB;

Names can be quoted with double-apostrophes. Quoted names are case sensitive and allow for funny characters:

create table "The bad & the ugly!" ("x/y" number, "20% rule" varchar2(10));

Object names should not start with either SYS_ or ORA_ because SYS_* is used objects implicitly created by Oracle and ORA_ is used for Oracle supplied objects.

An object (except a private temporary table) whose name starts with the prefix which is specified for private temporary tables (i. e. the value of the init parameter private_temp_table_prefix) results in the error message ORA-32463: cannot create an object with a name matching private temporary table prefix.

SYS.EXPPKGOBJ$

The sys.exppkgobj$ table registers PL/SQL packages which allows to export (and/or import?) specific types:

select
   type#,
   schema,
   package,
   decode(class,
     1, 'system',
     2, 'schema',
     3, 'object instance'
   )                       class,
   prepost,
   level#    -- Ordering in dump file of objects exported at same time
from
   sys.exppkgobj$
order by
   level#;

TODO: Compare with sys.exppkgact$ and sys.impcalloutreg$.