7 Managing Oracle Objects

This chapter explains how Oracle objects work in combination with the rest of the database, and how to perform DML and DDL operations on them. It contains the following major sections:

Privileges on Object Types and Their Methods

Privileges for object types exist at the system level and the schema object level.

This section contains the following topics:

System Privileges for Object Types

Oracle database defines the following system privileges for object types:

  • CREATE TYPE enables you to create object types in your own schema

  • CREATE ANY TYPE enables you to create object types in any schema

  • ALTER ANY TYPE enables you to alter object types in any schema

  • DROP ANY TYPE enables you to drop named types in any schema

  • EXECUTE ANY TYPE enables you to use and reference named types in any schema

  • UNDER ANY TYPE enables you to create subtypes under any non-final object types

  • UNDER ANY VIEW enables you to create subviews under any object view

The following roles are helpful:

  • The RESOURCE role includes the CREATE TYPE system privilege.

  • The DBA role includes all of these privileges.

Schema Object Privileges

Two schema object privileges apply to object types:

  • EXECUTE enables you to use the type to:

    • Define a table.

    • Define a column in a relational table.

    • Declare a variable or parameter of the named type.

    EXECUTE lets you invoke the methods of a type, including the constructor.

    Method execution and the associated permissions are the same as for stored PL/SQL procedures.

  • UNDER enables you to create a subtype or subview under the type or view on which the privilege is granted.

    Only a grantor with the UNDER privilege WITH GRANT OPTION on the direct supertype or superview can grant the UNDER privilege on a subtype or subview.

The phrase WITH HIERARCHY OPTION grants a specified object privilege on all subtypes of the object. This option is meaningful only with the SELECT object privilege granted on an object view in an object view hierarchy. In this case, the privilege applies to all subviews of the view on which the privilege is granted.

Using Types in New Types or Tables

In addition to the permissions detailed in the previous sections, you need specific privileges to:

  • Create types or tables that use types created by other users.

  • Grant use of your new types or tables to other users.

You must have either the EXECUTE ANY TYPE system privilege or the EXECUTE object privilege for any type used to define a new type or table. You must have been granted these privileges explicitly, and not through a role.

To grant access to your new type or table to other users, you must have either the required EXECUTE object privileges with the GRANT option or the EXECUTE ANY TYPE system privilege with the option WITH ADMIN OPTION. You must have been granted these privileges explicitly, not through a role.

Example: Privileges on Object Types

Example 7-1 creates three users or schemas, USER1, USER2, and USER3, and grants them the CREATE SESSION and RESOURCE roles. Some of the subsequent examples in this chapter use these schemas.

This example requires you to create and use several passwords. If you plan to run the example, make these changes to your SQL code first.

Note:

For simplicity, this example does not perform the password management techniques that a deployed system normally uses. In a production environment, follow the Oracle Database password management guidelines, and disable any sample accounts. See Oracle Database Security Guide for password management guidelines and other security recommendations.

Example 7-1 Creating User Schemas

-- Requires passwords
CONNECT SYSTEM
-- Enter password
CREATE USER user1 PROFILE default 
   IDENTIFIED BY password DEFAULT TABLESPACE example ACCOUNT UNLOCK;
GRANT CREATE SESSION TO user1;
GRANT RESOURCE TO user1;
GRANT CREATE SYNONYM TO user1;
GRANT CREATE PUBLIC SYNONYM TO user1;
GRANT DROP PUBLIC SYNONYM TO user1;
CREATE USER user2 PROFILE default 
   IDENTIFIED BY password DEFAULT TABLESPACE example ACCOUNT UNLOCK;
GRANT CREATE SESSION TO user2;
GRANT RESOURCE TO user2;
CREATE USER user3  PROFILE default 
   IDENTIFIED BY password DEFAULT TABLESPACE example ACCOUNT UNLOCK;
GRANT CREATE SESSION TO user3;
GRANT RESOURCE TO user3;

Example 7-2 requires the input of a password, USER1 performs the CREATE and GRANT Data Definition Language (DDL) statements in the USER1 schema:

Example 7-2 Granting Privileges on Object Types

-- Requires Ex. 7-1 and the input of a password
CONNECT user1
-- Enter password
CREATE TYPE type1 AS OBJECT ( attr1 NUMBER );
/
CREATE TYPE type2 AS OBJECT ( attr2 NUMBER );
/
GRANT EXECUTE ON type1 TO user2;
GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;

In Example 7-3, USER2 performs the CREATE DDL statement in the USER2 schema:

Example 7-3 Performing DDL Statements in USER2 Schema

-- Requires Ex. 7-1, 7-2 and password input
CONNECT user2
-- Enter password
CREATE TABLE tab1 OF user1.type1;
CREATE TYPE type3 AS OBJECT ( attr3 user1.type2 );
/
CREATE TABLE tab2 (col1 user1.type2 );

In Example 7-4, the first two statements succeed because USER2 was granted the EXECUTE privilege with the GRANT option on USER1's TYPE2 in the last line of Example 7-2 and Example 7-3 created type3 as an object using attr3 user1.type2.

However, the last grant Example 7-4 fails because USER2 has not been granted the EXECUTE privilege with the GRANT option on USER1.TYPE1.

Example 7-4 Performing Grants to USER3

-- Requires Ex. 7-1, 7-2, and 7-3
GRANT EXECUTE ON type3 TO user3;
GRANT SELECT ON tab2 TO user3;

-- Privileges on Object Types
GRANT SELECT ON tab1 TO user3 -- incorrect statement;

In Example 7-5, USER3 has the necessary privileges to perform the following actions:

Example 7-5 Creating Tables and Types

-- Requires Ex. 7-1, 7-2, 7-3, and 7-4
CONNECT user3
-- Enter password
CREATE TYPE type4 AS OBJECT (attr4 user2.type3);
/
CREATE TABLE tab3 OF type4;

Access Privileges on Objects, Types, and Tables

Object types only make use of the EXECUTE privilege, but object tables use all the same privileges as relational tables:

  • SELECT lets you access an object and its attributes from the table.

  • UPDATE lets you modify attributes of objects in the table.

  • INSERT lets you add new objects to the table.

  • DELETE lets you delete objects from the table.

Similar table and column privileges regulate the use of table columns of object types.

Selecting columns of an object table does not require privileges on the type of the object table. Selecting the entire row object, however, does.

Consider the following schema and queries in Example 7-6:

Example 7-6 SELECT Privileges on Type Access

-- Requires Ex. 7-1, 7-2, 7-3, 7-4, and 7-5
CREATE TYPE emp_type AS OBJECT (
  eno     NUMBER,
  ename   VARCHAR2(36));
/
CREATE TABLE emp OF emp_type; // an object table
GRANT SELECT on emp TO user1;
SELECT VALUE(e) FROM emp e;
SELECT eno, ename FROM emp;

For both queries, Oracle database checks the user's SELECT privilege for the object table emp. For the first query, the user needs to obtain the emp_type type information to interpret the data. When the query accesses the emp_type type, the database checks the user's EXECUTE privilege.

The second query, however, does not involve named types, so the database does not check type privileges.

Additionally, USER3 can perform queries such as these:

SELECT t.col1.attr2 from user2.tab2 t;
SELECT t.attr4.attr3.attr2 FROM tab3 t;

Note that in both queries, USER3 does not have explicit privileges on the underlying type. However, the statement succeeds because the type and table owners have the necessary privileges with the GRANT option.

Oracle database checks privileges on the following requests and returns an error if the requestor does not have the privilege for the action:

  • Pinning an object in the object cache using its REF value causes the database to check the SELECT privilege on the object table containing the object and the EXECUTE privilege on the object type.

    See Also:

    Oracle Call Interface Programmer's Guide for tips and techniques for using OCI program effectively with objects
  • Modifying an existing object or flushing an object from the object cache causes the database to check the UPDATE privilege on the destination object table. Flushing a new object causes the database to check the INSERT privilege on the destination object table.

  • Deleting an object causes the database to check the DELETE privilege on the destination table.

  • Invoking a method causes the database to check the EXECUTE privilege on the corresponding object type.

Oracle database does not provide column level privileges for object tables.

Type Dependencies

This section discusses type dependencies in two broad categories:

  • Situations where types depend upon each other for their definitions, where one type might be part of the definition of another type.

  • Situations where creating or dropping types is complicated by dependencies that the type has such, as tables or types.

This section covers the following topics:

Creating Incomplete Types

Types that depend on each other for their definitions, either directly or through intermediate types, are called mutually dependent. For example, you might want to define object types employee and department in such a way that one attribute of employee is the department the employee belongs to and one attribute of department is the employee who manages the department.

If you visualize a diagram with arrows showing the relationships among a set of mutually dependent types, the connections form a loop. To define such a circular dependency, you must use REFs for at least one segment of the circle.

For example, you can define the types show in Example 7-7.

Example 7-7 Creating Dependent Object Types

-- Requires Ex. 7-1 and password
CONNECT user1
-- Enter password
ALTER SESSION SET PLSQL_WARNINGS = 'enable:all';

CREATE TYPE department; // a placeholder
/

CREATE TYPE employee AS OBJECT (
  name    VARCHAR2(30),
  dept    REF department,
  supv    REF employee );
/

CREATE TYPE emp_list AS TABLE OF employee;
/

CREATE TYPE department AS OBJECT (
  name    VARCHAR2(30),
  mgr     REF employee,
  staff   emp_list );
/

This is a legal set of mutually dependent types and a legal sequence of SQL DDL statements. Oracle database compiles it without errors.

Notice that the code in Example 7-7 creates the type department twice. The first statement is an optional, incomplete declaration of department that serves as a placeholder for the REF attribute of employee to point to. The declaration is incomplete in that it omits the AS OBJECT phrase and lists no attributes or methods. These are specified later in the full declaration that completes the type. In the meantime, department is created as an incomplete object type. This enables the compilation of employee to proceed without errors.

If you do not create incomplete types as placeholders, types that refer to the missing types still compile, but the compilation proceeds with errors. For example, if department did not exist at all, Oracle database would create it as an incomplete type and compile employee with errors. Then employee would be recompiled the next time that some operation accesses it. This time, if all the types it depends on have been created and its dependencies are satisfied, it compiles without errors.

Incomplete types also enable you to create types that contain REF attributes to a subtype that has not yet been created. To create such a supertype, first create an incomplete type of the subtype to be referenced. Create the complete subtype after you create the supertype.

Completing Incomplete Types

When you have created all the types referenced by an incomplete type, complete the declaration of the incomplete type, because there is no longer any need for it to remain incomplete. Completing the type recompiles it and enables the system to release various locks.

To complete an incomplete type, you execute a CREATE TYPE statement that specifies the attributes and methods of the type, as shown at the end of Example 7-7.

Also, you must complete any incomplete types that the database creates for you. If, as discussed in the preceding section, you did not explicitly create department as an incomplete type, then the database did. In this case, you still need to complete it.

You must complete an incomplete object type as an object type: you cannot complete an object type as a collection type (a nested table type or an array type). The only alternative is to drop the type.

Manually Recompiling a Type

If a type was created with compilation errors, and you attempt an operation on it, such as creating tables or inserting rows, you may receive an error. You need to recompile the type before attempting the operation. To manually recompile a type, execute an ALTER TYPE typename COMPILE statement. After you have successfully compiled the type, attempt the operation again.

Using CREATE OR REPLACE TYPE with Type and Table Dependencies

The CREATE OR REPLACE TYPE statement throws an error if the type being replaced has table or type dependencies. This applies to objects, varrays, and nested table types. This also applies to type dependencies involving either inheritance or type composition (embedding one type into another). The latter might be a situation where one type is attribute of another.

Using the FORCE option with a CREATE OR REPLACE TYPE statement enables you to replace a type if it has type dependencies, but not table dependencies. Table dependencies still cause errors.

Example 7-8 shows a CREATE OR REPLACE statement (second statement) that fails due to a type dependency.

Example 7-8 CREATE OR REPLACE Type and Table Failure

SQL> CREATE type t1 AS OBJECT (a number) not final;
  2  /
Type created.
 
SQL> CREATE TYPE t2 UNDER t1 (b varchar(10));
  2  /
Type created.
 
SQL> CREATE OR REPLACE TYPE t1 AS OBJECT (c varchar(20));
  2  /
CREATE OR REPLACE TYPE t1 AS OBJECT (c varchar(20));
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

Example 7-9 shows code in which a CREATE OR REPLACE FORCE statement succeeds in replacing a type that has a type dependency and then creates a table using the parent type. However, the final CREATE OR REPLACE FORCE statement fails because the type now has a table dependency and even with the FORCE option, a type with a table dependency cannot be replaced.

Example 7-9 CREATE OR REPLACE with FORCE

SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (c varchar(20));
  2  /
Type created.
 
SQL> CREATE TABLE tb1 (c1 t1);
Table created.
 
SQL> CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
  2  /
CREATE OR REPLACE TYPE t1 FORCE AS OBJECT (d number);
*
ERROR at line 1:
ORA-22866: cannot replace a type with table dependents

See Also:

Oracle Database PL/SQL Language Reference for details of the CREATE OR REPLACE TYPE SQL statement

Type Dependencies of Substitutable Tables and Columns

A substitutable table or column of a specific type is dependent not only on that type but on all subtypes of the type as well. This is because a hidden column is added to the table for each attribute added in a subtype of the type. The hidden columns are added even if the substitutable table or column contains no data of that subtype.

See Also:

"Substituting Types in a Type Hierarchy" for further explanation of substitutability

In Example 7-10, a persons table of type person_typ is dependent not only on person_typ but also on the person_typ subtypes student_typ and part_time_student_typ.

If you attempt to drop a subtype that has a dependent type, table, or column, the DROP TYPE statement returns an error and aborts. Consequently, trying to drop a part_time_student_typ raises an error because of the dependent persons table.

If dependent tables or columns exist but contain no data of the type being dropped, you can use the VALIDATE keyword to drop the type. The VALIDATE keyword causes Oracle database to check for actual stored instances of the specified type and to drop the type if none are found. This also removes hidden columns associated with attributes unique to the type.

In Example 7-10, the first DROP TYPE statement fails because part_time_student_typ has a dependent table (persons). But if persons contains no instances of part_time_student_typ (nor does any other dependent table or column), the VALIDATE keyword causes the second DROP TYPE statement to succeed.

Example 7-10 DROP TYPE with and without VALIDATE

CREATE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20))
  NOT FINAL;
/
CREATE TYPE student_typ UNDER person_typ (
    dept_id NUMBER,
    major VARCHAR2(30))
NOT FINAL;
/
CREATE TYPE part_time_student_typ UNDER student_typ (number_hours NUMBER);
/
CREATE TABLE persons OF person_typ;
-- Following generates an error due to presence of Persons table 
DROP TYPE part_time_student_typ -- incorrect statement;
-- Following succeeds if there are no stored instances of part_time_student_typ
DROP TYPE part_time_student_typ VALIDATE;

Note:

Oracle recommends that you always use the VALIDATE option while dropping subtypes.

The DROP TYPE FORCE Option

The DROP TYPE statement also has a FORCE option that causes the type to be dropped even though it may have dependent types or tables. Use the FORCE option with great care, because any dependent types or tables that do exist are marked invalid and become inaccessible when the type is dropped. Data in a table that is marked invalid for this reason can never be accessed again. The only action that can be performed on such a table is to drop it.

See "Type Evolution" for information about how to alter a type.

Synonyms for Object Types

Just as you can create synonyms for tables, views, and various other schema objects, you can also define synonyms for object types.

Synonyms for types have the same advantages as synonyms for other kinds of schema objects: they provide a location-independent way to reference the underlying schema object. An application that uses public type synonyms can be deployed unaltered, in any schema of a database, without requiring a qualified type name with the schema name.

See Also:

Oracle Database Administrator's Guide for more information on synonyms in general

This section contains the following topics:

Creating a Type Synonym

You create a type synonym with a CREATE SYNONYM statement. The user must have been granted CREATE SYNONYM and CREATE PUBLIC SYNONYM privileges.

For example, these statements create a type typ1 and then create a synonym for it:

Example 7-11 CREATE TYPE / SYNONYM for user1

-- Example requires Ex.7-1 which created user1 and granted it the CREATE SYNONYM
-- and CREATE PUBLIC SYNONYM privileges
-- connect as user1 if not already connected.

CREATE TYPE typ1 AS OBJECT (x number);
/
CREATE SYNONYM syn1 FOR typ1;

Synonyms can be created for collection types, too. The following example creates a synonym for a nested table type:

CREATE TYPE typ2 AS TABLE OF NUMBER;
/
CREATE SYNONYM syn2 FOR typ2;

You create a public synonym by using the PUBLIC keyword:

CREATE TYPE shape AS OBJECT ( name VARCHAR2(10) );
/
CREATE PUBLIC SYNONYM pub_shape FOR shape;

With the REPLACE option you can make the synonym point to a different underlying type. For example, the following statement causes syn1 to point to type typ2 instead of the type it formerly pointed to:

CREATE OR REPLACE SYNONYM syn1 FOR typ2;

Using a Type Synonym

You can use a type synonym anywhere that you can refer to a type. For instance, you can use a type synonym in a DDL statement to name the type of a table column or type attribute.

Example 7-12 uses synonym syn1 to specify the type of an attribute in type typ3:

Example 7-12 Using a Type Synonym in a Create Statement

-- Requires Ex 7-1 and connection as user1
-- drop syn1 and typ1 if created for Ex. 7-12
CREATE TYPE typ1 AS OBJECT (x number);
/
CREATE SYNONYM syn1 FOR typ1;

CREATE TYPE typ3 AS OBJECT ( a syn1 );
/

In the next statement, the type synonym syn1 calls the constructor of the object type typ1, for which syn1 is a synonym. The statement returns an object instance of typ1:

SELECT syn1(0) FROM dual;

In the following, syn2 is a synonym for a nested table type. The synonym replaces the actual type name in a CAST expression.

SELECT CAST(MULTISET(SELECT eno FROM USER3.EMP) AS syn2) FROM dual;

This code returns the following output:


SQL> -- Type synonym used to call a constructor / nested table
SELECT syn1(0) FROM dual;
SELECT CAST(MULTISET(SELECT eno FROM USER3.EMP) AS syn2) FROM
dual;

SQL> SYN1(0)(X)
----------------------------------------------------------------
TYP1(0)

SQL>
CAST(MULTISET(SELECTENOFROMUSER3.EMP)ASSYN2)
----------------------------------------------------------------
TYP2()

Type synonyms can be used in the following kinds of statements:

  • DML statements: SELECT, INSERT, UPDATE, DELETE, FLASHBACK TABLE, EXPLAIN PLAN, and LOCK TABLE

  • DDL statements: AUDIT, NOAUDIT, GRANT, REVOKE, and COMMENT

Describing Schema Objects That Use Synonyms

If a type or table has been created using type synonyms, the DESCRIBE command shows the synonyms in place of the types they represent. Similarly, catalog views, which show type names, such as USER_TYPE_ATTRS, show the type synonym names in their place.

You can query the catalog view USER_SYNONYMS to find out the underlying type of a type synonym.

See Also:

Chapter 2 of Oracle Database Reference for a complete list of the data dictionary catalog views

Dependents of Type Synonyms

A type that directly or indirectly references a synonym in its type declaration is a dependent of that synonym. Thus, in the following line from Example 7-12, type typ3 is a dependent type of synonym syn1.

CREATE TYPE typ3 AS OBJECT ( a syn1 );
/

Other kinds of schema objects that reference synonyms in their DDL statements also become dependents of those synonyms. An object that depends on a type synonym depends on both the synonym and the underlying type of the synonym.

The dependency relationships of a synonym affect your ability to drop or rename the synonym. Dependent schema objects are also affected by some operations on synonyms. The following sections describe these various ramifications.

Restriction on Replacing a Type Synonym

You can replace a synonym only if it has no dependent tables or valid user-defined types. Replacing a synonym is equivalent to dropping it and then re-creating a new synonym with the same name.

Dropping Type Synonyms

You drop a synonym with the DROP SYNONYM statement as shown in Example 7-13.

Example 7-13 Dropping Type Synonyms

CREATE SYNONYM syn4 FOR typ1;

DROP SYNONYM syn4;

You cannot drop a type synonym if it has table or valid object types as dependents unless you use the FORCE option. The FORCE option causes any columns that directly or indirectly depend on the synonym to be marked unused, just as if the actual types of the columns were dropped. (A column indirectly depends on a synonym if, for instance, the synonym is used to specify the type of an attribute of the declared type of the column.)

Any dependent schema objects of a dropped synonym are invalidated. They can be revalidated by creating a local object or a new public synonym with the same name as the dropped synonym.

Dropping the underlying base type of a type synonym has the same effect on dependent objects as dropping the synonym.

Renaming Type Synonyms

You can rename a type synonym with the RENAME statement. Renaming a synonym is equivalent to dropping it and then re-creating it with a new name. You cannot rename a type synonym if it has dependent tables or valid object types. The following example fails because synonym syn1 has a dependent object type:

RENAME syn1 TO syn3 -- invalid statement;

Public Type Synonyms and Local Schema Objects

You cannot create a local schema object that has the same name as a public synonym if the public synonym has a dependent table or valid object type in the local schema that will hold the new schema object. Nor can you create a local schema object that has the same name as a private synonym in the same schema.

For instance, in the following example, table shape_tab is a dependent table of public synonym pub_shape because the table has a column that uses the synonym in its type definition. Consequently, the attempt to create a table that has the same name as public synonym pub_shape, in the same schema as the dependent table, fails:

-- Following uses public synonym pub_shape
CREATE TABLE shape_tab ( c1 pub_shape );
-- Following is not allowed
CREATE TABLE pub_shape ( c1 NUMBER ) -- invalid statement;

Performance Tuning

When tuning objects, the following items need to be addressed:

  • How objects and object views consume CPU and memory resources during runtime

  • How to monitor memory and CPU resources during runtime

  • How to manage large numbers of objects

Some of the key performance factors are the following:

  • DBMS_STATS package to collect statistics

  • tkprof to profile execution of SQL commands

  • EXPLAIN PLAN to generate the query plans

    See Also:

    Oracle Database Performance Tuning Guide for details on measuring and tuning the performance of your application