Oracle DBMS_TDB Demos

Oracle DBMS_TDB Version 11.2.0.3

General Information

Purpose

The DBMS_TDB package serves two purposes:

Confirms support of transporting a database from a given source platform to a given target platform
Determining whether a database to be transported has been properly prepared for transport, and if not, identifies the condition that prevents database transport

AUTHID

DEFINER

Constants

Name	Data Type	Value
SKIP_NONE	NUMBER	0
SKIP_INACCESSIBLE	NUMBER	1
SKIP_OFFLINE	NUMBER	2
SKIP_READONLY	NUMBER	3

Dependencies

DBMS_OUTPUT	TS$	V$TRANSPORTABLE_PLATFORM
DBMS_PLUGTS	V$DATABASE	X$KCVFH
DBMS_SYS_ERROR	V$DATAFILE	X$KTUSUS
DUAL	V$DB_TRANSPORTABLE_PLATFORM	X$KTUXE

Security Model

Owned by SYS with EXECUTE granted to the DBA role

Source

{ORACLE_HOME}/rdbms/admin/dbmsplts.sql

Subprograms

CHECK_DB

CHECK_EXTERNAL

EXIT_TRANSPORT_SCRIPT

CHECK_DB

Overload 1

dbms_tdb.check_db(
target_platform_name IN VARCHAR2,
skip_option IN NUMBER)
RETURN BOOLEAN;

SELECT platform_name
FROM gv$transportable_platform
ORDER BY 1;

set serveroutput on

DECLARE
x BOOLEAN;
BEGIN
x := dbms_tdb.check_db('Microsoft Windows IA (32-bit)', 0);
IF x THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/

Overload 2

dbms_tdb.check_db(target_platform_name IN VARCHAR2) RETURN BOOLEAN;

SELECT platform_name
FROM gv$transportable_platform
ORDER BY 1;

set serveroutput on

DECLARE
x BOOLEAN;
BEGIN
x := dbms_tdb.check_db('Apple Mac OS');
IF x THEN
    dbms_output.put_line('TRUE');
ELSE
    dbms_output.put_line('FALSE');
END IF;
END;
/

DECLARE
x BOOLEAN;
BEGIN
x := dbms_tdb.check_db('Linux IA (32-bit)');
IF x THEN
    dbms_output.put_line('TRUE');
ELSE
    dbms_output.put_line('FALSE');
END IF;
END;
/

Overload 3

dbms_tdb.check_db RETURN BOOLEAN;

set serveroutput on

DECLARE
x BOOLEAN;
BEGIN
x := dbms_tdb.check_db;
IF x THEN
    dbms_output.put_line('TRUE');
ELSE
    dbms_output.put_line('FALSE');
END IF;
END;
/

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE OPEN READ ONLY;

set serveroutput on

DECLARE
x BOOLEAN;
BEGIN
x := dbms_tdb.check_db;
IF x THEN
    dbms_output.put_line('TRUE');
ELSE
    dbms_output.put_line('FALSE');
END IF;
END;
/

SHUTDOWN IMMEDIATE;

STARTUP;

CHECK_EXTERNAL

Check if a database has external table, directory or BFILE. Uses dbms_output.put_line to output the external objects and their owners

dbms_tts.check_external RETURN BOOLEAN;

set serveroutput on

DECLARE
x BOOLEAN;
BEGIN
x := dbms_tdb.check_external;
IF x THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/

EXIT_TRANSPORT_SCRIPT

Internal: Used in transport script to throw a SQL error so that the transport script can exit

dbms_tdb.exit_transport_script(should_exit IN VARCHAR2);

Generates an ORA-09330 when run in an independent session