ユーザ各種設定情報検索 - lookworld @ 簡単メモ～

 

--ご使用の環境に合わせてください。（ディレクトリ、ファイル名）
--PC
spool c:\temp\user.lst
--UNIX
--spool ./user.lst

set echo on

/*----------------------------------------------*/
/*----- -----*/
/*----- 一般ユーザ各種設定情報検索 -----*/
/*----- -----*/
/*----------------------------------------------*/

select to_char(sysdate,'YY/MM/DD HH24:MI:SS') from dual;

--エクステント
col SEGMENT_NAME format a25;
col SEGMENT_TYPE format a10;
col TABLESPACE_NAME format a20;
col BYTES format 999999999999;
select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES,EXTENTS 
from user_segments
order by SEGMENT_TYPE,TABLESPACE_NAME,SEGMENT_NAME;

--フリークステント
col TABLESPACE_NAME format a20;
col BYTES format 999999999999;
col BLOCKS format 999999999999;
select TABLESPACE_NAME,sum(BYTES),sum(BLOCKS) from user_free_space
group by TABLESPACE_NAME
order by TABLESPACE_NAME,sum(BYTES);

--ユーザセグメント合計情報
col SUM(BYTES) format 999999999999999;
select SUM(BYTES) from user_segments;

--ユーザセグメント合計情報1
col SUM(BYTES) format 999999999999999;
select SEGMENT_NAME,SUM(BYTES) from user_segments
group by SEGMENT_NAME;

--ユーザオブジェクト情報
col OBJECT_NAME format a20;
col OBJECT_TYPE format a15;
select OBJECT_NAME,OBJECT_TYPE from user_objects
order by OBJECT_TYPE;

--ユーザオブジェクト情報1
--ステータスがINVALIDの物
col OBJECT_NAME format a30;
col OBJECT_TYPE format a15;
select OBJECT_NAME,OBJECT_TYPE from user_objects
where status like 'IN%'
order by OBJECT_TYPE,OBJECT_NAME;

--ユーザオブジェクト情報2
col OBJECT_NAME format a30;
col OBJECT_TYPE format a15;
select OBJECT_NAME,OBJECT_TYPE,status from user_objects
where OBJECT_type like 'PACKAGE BODY' or OBJECT_type like 'PACKAGE' or OBJECT_type like 'PROCEDURE' or OBJECT_type like 'FUNCTION'
order by status,OBJECT_NAME,OBJECT_TYPE;

--ユーザオブジェクト情報3
--再コンパイル文を作成
select 
'alter ' || decode( OBJECT_TYPE,'PACKAGE BODY','PACKAGE',OBJECT_TYPE) || 
' ' || OBJECT_NAME || ' ' || decode( OBJECT_TYPE,'PACKAGE BODY','compile body;','compile;')
from user_objects
where status like 'IN%';

--ユーザシノニム
col SYNONYM_NAME format a15;
col TABLE_OWNER format a15;
col TABLE_NAME format a15;
select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from user_synonyms;

--ユーザテーブル情報
col TABLE_NAME format a20;
col TABLESPACE_NAME format a15;
col INITIAL_EXTENT format 99999999999999;
col NEXT_EXTENT format 99999999999999;
col MIN_EXTENTS format 9999999999;
col MAX_EXTENTS format 9999999999;
select TABLE_NAME,TABLESPACE_NAME,INITIAL_EXTENT,NEXT_EXTENT,
MIN_EXTENTS,MAX_EXTENTS
from user_tables
order by TABLE_NAME,TABLESPACE_NAME;

--ユーザテーブル情報1
col TABLE_NAME format a20;
col TABLESPACE_NAME format a15;
col INITIAL_EXTENT format 99999999999999;
col NEXT_EXTENT format 99999999999999;
col MIN_EXTENTS format 9999999999;
col MAX_EXTENTS format 9999999999;
select 
TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS
AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
from user_tables
order by TABLE_NAME,TABLESPACE_NAME;

--ユーザインデックス1
col INDEX_NAME format a20;
col TABLE_NAME format a20;
col TABLESPACE_NAME format a15;
select INDEX_NAME,UNIQUENESS,TABLE_NAME,TABLESPACE_NAME,
INITIAL_EXTENT,NEXT_EXTENT 
from user_indexes 
order by TABLE_NAME,INDEX_NAME;

--ユーザインデックス2
col INDEX_NAME format a20;
col TABLE_NAME format a20;
col COLUMN_NAME format a20;
select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION,TABLE_NAME
from user_ind_columns
order by TABLE_NAME,INDEX_NAME,COLUMN_POSITION;

--制約情報
col CONSTRAINT_NAME format a30;
col TABLE_NAME format a20;
select CONSTRAINT_NAME,CONSTRAINT_TYPE, TABLE_NAME from user_constraints;

--順序
select * from seq;

--ビュー
select VIEW_NAME,VIEW_TYPE_OWNER from user_views;

--付与されているロール
select * from user_role_privs;

--付与されているシステム権限
select * from user_sys_privs;

--付与されているオブジェクト権限
select * from user_tab_privs_recd;

spool off

set echo on