|
|
bfc99 发表于 2015-7-28 15:30 ![]()
那是加密的吧？怎么看啊？
PACKAGE BODY dbms_utility IS
ARRAY_TYPE_UNCL CONSTANT BINARY_INTEGER := 1;
ARRAY_TYPE_LNAME CONSTANT BINARY_INTEGER := 2;
MAX_BYTES_PER_CHAR CONSTANT NUMBER := 4;
MAX_LNAME_LENGTH CONSTANT BINARY_INTEGER := 4000;
FUNCTION IS_PARALLEL RETURN BINARY_INTEGER;
PRAGMA INTERFACE (C, IS_PARALLEL);
FUNCTION ICD_GET_TIME RETURN BINARY_INTEGER;
PRAGMA INTERFACE (C, ICD_GET_TIME);
PROCEDURE ICD_NAME_RES(NAME IN VARCHAR2, CONTEXT IN BINARY_INTEGER,
SCHEMA OUT VARCHAR2, PART1 OUT VARCHAR2, PART2 OUT VARCHAR2,
DBLINK OUT VARCHAR2, PART1_TYPE OUT BINARY_INTEGER,
OBJECT_NUMBER OUT BINARY_INTEGER);
PRAGMA INTERFACE (C, ICD_NAME_RES);
PROCEDURE ICD_NAME_TOKENIZE( NAME IN VARCHAR2,
A OUT VARCHAR2,
B OUT VARCHAR2,
C OUT VARCHAR2,
DBLINK OUT VARCHAR2,
NEXTPOS OUT BINARY_INTEGER);
PRAGMA INTERFACE (C, ICD_NAME_TOKENIZE);
FUNCTION PSDPOR RETURN VARCHAR2;
PRAGMA INTERFACE (C, PSDPOR);
FUNCTION ICD_DBA(FILE BINARY_INTEGER, BLOCK BINARY_INTEGER)
RETURN NUMBER;
PRAGMA INTERFACE (C, ICD_DBA);
FUNCTION ICD_DBA_FILE(DBA NUMBER) RETURN BINARY_INTEGER;
PRAGMA INTERFACE (C, ICD_DBA_FILE);
FUNCTION ICD_DBA_BLOCK(DBA NUMBER) RETURN BINARY_INTEGER;
PRAGMA INTERFACE (C, ICD_DBA_BLOCK);
FUNCTION PSDDBV RETURN VARCHAR2;
PRAGMA INTERFACE (C,PSDDBV);
FUNCTION ICD_HASH(NAME VARCHAR2, BASE BINARY_INTEGER,
HASH_SIZE BINARY_INTEGER)
RETURN BINARY_INTEGER;
PRAGMA INTERFACE (C, ICD_HASH);
PROCEDURE KSPGPNICD(PARNAM IN VARCHAR2,
PARTYP IN OUT BINARY_INTEGER,
INTVAL IN OUT BINARY_INTEGER,
STRVAL IN OUT VARCHAR2,
LISTNO IN BINARY_INTEGER);
PRAGMA INTERFACE (C, KSPGPNICD);
PROCEDURE PSDANAM( NAME IN VARCHAR2,
MAX_LNAME_LENGTH IN BINARY_INTEGER,
NEXTPOS OUT BINARY_INTEGER);
PRAGMA INTERFACE (C, PSDANAM);
PROCEDURE PSDCNAM( NAME IN VARCHAR2,
CANON_LEN IN BINARY_INTEGER,
CANON_NAME OUT VARCHAR2);
PRAGMA INTERFACE (C, PSDCNAM);
FUNCTION PSDGTR (REGIONID BINARY_INTEGER) RETURN MAXRAW;
PRAGMA INTERFACE (C, PSDGTR);
PROCEDURE GET_TZ_TRANSITIONS(REGIONID NUMBER,TRANSITIONS OUT MAXRAW)
IS BEGIN TRANSITIONS := PSDGTR(REGIONID); END;
FUNCTION GET_PARAMETER_VALUE(PARNAM IN VARCHAR2,
INTVAL IN OUT BINARY_INTEGER,
STRVAL IN OUT VARCHAR2,
LISTNO IN BINARY_INTEGER DEFAULT 1)
RETURN BINARY_INTEGER IS
PARTYP BINARY_INTEGER;
BEGIN
IF LISTNO <= 0 THEN
RAISE_APPLICATION_ERROR(-20000,
'get_parameter_value: listno must be greater than zero');
END IF;
IF PARNAM IS NULL THEN
RAISE_APPLICATION_ERROR(-20000,
'get_parameter_value: input parameter must not be null');
END IF;
KSPGPNICD(PARNAM, PARTYP, INTVAL, STRVAL, LISTNO);
IF INTVAL IS NULL AND STRVAL IS NULL THEN
RAISE_APPLICATION_ERROR(-20000,
'get_parameter_value: invalid or unsupported parameter "'||
SUBSTR(PARNAM,1,1000)||'"');
END IF;
RETURN PARTYP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END GET_PARAMETER_VALUE;
PROCEDURE NAME_RESOLVE(NAME IN VARCHAR2, CONTEXT IN NUMBER,
SCHEMA OUT VARCHAR2, PART1 OUT VARCHAR2, PART2 OUT VARCHAR2,
DBLINK OUT VARCHAR2, PART1_TYPE OUT NUMBER, OBJECT_NUMBER OUT NUMBER) IS
BEGIN
IF CONTEXT != 1 AND CONTEXT != 2 AND CONTEXT != 3 AND CONTEXT != 4 AND CONTEXT != 5 AND CONTEXT != 6 AND CONTEXT != 7 THEN
RAISE_APPLICATION_ERROR(-20005,'ORU-10034: context argument must be 1 or 2 or 3 or 4 or 5 or 6 or 7');
END IF;
ICD_NAME_RES(NAME, CONTEXT, SCHEMA, PART1, PART2, DBLINK, PART1_TYPE,
OBJECT_NUMBER);
END;
PROCEDURE NAME_TOKENIZE( NAME IN VARCHAR2,
A OUT VARCHAR2,
B OUT VARCHAR2,
C OUT VARCHAR2,
DBLINK OUT VARCHAR2,
NEXTPOS OUT BINARY_INTEGER) IS
BEGIN
ICD_NAME_TOKENIZE( NAME, A, B, C, DBLINK, NEXTPOS );
END;
PROCEDURE LNAME_PARSE( NAME IN VARCHAR2,
MAX_LNAME_LENGTH IN BINARY_INTEGER,
NEXTPOS OUT BINARY_INTEGER) IS
BEGIN
PSDANAM( NAME, MAX_LNAME_LENGTH, NEXTPOS);
END;
PROCEDURE COMMA_TO_TABLE( LIST IN VARCHAR2,
ARRAY_TYPE IN BINARY_INTEGER,
TABLEN OUT BINARY_INTEGER,
TAB_U OUT UNCL_ARRAY,
TAB_A OUT LNAME_ARRAY ) IS
NEXTPOS BINARY_INTEGER;
OLDPOS BINARY_INTEGER;
DONE BOOLEAN;
I BINARY_INTEGER;
LEN BINARY_INTEGER;
DUMMY VARCHAR2(128);
BEGIN
NEXTPOS := 1;
DONE := FALSE;
I := 1;
LEN := NVL(LENGTHB(LIST),0);
WHILE NOT DONE LOOP
OLDPOS := NEXTPOS;
IF ARRAY_TYPE = ARRAY_TYPE_UNCL THEN
DBMS_UTILITY.NAME_TOKENIZE( SUBSTRB(LIST,OLDPOS),
DUMMY, DUMMY, DUMMY, DUMMY, NEXTPOS );
TAB_U(I) := SUBSTRB( LIST, OLDPOS, NEXTPOS );
ELSE
DBMS_UTILITY.LNAME_PARSE( SUBSTRB(LIST,OLDPOS), MAX_LNAME_LENGTH,
NEXTPOS );
TAB_A(I) := SUBSTRB( LIST, OLDPOS, NEXTPOS );
END IF;
NEXTPOS := OLDPOS + NEXTPOS;
IF NEXTPOS > LEN THEN
DONE := TRUE;
ELSIF SUBSTRB(LIST,NEXTPOS,1) = ',' THEN
NEXTPOS := NEXTPOS + 1;
ELSE
RAISE_APPLICATION_ERROR( -20001,
'comma-separated list invalid near ' || SUBSTRB(LIST,NEXTPOS-2,5));
END IF;
I := I + 1;
END LOOP;
IF ARRAY_TYPE = ARRAY_TYPE_UNCL THEN
TAB_U(I) := NULL;
ELSE
TAB_A(I) := NULL;
END IF;
TABLEN := I-1;
END COMMA_TO_TABLE;
PROCEDURE COMMA_TO_TABLE( LIST IN VARCHAR2,
TABLEN OUT BINARY_INTEGER,
TAB OUT UNCL_ARRAY ) IS
TAB_DUMMY LNAME_ARRAY;
BEGIN
COMMA_TO_TABLE(LIST, ARRAY_TYPE_UNCL, TABLEN, TAB, TAB_DUMMY);
END COMMA_TO_TABLE;
PROCEDURE COMMA_TO_TABLE( LIST IN VARCHAR2,
TABLEN OUT BINARY_INTEGER,
TAB OUT LNAME_ARRAY ) IS
TAB_DUMMY UNCL_ARRAY;
BEGIN
COMMA_TO_TABLE(LIST, ARRAY_TYPE_LNAME, TABLEN, TAB_DUMMY, TAB);
END COMMA_TO_TABLE;
PROCEDURE TABLE_TO_COMMA( TAB IN UNCL_ARRAY,
TABLEN OUT BINARY_INTEGER,
LIST OUT VARCHAR2) IS
TEMP VARCHAR2(32512) := '';
I BINARY_INTEGER := 1;
BEGIN
IF TAB(I) IS NOT NULL THEN
TEMP := TAB(I);
I := I + 1;
WHILE TAB(I) IS NOT NULL LOOP
TEMP := TEMP || ',' || TAB(I);
I := I + 1;
END LOOP;
END IF;
TABLEN := I-1;
LIST := TEMP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
TABLEN := I-1;
LIST := TEMP;
END;
PROCEDURE TABLE_TO_COMMA( TAB IN LNAME_ARRAY,
TABLEN OUT BINARY_INTEGER,
LIST OUT VARCHAR2) IS
TEMP VARCHAR2(32512) := '';
I BINARY_INTEGER := 1;
BEGIN
IF TAB(I) IS NOT NULL THEN
TEMP := TAB(I);
I := I + 1;
WHILE TAB(I) IS NOT NULL LOOP
TEMP := TEMP || ',' || TAB(I);
I := I + 1;
END LOOP;
END IF;
TABLEN := I-1;
LIST := TEMP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
TABLEN := I-1;
LIST := TEMP;
END;
FUNCTION GET_TIME RETURN NUMBER IS
BEGIN
RETURN ICD_GET_TIME;
END;
FUNCTION IS_CLUSTER_DATABASE RETURN BOOLEAN IS
BEGIN
IF IS_PARALLEL = 1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
PROCEDURE COMPILE_SCHEMA (SCHEMA VARCHAR2,
COMPILE_ALL BOOLEAN DEFAULT TRUE,
REUSE_SETTINGS BOOLEAN DEFAULT FALSE) IS
NOT_EXIST_OR_NO_PRIV EXCEPTION;
PRAGMA EXCEPTION_INIT(NOT_EXIST_OR_NO_PRIV, -20000);
COMMIT_DISABLED EXCEPTION;
PRAGMA EXCEPTION_INIT(COMMIT_DISABLED, -34);
IN_DIST_TX EXCEPTION;
PRAGMA EXCEPTION_INIT(IN_DIST_TX, -2074);
IN_TRIGGER EXCEPTION;
PRAGMA EXCEPTION_INIT(IN_TRIGGER, -4092);
CURSOR INTERESTING_OBJS_C IS
SELECT O.OBJ#
FROM OBJ$ O, USER$ U
WHERE U.USER# = O.OWNER#
AND O.REMOTEOWNER IS NULL
AND U.NAME = SCHEMA
AND O.TYPE# IN (7, 8, 9, 11, 12)
AND (BITAND(O.FLAGS, 128) = 0);
BEGIN
IF (UPPER(SCHEMA) = 'SYS') THEN
RAISE_APPLICATION_ERROR(-20001, 'Cannot recompile SYS objects');
END IF;
IF (COMPILE_ALL) THEN
FOR INTERESTING_OBJ IN INTERESTING_OBJS_C LOOP
BEGIN
INVALIDATE(INTERESTING_OBJ.OBJ#,
CASE WHEN REUSE_SETTINGS = TRUE THEN 'REUSE SETTINGS'
ELSE ' '
END);
EXCEPTION
WHEN INV_NOT_EXIST_OR_NO_PRIV THEN
RAISE NOT_EXIST_OR_NO_PRIV;
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END IF;
BEGIN
UTL_RECOMP.RECOMP_SERIAL(SCHEMA);
EXCEPTION
WHEN NOT_EXIST_OR_NO_PRIV THEN
RAISE;
WHEN COMMIT_DISABLED OR IN_DIST_TX OR IN_TRIGGER THEN
RAISE;
WHEN OTHERS THEN
NULL;
END;
END;
PROCEDURE ANALYZE_SCHEMA(SCHEMA VARCHAR2, METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL) IS
NOT_EXIST_OR_NO_PRIV EXCEPTION;
PRAGMA EXCEPTION_INIT(NOT_EXIST_OR_NO_PRIV, -20000);
CURSOR C1(SCHEMA VARCHAR2) IS
SELECT OBJECT_NAME, OBJECT_TYPE
FROM SYS.DBA_ANALYZE_OBJECTS
WHERE OWNER = C1.SCHEMA
ORDER BY OBJECT_TYPE, OBJECT_NAME;
DUMMY VARCHAR2(1);
BEGIN
BEGIN
SELECT 'x' INTO DUMMY FROM SYS.USER$ WHERE NAME = SCHEMA AND TYPE# = 1;
EXCEPTION WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'user '''||SCHEMA||''' does not exist');
END;
FOR REC IN C1(SCHEMA) LOOP
BEGIN
DBMS_DDL.ANALYZE_OBJECT(REC.OBJECT_TYPE, '"'||SCHEMA||'"',
'"'||REC.OBJECT_NAME||'"',
METHOD, ESTIMATE_ROWS, ESTIMATE_PERCENT,
METHOD_OPT);
EXCEPTION WHEN NOT_EXIST_OR_NO_PRIV THEN
RAISE_APPLICATION_ERROR(-20000,
'You have insufficient privileges for an object in this schema.');
END;
END LOOP;
END;
PROCEDURE ANALYZE_DATABASE(METHOD VARCHAR2,
ESTIMATE_ROWS NUMBER DEFAULT NULL,
ESTIMATE_PERCENT NUMBER DEFAULT NULL,
METHOD_OPT VARCHAR2 DEFAULT NULL) IS
NOT_EXIST_OR_NO_PRIV EXCEPTION;
PRAGMA EXCEPTION_INIT(NOT_EXIST_OR_NO_PRIV, -20000);
CURSOR C1 IS
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM SYS.DBA_ANALYZE_OBJECTS
ORDER BY OBJECT_TYPE, OBJECT_NAME;
BEGIN
FOR REC IN C1 LOOP
BEGIN
DBMS_DDL.ANALYZE_OBJECT(REC.OBJECT_TYPE, '"'||REC.OWNER||'"',
'"'||REC.OBJECT_NAME||'"',
METHOD, ESTIMATE_ROWS, ESTIMATE_PERCENT,
METHOD_OPT);
EXCEPTION WHEN NOT_EXIST_OR_NO_PRIV THEN
RAISE_APPLICATION_ERROR(-20000,
'You have insufficient privileges for an object in this database.');
END;
END LOOP;
END;
FUNCTION PORT_STRING RETURN VARCHAR2 IS
BEGIN
RETURN(PSDPOR);
END PORT_STRING;
PROCEDURE DB_VERSION(VERSION OUT VARCHAR2,
COMPATIBILITY OUT VARCHAR2) IS
BEGIN
DB_VERSION.VERSION := PSDDBV;
SELECT VALUE INTO DB_VERSION.COMPATIBILITY FROM SYS.V$PARAMETER
WHERE NAME = 'compatible';
END DB_VERSION;
FUNCTION MAKE_DATA_BLOCK_ADDRESS(FILE NUMBER, BLOCK NUMBER) RETURN NUMBER IS
BEGIN
RETURN (ICD_DBA(FILE,BLOCK));
END;
FUNCTION DATA_BLOCK_ADDRESS_FILE(DBA NUMBER) RETURN NUMBER IS
BEGIN
RETURN (ICD_DBA_FILE(DBA));
END;
FUNCTION DATA_BLOCK_ADDRESS_BLOCK(DBA NUMBER) RETURN NUMBER IS
BEGIN
RETURN (ICD_DBA_BLOCK(DBA));
END;
FUNCTION GET_HASH_VALUE(NAME VARCHAR2, BASE NUMBER, HASH_SIZE NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (ICD_HASH(NAME, BASE, HASH_SIZE));
END;
PROCEDURE EXEC_DDL_STATEMENT(PARSE_STRING IN VARCHAR2) IS
CUR BINARY_INTEGER;
DUMMY NUMBER;
BEGIN
CUR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CUR,PARSE_STRING,DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(CUR);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(CUR) THEN
DBMS_SQL.CLOSE_CURSOR(CUR);
END IF;
RAISE;
END EXEC_DDL_STATEMENT;
PROCEDURE ANALYZE_PART_OBJECT (
SCHEMA IN VARCHAR2 DEFAULT NULL,
OBJECT_NAME IN VARCHAR2 DEFAULT NULL,
OBJECT_TYPE IN CHAR DEFAULT 'T',
COMMAND_TYPE IN CHAR DEFAULT 'E',
COMMAND_OPT IN VARCHAR2 DEFAULT NULL,
SAMPLE_CLAUSE IN VARCHAR2 DEFAULT 'sample 5 percent') IS
PART_COUNT NUMBER := 0;
PNAME VARCHAR2(30);
PSTR VARCHAR2(2000);
TNAME VARCHAR2(30);
ERR_STR VARCHAR2(2000);
X NUMBER :=0;
CURSOR TPART_CUR (WHOSE_TAB IN VARCHAR2,WHICH_TAB VARCHAR2) IS
SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = WHOSE_TAB AND
TABLE_NAME = WHICH_TAB;
CURSOR IPART_CUR (WHOSE_IND IN VARCHAR2,WHICH_IND VARCHAR2) IS
SELECT PARTITION_NAME FROM DBA_IND_PARTITIONS
WHERE INDEX_OWNER = WHOSE_IND AND
INDEX_NAME = WHICH_IND;
BEGIN
ERR_STR := NULL;
IF UPPER(OBJECT_TYPE) = 'T' THEN
OPEN TPART_CUR(SCHEMA,OBJECT_NAME);
ELSIF UPPER(OBJECT_TYPE) = 'I' THEN
IF COMMAND_OPT IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001,
'<command_opt> not allowed for index');
END IF;
OPEN IPART_CUR(SCHEMA,OBJECT_NAME);
ELSE
RAISE_APPLICATION_ERROR(-20001, 'Object type must be T/I');
END IF;
LOOP
IF UPPER(OBJECT_TYPE) = 'T' THEN
FETCH TPART_CUR INTO PNAME;
EXIT WHEN TPART_CUR%NOTFOUND;
PSTR := 'analyze table ';
ELSIF UPPER(OBJECT_TYPE) = 'I' THEN
FETCH IPART_CUR INTO PNAME;
EXIT WHEN IPART_CUR%NOTFOUND;
PSTR := 'analyze index ';
END IF;
PART_COUNT := PART_COUNT + 1;
PSTR := PSTR|| DBMS_ASSERT.ENQUOTE_NAME(SCHEMA) || '.'||
DBMS_ASSERT.ENQUOTE_NAME(OBJECT_NAME);
PSTR := PSTR||' partition("'||PNAME||'" ';
IF UPPER(COMMAND_TYPE) = 'E' THEN
PSTR := PSTR||' estimate statistics';
IF COMMAND_OPT IS NOT NULL THEN
PSTR := PSTR||' for '||DBMS_ASSERT.NOOP(COMMAND_OPT);
END IF;
IF SAMPLE_CLAUSE IS NOT NULL THEN
PSTR := PSTR||' '||DBMS_ASSERT.NOOP(SAMPLE_CLAUSE);
END IF;
ELSIF UPPER(COMMAND_TYPE) = 'C' THEN
PSTR := PSTR||' compute statistics';
IF COMMAND_OPT IS NOT NULL THEN
PSTR := PSTR||' '||DBMS_ASSERT.NOOP(COMMAND_OPT);
END IF;
ELSIF UPPER(COMMAND_TYPE) = 'D' THEN
PSTR := PSTR||'delete statistics';
ELSIF UPPER(COMMAND_TYPE) = 'V' THEN
PSTR := PSTR||' validate structure';
IF COMMAND_OPT IS NOT NULL THEN
PSTR := PSTR||' '||DBMS_ASSERT.NOOP(COMMAND_OPT);
END IF;
ELSE
ERR_STR := 'Invalid option - choose one of C/D/E/V';
EXIT;
END IF;
DBMS_JOB.SUBMIT(X,
'dbms_utility.exec_ddl_statement('||''''||PSTR||''''||');');
END LOOP;
IF UPPER(OBJECT_TYPE) = 'T' THEN
CLOSE TPART_CUR;
ELSIF UPPER(OBJECT_TYPE) = 'I' THEN
CLOSE IPART_CUR;
END IF;
COMMIT;
IF ERR_STR IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001, ERR_STR);
END IF;
IF PART_COUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001,
'Object does not exist or is not partitioned');
END IF;
END ANALYZE_PART_OBJECT;
FUNCTION CURRENT_INSTANCE RETURN NUMBER IS
CUR_INST NUMBER;
BEGIN
SELECT INSTANCE_NUMBER INTO CUR_INST
FROM SYS.V$INSTANCE;
RETURN CUR_INST;
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
PROCEDURE ACTIVE_INSTANCES (INSTANCE_TABLE OUT INSTANCE_TABLE,
INSTANCE_COUNT OUT NUMBER)
AS
CURSOR C1 IS SELECT INST_NUMBER, INST_NAME FROM SYS.V$ACTIVE_INSTANCES
ORDER BY INST_NUMBER, INST_NAME;
INSTANCE_REC INSTANCE_RECORD;
BEGIN
INSTANCE_COUNT := 0;
FOR REC IN C1 LOOP
INSTANCE_COUNT :=INSTANCE_COUNT + 1;
INSTANCE_REC.INST_NUMBER := REC.INST_NUMBER;
INSTANCE_REC.INST_NAME := REC.INST_NAME;
INSTANCE_TABLE(INSTANCE_COUNT) := INSTANCE_REC;
END LOOP;
END;
PROCEDURE CANONICALIZE(NAME IN VARCHAR2,
CANON_NAME OUT VARCHAR2,
CANON_LEN IN BINARY_INTEGER) IS
NAME_LENGTH NUMBER;
DUMMY NUMBER := 1;
BEGIN
IF NAME IS NULL THEN
CANON_NAME := NULL;
RETURN;
END IF;
PSDCNAM(NAME, CANON_LEN, CANON_NAME);
END CANONICALIZE;
FUNCTION RAWS(BIT_OFFSET IN NUMBER) RETURN RAW IS
BEGIN
IF BIT_OFFSET = 1 THEN RETURN HEXTORAW('01');
ELSIF BIT_OFFSET = 2 THEN RETURN HEXTORAW('02');
ELSIF BIT_OFFSET = 3 THEN RETURN HEXTORAW('04');
ELSIF BIT_OFFSET = 4 THEN RETURN HEXTORAW('08');
ELSIF BIT_OFFSET = 5 THEN RETURN HEXTORAW('10');
ELSIF BIT_OFFSET = 6 THEN RETURN HEXTORAW('20');
ELSIF BIT_OFFSET = 7 THEN RETURN HEXTORAW('40');
ELSE RETURN HEXTORAW('80');
END IF;
END RAWS;
FUNCTION BIT(FLAG IN RAW,
BYTE IN NUMBER,
BIT_OFFSET IN NUMBER) RETURN BOOLEAN IS
BIT_FLAG RAW(1) := RAWS(BIT_OFFSET);
BEGIN
RETURN UTL_RAW.BIT_AND(UTL_RAW.SUBSTR(FLAG, BYTE, 1),
BIT_FLAG) = BIT_FLAG;
END BIT;
FUNCTION IS_BIT_SET(R IN RAW, N IN NUMBER)
RETURN NUMBER IS
BEGIN
IF (BIT(R, 4 - (TRUNC((N-1)/8,0)), MOD((N-1), 8) + 1)) THEN
RETURN (1);
ELSE
RETURN (0);
END IF;
END;
PROCEDURE GET_DEPENDENCY (
TYPE VARCHAR2,
SCHEMA VARCHAR2,
NAME VARCHAR2) IS
OBJ_ID NUMBER;
I NUMBER;
J NUMBER;
N1 NUMBER;
N2 NUMBER;
OID NUMBER;
ROID NUMBER;
LVL VARCHAR2(100);
SQ NUMBER;
CUR_USER VARCHAR2(100);
CUR_UID NUMBER;
TEMP_NUM NUMBER;
OTYP ALL_OBJECTS.OBJECT_TYPE%TYPE;
OWNR ALL_OBJECTS.OWNER%TYPE;
OBJNM ALL_OBJECTS.OBJECT_NAME%TYPE;
SUBNM ALL_OBJECTS.SUBOBJECT_NAME%TYPE;
TYPE DT2TB IS TABLE OF GET_DEP_DT2 INDEX BY BINARY_INTEGER;
TYPE DT1TB IS TABLE OF GET_DEP_DT1 INDEX BY BINARY_INTEGER;
TDT1TB DT1TB;
TDT1 GET_DEP_DT1;
TDT2 GET_DEP_DT2;
TDT2TB DT2TB;
TYPE CURTYP1 IS REF CURSOR;
C1 CURTYP1;
SPC VARCHAR2(1) := ' ';
CURSOR C2 IS
SELECT DISTINCT OBJECT_TYPE, OWNER, OBJECT_NAME, SUBOBJECT_NAME
FROM ALL_OBJECTS O, SYS.OBJAUTH$ A
WHERE OBJ_ID = OBJECT_ID (+)
AND ((OWNER = CUR_USER) OR (CUR_UID = 0) OR
(OBJ# = OBJECT_ID AND ((GRANTEE# = CUR_UID) OR (GRANTEE# = 1))));
BEGIN
SELECT OBJECT_ID INTO OBJ_ID FROM ALL_OBJECTS
WHERE OWNER = UPPER(SCHEMA)
AND OBJECT_NAME = UPPER(NAME)
AND OBJECT_TYPE = UPPER(TYPE)
AND SUBOBJECT_NAME IS NULL;
SELECT USER INTO CUR_USER FROM DUAL;
SELECT UID INTO CUR_UID FROM DUAL;
IF ((UPPER(SCHEMA) <> CUR_USER) AND (0 <> CUR_UID)) THEN
SELECT DISTINCT OBJ# INTO TEMP_NUM FROM OBJAUTH$ WHERE
OBJ# = OBJ_ID AND ((GRANTEE# = CUR_UID) OR (GRANTEE# = 1));
END IF;
TDT1TB(1) := GET_DEP_DT1(OBJ_ID, 0, '', 0);
I := 2;
SQ := 1;
OPEN C1 FOR
'select d_obj#, p_obj#, lpad('' '',3*level) from dependency$ start with p_obj# =  bj_id connect by prior d_obj# = p_obj# and prior property = 1 order siblings by d_obj#, p_obj#' USING OBJ_ID;
LOOP
FETCH C1 INTO OID, ROID, LVL;
EXIT WHEN C1%NOTFOUND;
TDT1TB(I) := GET_DEP_DT1(OID, ROID, LVL, SQ);
I := I + 1;
SQ := SQ + 1;
END LOOP;
CLOSE C1;
N1 := I - 1;
I := 1;
J := 1;
FOR I IN 1..N1 LOOP
TDT1 := TDT1TB(I);
OBJ_ID := TDT1.OBJECT_ID;
OPEN C2;
LOOP
FETCH C2 INTO OTYP, OWNR, OBJNM, SUBNM;
EXIT WHEN C2%NOTFOUND;
TDT2TB(J) := GET_DEP_DT2(TDT1.NEST_LEVEL,OTYP,OWNR,OBJNM,SUBNM,TDT1.SEQ#);
J := J + 1;
END LOOP;
CLOSE C2;
END LOOP;
DBMS_OUTPUT.ENABLE(1000000);
N1 := J - 1;
TDT2 := TDT2TB(1);
DBMS_OUTPUT.PUT_LINE('-');
DBMS_OUTPUT.PUT_LINE(
'DEPENDENCIES ON ' || TDT2.SCHEMA || '.' || TDT2.NAME
);
DBMS_OUTPUT.PUT_LINE(
'------------------------------------------------------------------'
);
FOR I IN 1..N1 LOOP
TDT2 := TDT2TB(I);
DBMS_OUTPUT.PUT_LINE('*' || TDT2.NESTED_LEVEL || TDT2.TYPE || ' ' ||
TDT2.SCHEMA || '.' || TDT2.NAME || '(' || SUBSTR(TDT2.SUBNAME,6,3) ||
')' );
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000, 'ORU-10013: ' ||
TYPE || ' ' || SCHEMA || '.' || NAME || ' was not found.');
END GET_DEPENDENCY;
PROCEDURE CREATE_ALTER_TYPE_ERROR_TABLE( SCHEMA_NAME IN VARCHAR2,
TABLE_NAME IN VARCHAR2) IS
SCHEMA VARCHAR2(30);
CURRENT_USER VARCHAR2(30);
PRIV_CNT NUMBER := 0;
TABLE_EXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT(TABLE_EXISTS, -955);
THESTRING VARCHAR2(4000);
BEGIN
SCHEMA := SCHEMA_NAME;
SELECT USER INTO CURRENT_USER FROM DUAL;
IF SCHEMA_NAME IS NULL THEN
SCHEMA := CURRENT_USER;
END IF;
IF (UPPER(SCHEMA) <> CURRENT_USER) THEN
SELECT COUNT(*) INTO PRIV_CNT FROM SYS.SYSAUTH$ S
WHERE GRANTEE# = (SELECT UID FROM DUAL) AND
PRIVILEGE# IN ( -40 ,
-41 );
IF (PRIV_CNT = 0) THEN
RAISE_APPLICATION_ERROR(-20000,
'You have insufficient privileges to create a table in ' || SCHEMA);
END IF;
END IF;
EXECUTE IMMEDIATE
'create table ' || DBMS_ASSERT.SIMPLE_SQL_NAME(SCHEMA) || '.' ||
DBMS_ASSERT.SIMPLE_SQL_NAME(TABLE_NAME) ||
' ( owner varchar(30), object_name varchar(30), sequence# number,
text_length number, error_text varchar(4000) ) ';
EXCEPTION
WHEN TABLE_EXISTS THEN
RAISE_APPLICATION_ERROR(-20000, 'ORU-00955 ' || ' The table ' ||
SCHEMA || '.' || TABLE_NAME || ' exists.');
END CREATE_ALTER_TYPE_ERROR_TABLE;
FUNCTION PSDOCS RETURN VARCHAR2;
PRAGMA INTERFACE (C, PSDOCS);
FUNCTION PSDOCU RETURN VARCHAR2;
PRAGMA INTERFACE (C, PSDOCU);
FUNCTION OLD_CURRENT_SCHEMA RETURN VARCHAR2 IS
BEGIN
RETURN PSDOCS;
END;
FUNCTION OLD_CURRENT_USER RETURN VARCHAR2 IS
BEGIN
RETURN PSDOCU;
END;
FUNCTION KESUTLSQLIDTOHASHICD(SQL_ID VARCHAR2)
RETURN NUMBER;
PRAGMA INTERFACE (C, KESUTLSQLIDTOHASHICD);
FUNCTION SQLID_TO_SQLHASH(SQL_ID VARCHAR2)
RETURN NUMBER IS
BEGIN
RETURN (KESUTLSQLIDTOHASHICD(SQL_ID));
END;
PROCEDURE PSD_VALIDATE(OWNER VARCHAR2, NAME VARCHAR2, NAMESPACE PLS_INTEGER);
PRAGMA INTERFACE (C, PSD_VALIDATE);
PROCEDURE VALIDATE(OBJECT_ID NUMBER) IS
OWNER VARCHAR2(4000);
OBJ_NAME VARCHAR2(4000);
OBJ_STATUS PLS_INTEGER;
NAMESPACE PLS_INTEGER;
LINK_NAME VARCHAR2(4000);
BEGIN
SELECT U.NAME, O.NAME, O.STATUS, O.NAMESPACE, O.LINKNAME
INTO OWNER, OBJ_NAME, OBJ_STATUS, NAMESPACE, LINK_NAME
FROM USER$ U, OBJ$ O
WHERE U.USER# = O.OWNER# AND O.OBJ# = OBJECT_ID;
IF (LINK_NAME IS NULL AND OBJ_STATUS IN (4, 5, 6)) THEN
PSD_VALIDATE(OWNER, OBJ_NAME, NAMESPACE);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
PROCEDURE VALIDATE(OWNER VARCHAR2, OBJNAME VARCHAR2, NAMESPACE NUMBER) IS
BEGIN
PSD_VALIDATE(OWNER, OBJNAME, NAMESPACE);
END;
FUNCTION ICD_GET_CPU_TIME RETURN BINARY_INTEGER;
PRAGMA INTERFACE (C, ICD_GET_CPU_TIME);
FUNCTION GET_CPU_TIME RETURN NUMBER IS
BEGIN
RETURN ICD_GET_CPU_TIME;
END;
FUNCTION ICD_GETSQLHASH(NAME IN VARCHAR2, HASH OUT RAW,
PRE10IHASH OUT NUMBER)
RETURN NUMBER;
PRAGMA INTERFACE (C, ICD_GETSQLHASH);
FUNCTION GET_SQL_HASH(NAME IN VARCHAR2, HASH OUT RAW,
PRE10IHASH OUT NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (ICD_GETSQLHASH(NAME, HASH, PRE10IHASH));
END;
PROCEDURE PSD_INVALIDATE(P_OBJECT_ID NUMBER,
PLSQL_SETTINGS VARCHAR2 DEFAULT NULL);
PRAGMA INTERFACE (C, PSD_INVALIDATE);
PROCEDURE INVALIDATE(P_OBJECT_ID NUMBER,
P_PLSQL_OBJECT_SETTINGS VARCHAR2 DEFAULT NULL,
P_OPTION_FLAGS PLS_INTEGER DEFAULT 0) IS
L_OWNER ALL_OBJECTS.OWNER%TYPE;
L_OBJNAME ALL_OBJECTS.OBJECT_NAME%TYPE;
L_OBJTYPE ALL_OBJECTS.OBJECT_TYPE%TYPE;
FUNCTION HAS_TABLE_DEPENDENTS(P_OBJECT_ID NUMBER) RETURN BOOLEAN IS
TYPE NUMTAB_T IS TABLE OF
PUBLIC_DEPENDENCY.OBJECT_ID%TYPE INDEX BY PLS_INTEGER;
L_DEPENDENTS NUMTAB_T;
L_OBJTYPE ALL_OBJECTS.OBJECT_TYPE%TYPE;
BEGIN
SELECT D_OBJ# BULK COLLECT INTO L_DEPENDENTS
FROM DEPENDENCY$ D
WHERE BITAND(D.PROPERTY, 1) = 1
CONNECT BY PRIOR D_OBJ# = P_OBJ#
START WITH P_OBJ# = P_OBJECT_ID;
FOR I IN L_DEPENDENTS.FIRST..L_DEPENDENTS.LAST LOOP
SELECT OBJECT_TYPE INTO L_OBJTYPE
FROM ALL_OBJECTS WHERE OBJECT_ID = L_DEPENDENTS(I);
IF (L_OBJTYPE = 'TABLE') THEN
RETURN TRUE;
END IF;
END LOOP;
RETURN FALSE;
END;
BEGIN
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
INTO L_OWNER, L_OBJNAME, L_OBJTYPE
FROM ALL_OBJECTS WHERE OBJECT_ID = P_OBJECT_ID;
IF (L_OBJTYPE NOT IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY',
'TRIGGER', 'TYPE', 'TYPE BODY', 'LIBRARY', 'VIEW',
'OPERATOR', 'SYNONYM', 'JAVA CLASS')) THEN
IF (BITAND(P_OPTION_FLAGS, INV_ERROR_ON_RESTRICTIONS) <> 0) THEN
RAISE INV_RESTRICTED_OBJECT;
END IF;
RETURN;
END IF;
IF ( (L_OWNER = 'SYS')
AND (L_OBJNAME IN ('STANDARD', 'DBMS_STANDARD', 'DBMS_UTILITY')))
THEN
IF (L_OBJTYPE = 'PACKAGE') THEN
IF (BITAND(P_OPTION_FLAGS, INV_ERROR_ON_RESTRICTIONS) <> 0) THEN
RAISE INV_RESTRICTED_OBJECT;
END IF;
RETURN;
ELSIF ( (L_OBJTYPE = 'PACKAGE BODY')
AND (L_OBJNAME = 'DBMS_UTILITY')) THEN
IF (BITAND(P_OPTION_FLAGS, INV_ERROR_ON_RESTRICTIONS) <> 0) THEN
RAISE INV_RESTRICTED_OBJECT;
END IF;
RETURN;
END IF;
END IF;
IF ((L_OBJTYPE = 'TYPE') AND HAS_TABLE_DEPENDENTS(P_OBJECT_ID)) THEN
IF (BITAND(P_OPTION_FLAGS, INV_ERROR_ON_RESTRICTIONS) <> 0) THEN
RAISE INV_RESTRICTED_OBJECT;
END IF;
RETURN;
END IF;
PSD_INVALIDATE(P_OBJECT_ID, P_PLSQL_OBJECT_SETTINGS);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE INV_NOT_EXIST_OR_NO_PRIV;
END;
END DBMS_UTILITY; |
|
楼主: bfc99
IP卡
狗仔卡
发表于 2015-7-28 16:00
显身卡
楼主