Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Creating a supplemental catalog view for external files

with one comment

It’s not recommended to mess with the catalog but, occasionally, it’s the right thing to do. This code creates a read only catalog view that links table, virtual directory, and external file names. It seems a better course of action to create the view as SYS, rather than grant permissions to SYSTEM on three catalog tables. Clearly, there are differences of opinion on pursing this type of solution.

Oracle Database 11g creates two catalog tables for external files – EXTERNAL_TAB$ and EXTERNAL_LOCATION$. They’re created by the dexttab.bsq file when you build an instance. You’re also provided with a DBA_EXTERNAL_TABLES, ALL_EXTERNAL_TABLES, and USER_EXTERNAL_TABLES. This example only creates a user view only.

1. Creates the view.

CREATE OR REPLACE VIEW user_external_table_files AS
SELECT   o.name AS table_name
,        'SYS' AS table_owner
,        xt.type$ AS type_name
,        xl.name AS file_name
,        'SYS' AS default_directory_owner
,        xt.default_dir AS default_directory_name
,        DECODE(xt.reject_limit,2147483647,'UNLIMITED',xt.reject_limit) AS reject_limit
,        DECODE(xt.par_type,1,'BLOB',2,'CLOB','UNKNOWN') AS access_type
,        CASE WHEN xt.par_type = 2 THEN xt.param_clob ELSE NULL END AS access_parameters
,        DECODE(xt.property,2,'REFERENCED',1,'ALL','UNKNOWN') AS property
FROM     sys.external_tab$ xt
,        sys.external_location$ xl
,        sys.obj$ o
WHERE    o.owner# = userenv('SCHEMAID')
AND      o.obj# = xt.obj#
AND      o.obj# = xl.obj#;

2. Grant SELECT privileges to specific users or PUBLIC (probably a bad idea):

SQL> GRANT select ON user_external_table_files TO user_name;

3. Create a synonym for the view:

SQL> CREATE SYNONYM user_external_table_files FOR sys.user_external_table_files;

You now have access to the table, virtual directory, and file names as TABLE_NAME, DEFAULT_DIRECTORY_NAME, and FILE_NAME columns respectively.

 

Advertisements

Written by maclochlainn

June 20, 2008 at 3:17 am

One Response

Subscribe to comments with RSS.

  1. […] The catalog view is here … Tagged with: Data Pump, data_pump, oracle_loader, SQL*Loader, USER_EXTERNAL_TABLES « Creating a supplemental catalog view […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: