print_table --纵向显示一行
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
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( '-----------------' ); --注意如果输出的行比较多的话,要加大dbms_output.enable(值)
end loop;
exception
when others then
dbms_sql.close_cursor( l_theCursor );
RAISE;
end;
/
grant execute on print_table to public;
--该脚本是实现横向改成纵向显示
例如:
一行记录显示如下:
ADMIN_MEMBER_ID
: dealexpress
VIEW_NAME
: Deal Express
BUSINESS_TYPE
: 5
FIRST_NAME
: Tim
LAST_NAME
: Horton
JOB_TITLE
:
PROVINCE
: Wisconsin
COUNTRY
: US
PHONE_COUNTRY
: 1920
PHONE_AREA
: 623
PHONE_NUMBER
: 9528
FAX_COUNTRY
: 1920
FAX_AREA
: 623
FAX_NUMBER
: 9528
MOBILE_NO
:
ZIP
: 53925
ADDRESS
: 215 Robbins Rd
CITY
: Columbus
#######################################################
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;
#######################################################################
moveall.sql
--move表及rebuild 索引
set echo off
column order_col1 noprint
column order_col2 noprint
set heading off
set verify off
set feedback off
set echo off
spool move_build_tpcrm.sql
!date
select decode( segment_type, 'TABLE',
segment_name, table_name ) order_col1,
decode( segment_type, 'TABLE', 1, 2 ) order_col2,
'alter ' || segment_type || ' ' || segment_name ||
decode( segment_type, 'TABLE', ' move; ', ' rebuild; ' )
from user_segments,
(select table_name, index_name from user_indexes )
where segment_type in ( 'TABLE', 'INDEX' )
and segment_name = index_name (+)
and tablespace_name in
(自己选择的表空间)
order by 1, 2
/
!date
spool off
set heading on
set verify on
set feedback on
set echo on
REM UNCOMMENT TO AUTO RUN the generated commands
REM ELSE edit move_build.sql, modify as needed and run it
