tom's script

psufnxk2000發表於2012-02-08
--show_space    

CREATE OR REPLACE PROCEDURE show_space (
p_segname IN VARCHAR2,
p_owner IN VARCHAR2 DEFAULT 'USER',
p_type IN VARCHAR2 DEFAULT 'TABLE',
p_partition IN VARCHAR2 DEFAULT NULL
)
AUTHID CURRENT_USER
AS
l_free_blks NUMBER;
l_total_blocks NUMBER;
l_total_bytes NUMBER;
l_unused_blocks NUMBER;
l_unused_bytes NUMBER;
l_lastusedextfileid NUMBER;
l_lastusedextblockid NUMBER;
l_last_used_block NUMBER;
PROCEDURE p (p_label IN VARCHAR2, p_num IN NUMBER)
IS
BEGIN
DBMS_OUTPUT.put_line (RPAD (p_label, 40, '.') || p_num);
END;
BEGIN
FOR x IN (SELECT tablespace_name
FROM user_tablespaces
WHERE tablespace_name =
(SELECT tablespace_name
FROM user_segments
WHERE segment_type = p_type
AND segment_name = p_segname
AND segment_space_management <> 'AUTO'))
LOOP
DBMS_SPACE.free_blocks (segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks
);
END LOOP;
DBMS_SPACE.unused_space (segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
last_used_extent_file_id => l_lastusedextfileid,
last_used_extent_block_id => l_lastusedextblockid,
last_used_block => l_last_used_block
);
p ('Free Blocks', l_free_blks);
p ('Total Blocks', l_total_blocks);
p ('Total Bytes', l_total_bytes);
p ('Total MBytes', TRUNC (l_total_bytes / 1024 / 1024));
p ('Unused Blocks', l_unused_blocks);
p ('Unused Bytes', l_unused_bytes);
p ('Last Used Ext FileId', l_lastusedextfileid);
p ('Last Used Ext BlockId', l_lastusedextblockid);
p ('Last Used Block', l_last_used_block);
END;
/


--另一種方式顯示錶中內容
--使用時 exec print_table('select * from a1');

create or replace procedure print_table( p_query in varchar2 )
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
begin
    execute immediate
    'alter session set
        nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column
        (l_theCursor, i, l_columnValue, 4000);
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value
            ( l_theCursor, i, l_columnValue );
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
              || ': ' ||
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
    execute immediate
        'alter session set nls_date_format=''dd-MON-rr'' ';
exception
    when others then
      execute immediate
          'alter session set nls_date_format=''dd-MON-rr'' ';
      raise;
end;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-715846/，如需轉載，請註明出處，否則將追究法律責任。

相關文章