Library and Directory Management Tips Oracle Tips by Burleson Consulting

The Remote DBA will be required to alter a package when there are changes to tables, views, sequences, and so on that the package procedures and functions reference. This is accomplished through the use of the CREATE OR REPLACE PACKAGE [BODY] form of the CREATE PACKAGE command. The format for the command is identical to that of the CREATE PACKAGE [BODY] command. But be aware that all procedures, variables, and functions referenced in the CREATE PACKAGE command must be present in the CREATE OR REPLACE PACKAGE BODY command, for if you just use the command with a single procedure or function you want altered, that will be the only object left in the body when you are finished. Perhaps with a future release we will be able to use the package definition as a link list, and this won’t be required. There is also an ALTER PACKAGE BODY command that is used only to recompile the package body. The format of the ALTER command follows.

ALTER PACKAGE [schema.]package_name COMPILE
[DEBUG PACKAGE|SPECIFICATION|BODY];

The DEBUG clause has been added to compile and store debugging information for the specified area of the package for the PL/SQL debugger.

Dropping a Package

Even such wonderful things as packages have limited lifetimes. Applications are replaced or are no longer needed; entire database practices are rethought and changed. This leads to the requirement to be able to drop packages that are no longer needed. This is accomplished through the DROP PACKAGE command. The format of this command follows:

DROP PACKAGE [BODY] [schema.]package_name;

Exclusion of the keyword BODY results in the drop of both the definition and the body. Inclusion of BODY drops just the package body, leaving the definition intact.

When a package is dropped, all dependent objects are invalidated. If the package is not re-created before one of the dependent objects is accessed, Oracle tries to recompile the package; this will return an error and cause failure of the command.

Administration of Schemas

A feature of Oracle7, Oracle8, Oracle8i, and Oracle9i that has been mentioned but not explained in the previous sections is the schema. Those Remote DBAs familiar with other database systems may recognize this concept as it has been used in systems such as INFORMIX for several years. However, the concept of schemas wasn’t introduced to Oracle until Oracle7.

A schema is a logical grouping of related database objects; it roughly compares

to the owner of the objects. Objects in a given schema do not have to be in the same tablespace. In fact, each user has a default schema that corresponds to his or her user name in Oracle. A user may only create objects in his or her own schema (that schema named the same as the user he or she is logged in under). Schemas can be populated via the CREATE SCHEMA command or by using individual object CREATE commands.

Creation of Schemas

The CREATE SCHEMA statement can include the creation commands for tables, views, and grants. The user issuing the command must have the appropriate privileges to create each of the objects mentioned in the CREATE SCHEMA command. The format of this command follows:

CREATE SCHEMA AUTHORIZATION schema
Create_table_statement|create_view_statement|grant_statement;

where:

schema. The user’s schema; it must be his or her user name.

*_statement. Corresponds to the appropriate CREATE object command.

The individual create commands are not separated with a command terminator; the terminator is placed at the end of the entire create sequence of commands for the schema. Schemas cannot be altered or dropped; only individual schema objects can be altered or dropped. Note that you cannot specify nonstandard SQL in the CREATE SCHEMA command; therefore, clauses such as STORAGE and partitioning logic cannot be used.

Administration of Library Objects

Under Oracle8, a new type of schema object, known as a library, was introduced. A library is a schema object that represents an operating system shared library from which calls can be made by SQL and PL/SQL to external functions and procedures. Note that libraries are not physical schema objects; they are stored pointers to physical operating system-shared library files.

To create a library, you must have the CREATE ANY LIBRARY internal Oracle system privilege. To use the functions and procedures stored in the library, you must have the execute privilege at the operating system level on the shared library that is referenced. Libraries can only be used on operating systems that allow shared libraries and dynamic linking.

The file specified as the source for the shared library is not verified to exist when the library is initially specified; it is verified at runtime.

The syntax for the CREATE LIBRARY command is:

CREATE [OR REPLACE] LIBRARY [schema.]library IS|AS 'filespec';

where:

schema. The schema in which the library is to reside. If not specified, it defaults to the user’s default schema.

library. The name for the schema object; it must comply with object naming standards.

filespec. The existing operating system shared library that is to correspond to the internal library name.

Example of the Use of CREATE LIBRARY

The following is an example of the creation of a library:

CREATE OR REPLACE LIBRARY dev_c_lib IS '/users/oracle/c/lib/sharedlibs.so.1.0';

Once a library link to an external shared library is created, the functions and procedures inside can be referenced for use from SQL and PL/SQL:

CREATE FUNCTION bitand (
left IN BINARY_INTEGER, right IN BINARY_INTEGER)
RETURN BINARY_INTEGER
IS
EXTERNAL
NAME "bitand"
LIBRARY dev_c_lib
PARAMETERS (
left INT,
left INDICATOR long,
right INT,
right INDICATOR short,
RETURN INDICATOR short);
       An external procedure call is similar:
CREATE OR REPLACE PROCEDURE factorial (
input_number IN INTEGER,
number_factorial OUT INTEGER)
IS
EXTERNAL NAME "factorial"
LIBRARY "dev_c_lib"
PARAMETERS  (
input_number INDICATOR short,
input_number INT,
output_factorial INDICATOR long,
output_factorial INT,
RETURN INDICATOR short);

Available libraries can be viewed via queries to the Remote DBA_LIBRARIES, ALL_LIBRARIES, and USER_LIBRARIES views.

Altering Libraries

The CREATE OR REPLACE option of the CREATE command for libraries is used to alter libraries; there is no ALTER LIBRARY command.

Dropping a Library

A library is dropped via the DROP LIBRARY command. The command has the following format:

DROP LIBRARY [schema.]library;

The drop command removes only the library alias at the database level; it does not affect the status of the operating system shared library.

Administration of Directories

A directory is an internal Oracle8, 8i, and 9i database pointer to a physical operating system directory where external files (in Oracle9i) and database objects of type BFILE are stored. As noted in Chapter 4, BFILEs are large binary objects such as video segments, which would be impractical to store within the database structure itself. An external file is used to create an external table reference. An internal Oracle directory is an alias that points to the external physical directory.

A directory in Oracle has no size or space requirements other than that for a table entry in the data dictionary. You must have the CREATE ANY DIRECTORY system privilege to create a directory alias in Oracle. The operating system directory must have the proper access permissions (READ) to allow the Oracle process access.

A BFILE column in a database table contains a locator that has the directory alias specified in a CREATE DIRECTORY command and a filename. The locator maintains the directory alias and filename.

A directory is created inside a single namespace; it is not owned by a schema. Therefore, directory names have to be unique across the entire database. You grant access to the BFILEs or external files in a specific directory by granting READ access to the users or roles that require access. When a directory is created by a user, that user automatically receives the READ grant with the admin option so it can be subsequently granted to others.

A directory is created with the CREATE DIRECTORY command; its syntax follows:

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';

where:

directory_name. Database-unique directory name.

path_name. Operating system directory path (note that if the OS is case-sensitive, the name must be exact)

Use of the CREATE DIRECTORY Command: An Example

The command below associates the directory internal Oracle alias g_vid_lib with the directory ‘/video/library/g_rated’.

CREATE OR REPLACE DIRECTORY g_vid_lib AS '/video/library/g_rated';

The path name must be a full path and not use any system logicals or symbols. The directory’s existence is not validated until the directory alias is referenced by Oracle.

Altering Directories

There is no ALTER DIRECTORY command. To change a DIRECTORY, you must drop and re-create it. @@@Au: Update? What about 8i or 9i? Fixed@@@

Dropping Directories

When a directory is no longer needed, it should be dropped with the DROP DIRECTORY command. You must have the DROP ANY DIRECTORY command to drop a directory. The syntax for this command is:

DROP DIRECTORY directory_name;

Once a directory is dropped, all BFILEs in that directory location become inaccessible.

See Code Depot for Full Scripts

---

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.