BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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

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:



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';


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:

NAME "bitand"
LIBRARY dev_c_lib
left INT,
left INDICATOR long,
right INT,
right INDICATOR short,
       An external procedure call is similar:
input_number IN INTEGER,
number_factorial OUT INTEGER)
EXTERNAL NAME "factorial"
LIBRARY "dev_c_lib"
input_number INDICATOR short,
input_number INT,
output_factorial INDICATOR long,
output_factorial INT,

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';


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.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter