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.
PACKAGE [schema.]package_name COMPILE
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:
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
SCHEMA AUTHORIZATION schema
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:
REPLACE] LIBRARY [schema.]library IS|AS 'filespec';
schema. The schema in which the library
is to reside. If not specified, it defaults to the user’s default
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
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:
FUNCTION bitand (
left IN BINARY_INTEGER, right IN BINARY_INTEGER)
left INDICATOR long,
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)
EXTERNAL NAME "factorial"
input_number INDICATOR short,
output_factorial INDICATOR long,
RETURN INDICATOR short);
Available libraries can be viewed via queries
to the Remote DBA_LIBRARIES, ALL_LIBRARIES, and USER_LIBRARIES views.
The CREATE OR REPLACE option of the CREATE
command for libraries is used to alter libraries; there is no ALTER
Dropping a Library
A library is dropped via the DROP LIBRARY
command. The command has the following format:
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
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
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:
REPLACE] DIRECTORY directory_name AS 'path_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
The command below associates the directory
internal Oracle alias g_vid_lib with the directory ‘/video/library/g_rated’.
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.
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@@@
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
Once a directory is dropped, all BFILEs in
that directory location become inaccessible.
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.