Page 291
FETCH get_objs INTO get_objs_rec; EXIT WHEN get_objs%NOTFOUND; DBMS_SPACE.unused_space(OWNER, get_objs_rec.object_name, get_objs_rec.object_type, tblocks, tbytes, ublocks, ubytes, luefil, lueblk, lblock); totblk := totblk + tblocks; -- accumulate total blocks totbyt := totbyt + tbytes; -- accumulate total bytes totubk := totubk + ublocks; -- accumulate unused blocks totuby := totuby + ubytes; -- accumulate unused bytes DBMS_OUTPUT.put_line(get_objs_rec.object_type || ` ` || get_objs_rec.object_name || TB || TO_CHAR(tblocks) || TB || TO_CHAR(tbytes) || TB || TO_CHAR(ublocks) || TB || TO_CHAR(ubytes) || TB || TO_CHAR(tblocks - ublocks) || TB || TO_CHAR(tbytes - ubytes)); END LOOP; -- space information CLOSE get_objs; DBMS_OUTPUT.put_line(`Total Blocks: ` || TO_CHAR(totblk) || `, Unused: ` || TO_CHAR(totubk) || `, Used: ` || TO_CHAR(totblk - totubk)); DBMS_OUTPUT.put_line(`Total Bytes : ` || TO_CHAR(totbyt) || `, Unused: ` || TO_CHAR(totuby) || `, Used: ` || TO_CHAR(totbyt - totuby)); EXCEPTION WHEN OTHERS THEN BEGIN status := SQLCODE; DBMS_OUTPUT.put_line(SQLERRM(status)); IF (get_objs%ISOPEN) THEN CLOSE get_objs; END IF; EXCEPTION WHEN OTHERS THEN NULL; -- don't care END; END; /
When I run this as user SCOTT, for example, I get:
------------ TOTAL BLKS/BYTES UNUSED BLKS/BYTES USED BLKS/BYTES TABLE BLICK 5 10240 3 6144 2 4096 TABLE BONUS 5 10240 4 8192 1 2048 TABLE DEPT 5 10240 3 6144 2 4096 TABLE EMP 5 10240 3 6144 2 4096 INDEX PK_DEPT 5 10240 3 6144 2 4096 INDEX PK_EMP 5 10240 3 6144 2 4096 TABLE SALGRADE 5 10240 3 6144 2 4096 Total Blocks: 35, Unused: 22, Used: 13 Total Bytes : 71680, Unused: 45056, Used: 26624 PL/SQL procedure successfully completed.
Page 292
This is a nice, concise format.
Want to check the freelist blocks for each segment in your schema? Try Listing 12.7 below.
Listing 12.7 freelist.sqlTaking a Peek at Segment Freelists
DECLARE -- cursors CURSOR get_objs IS SELECT object_name, object_type FROM user_objects WHERE object_type = ANY (`TABLE', `INDEX', `CLUSTER'); -- constants TB CONSTANT VARCHAR2(2) := CHR(9); -- tab character OWNER CONSTANT VARCHAR2(30) := USER; -- schema owner INST CONSTANT NUMBER := 0; -- freelist instance -- variables get_objs_rec get_objs%ROWTYPE; status NUMERIC; freeblks NUMBER; -- number of freelist blocks tfree NUMBER := 0; -- total freelist blocks BEGIN DBMS_OUTPUT.enable; OPEN get_objs; LOOP FETCH get_objs INTO get_objs_rec; EXIT WHEN get_objs%NOTFOUND; DBMS_SPACE.free_blocks(OWNER, get_objs_rec.object_name, get_objs_rec.object_type, INST, freeblks); tfree := tfree + freeblks; DBMS_OUTPUT.put_line(get_objs_rec.object_type || ` ` || get_objs_rec.object_name || TB || TO_CHAR(freeblks)); END LOOP; -- freelist information CLOSE get_objs; DBMS_OUTPUT.put_line(`Total Freelist Blocks: ` || TO_CHAR(tfree)); END; /
Running this, I get:
TABLE BLICK 1 TABLE BONUS 0 TABLE DEPT 1 TABLE EMP 1 INDEX PK_DEPT 0 INDEX PK_EMP 0 TABLE SALGRADE 1 Total Freelist Blocks: 4 PL/SQL procedure successfully completed.
I've heard a lot of DBAs asking after this capability, and now they have it!
Page 293
This package enables you to perform several kinds of operations dynamically (at runtime):
By "dynamic," I mean that we can build a string containing any valid statement, and execute this statement as if it were hard-coded in our program.
Let's try a simple example to perform any DDL or DML (see Listing 12.8).
Listing 12.8 uniddl.sqlUniversal DDL/DML Executor
CREATE OR REPLACE PROCEDURE uniddl( Pstmt IN VARCHAR2, Prows OUT NUMBER, Pstatus OUT NUMERIC) IS uni_c INTEGER := DBMS_SQL.open_cursor; BEGIN -- universal DDL execution Pstatus := 0; DBMS_SQL.parse(uni_c, Pstmt, DBMS_SQL.NATIVE); -- parse the statement Prows := DBMS_SQL.execute(uni_c); -- execute it DBMS_SQL.close_cursor(uni_c); EXCEPTION WHEN OTHERS THEN Pstatus := SQLCODE; DBMS_SQL.close_cursor(uni_c); END uniddl; /
The open_cursor statement creates a cursor object (a chunk of memory is allocated) in the SGA and returns an integer handle to it. Now, a dynamic cursor is a completely different animal from normal implicit and explicit cursors. For one thing, dynamic cursors are easily reusable; they need not be closed to execute a new statement, only reparsed. It's not an error to close an already closed cursor, provided it was opened in the first case.
Let's try some statements to observe what happens (see Listing 12.9).
Listing 12.9 testddl.sqlRun DDL and DML Statements Dynamically!
DECLARE numrows NUMBER; status NUMERIC; BEGIN DBMS_OUTPUT.enable; -- first let's create a table -- DYNAMICALLY!
continues
Page 294
Listing 12.9 Continued
uniddl(`CREATE TABLE BLICK (BLUCK NUMBER)', numrows, status); DBMS_OUTPUT.put_line(`CREATE TABLE BLICK: ` || SQLERRM(status)); -- next, let's throw some rows into it -- DYNAMICALLY! uniddl(`INSERT INTO BLICK VALUES (1)', numrows, status); DBMS_OUTPUT.put_line(`INSERT INTO BLICK: ` || TO_CHAR(numrows) || ` rows ` || SQLERRM(status)); uniddl(`INSERT INTO BLICK VALUES (2)', numrows, status); DBMS_OUTPUT.put_line(`INSERT INTO BLICK: ` || TO_CHAR(numrows) || ` rows ` || SQLERRM(status)); END; / SELECT * FROM BLICK -- examine the contents of our new table / DECLARE numrows NUMBER; status NUMERIC; BEGIN -- now, operate on the rows -- DYNAMICALLY! uniddl(`UPDATE BLICK SET BLUCK = 0', numrows, status); DBMS_OUTPUT.put_line(`UPDATE BLICK: ` || TO_CHAR(numrows) || ` rows ` || SQLERRM(status)); END; / SELECT * FROM BLICK -- see that we really updated the rows / DECLARE numrows NUMBER; status NUMERIC; BEGIN -- finally, trash the table -- DYNAMICALLY! uniddl(`DROP TABLE BLICK', numrows, status); DBMS_OUTPUT.put_line(`DROP BLICK: ` || SQLERRM(status)); END; / SELECT * FROM BLICK -- see that it's really gone /
I broke this test up into separate blocks so we could run an SQL directly to prove that it really did the job. From each piece we get:
From the first block:
CREATE TABLE BLICK: ORA-0000: normal, successful completion INSERT INTO BLICK: 1 rows ORA-0000: normal, successful completion INSERT INTO BLICK: 1 rows ORA-0000: normal, successful completion PL/SQL procedure successfully completed. !