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.

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.sql—Taking 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!

Enabling Dynamic SQL with DBMS_SQL

This package enables you to perform several kinds of operations dynamically (at runtime):

• Any DDL (Data Definition Language) statement, such as CREATE TABLE, CREATE SEQUENCE, and so on.
• Any SQL or DML (Data Manipulation Language) statement, such as SELECT, INSERT, UPDATE, and DELETE.
• Any other statement recognized by the server, such as EXPLAIN PLAN.
• Any anonymous PL/SQL block.

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.sql—Universal 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.sql—Run DDL and DML Statements Dynamically!

DECLARE
  numrows NUMBER;
  status NUMERIC;
BEGIN
  DBMS_OUTPUT.enable;
  -- first let's create a table -- DYNAMICALLY!

                                                                    continues

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. !