Sunday 26 August 2012

DBMS_METADATA Valid Object Types


DBMS_METADATA takes object type parameters that do not always match the object type you'd find in DBA_OBJECTS. e.g. in the following, which gets DDL for a database link, notice that "DB_LINK", not "DATABASE LINK", is passed as the object type:

SELECT DBMS_METADATA.GET_DDL('DB_LINK','SCOTTS_LINK','SCOTT')
FROM DUAL;

To get the definitive object type list visit http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#i998485 and navigate to "Table 57-18 Object Type Path Names for Heterogeneous Object Types"

Wednesday 1 August 2012

SP2-0027: Input Is Too Long

If you are attempting to execute DBMS_TTS.TRANSPORT_SET_CHECK over a long list of tablespaces, then you may encounter "SP2-0027: Input Is Too Long".

The solution is to use PL/SQL, where the VARCHAR2 length limit is 32767 (i.e. 32K - 1).

The following example loops through a list of schemas, and, per schema, creates a comma-separated list of all tablespaces, and then executes DBMS_TTS.TRANSPORT_SET_CHECK per schema:


DECLARE
LONG_VARCHAR2 VARCHAR2(32767);

BEGIN

    FOR X IN
    (
    SELECT DISTINCT TABLESPACE_NAME
      FROM DBA_SEGMENTS
     WHERE OWNER IN ('SCOTT','FRED','MEGA_APP')
    )
    LOOP
        LONG_VARCHAR2 := LONG_VARCHAR2||X.TABLESPACE_NAME||',';
    END LOOP;

-- remove the final comma
LONG_VARCHAR2 := SUBSTR(LONG_VARCHAR2,1,LENGTH(LONG_VARCHAR2)-1);

-- do the work
DBMS_TTS.TRANSPORT_SET_CHECK('' || LONG_VARCHAR2 || '', TRUE);

-- show the list of tablespaces we just worked on
DBMS_OUTPUT.PUT_LINE(LONG_VARCHAR2);
END;
/