Oracle - MemoSoup

Oracle: get the tablespace structure

The easiest method to get the tablespace structure is to use the dbms_metadata package:

SET linesize 200 LONG 50000 pagesize 5000 SELECT dbms_metadata.get_ddl(’TABLESPACE’,tablespace_name ) FROM dba_tablespaces WHERE tablespace_name IN ( ‘&your_tablespace’ );

Additionally, exp/imp (or expdp/impdp) utilities could be used. They could generate the DDL statements for all objects in the database, which could be [...]

Oracle: buffer overflow (ORU-10027)

Sometimes it’s necessary to show the output of the PL/SQL script. Usually the dbms_output package is used:

SET serveroutput ON DECLARE i NUMBER ; BEGIN i:=0 ; WHILE i< 100000 LOOP i:=i+1 ; dbms_output.put_line(’This is just test line #’ || TO_CHAR(i) ) ; END LOOP ; END; /

After several thousands lines the [...]

Oracle: how to clone profiles

Here is the simple script for cloning the Oracle profiles.

SET serveroutput ON DECLARE CURSOR c_profiles IS SELECT PROFILE, RESOURCE_NAME, LIMIT FROM dba_profiles ORDER BY PROFILE, resource_name; s_PROFILE dba_profiles.PROFILE%TYPE ; s_prev_PROFILE dba_profiles.PROFILE%TYPE ; s_RESOURCE_NAME [...]

Oracle: quotes and quote operator

There are several methods to put the quote into the string.

The first (and very traditional) one: use 2 quotes.

SELECT ‘This ” is quote’ FROM dual;

If there are more than one quote, it’s difficult to read and write such strings

The second: use chr(39)

SELECT ‘This ‘ || CHR(39) || ‘ is quote’ [...]

Oracle: problem relinking binaries on Solaris

I’ve got the following message relinking the binaries on Solaris:

ld: fatal: dlopen() of support library (libmakestate.so.1) failed with error:

ld.so.1: /usr/ccs/bin/sparcv9/ld: fatal: /usr/lib/libmakestate.so.1: wrong ELF class: ELFCLASS32

The problem was caused by missing 64-bit library libmakestate.so.1 in /usr/lib.

The normal library should look like the following:

[#] file /usr/lib/sparcv9/libmakestate.so.1 libmakestate.so.1: ELF 64-bit MSB dynamic [...]

Oracle: eval function

The function takes some expression as the argument and executes it, returning output in the varchar string

CREATE OR REPLACE FUNCTION eval (expr VARCHAR2) RETURN VARCHAR2 AS ret VARCHAR2(4000); BEGIN EXECUTE IMMEDIATE ‘begin :result := ‘ || expr || ‘; end;’ USING OUT ret; RETURN ret; END; /

The discussion and examples [...]

Oracle: list of the system events

This piece of code show the list of events, set in the Oracle database

SET serveroutput ON DECLARE event_level NUMBER; BEGIN dbms_output.enable(20000) ; FOR i IN 10000..33999 LOOP sys.dbms_system.read_ev(i,event_level); IF (event_level > 0) THEN dbms_output.put_line(’Event ‘||TO_CHAR(i)||’ set at level ‘|| TO_CHAR(event_level));
END IF;
END LOOP;
END;

Oracle: list of the running transactions

This query shows SID, username and start time of the running transaction. In addition, number of the used blocks is shown.

SELECT a.sid, a.status, a.username, b.xidusn, b.used_urec, b.used_ublk, b.START_TIME FROM v$session a, v$transaction b WHERE a.saddr = b.ses_addr ORDER BY START_TIME DESC;

Oracle: internal sql types

Here is the list of the Oracle SQL types and their internal codes:

SELECT t.typecode,o.name FROM sys.TYPE$ t, sys.obj$ o WHERE BITAND (t.properties, 16) = 16 AND t.toid = o.oid$ ORDER BY t.typecode

‘

Oracle: wait statisics by block class

This query helps to get information about the wait statistics (in fact, this query is contained in GV_$WAITSTAT)

SELECT inst_id, DECODE (indx, 1,’data block’, 2,’sort block’, 3,’save undo block’, 4,’segment header’, 5,’save undo header’, 6,’free list’, 7,’extent map’, 8,’1st level bmb’, 9,’2nd level bmb’, 10,’3rd level bmb’, 11,’bitmap block’, 12,’bitmap index block’, 13,’file header block’, [...]

Meta

Tags