Oracle Utility 1/3

지난 1시간동안 실행된 SQL 쿼리 보기 

/* 
  SQL QUERY statements which were excuted for last 1 hours,
  and
  - module name is x
  - schema name is y
*/
column sql_fulltext format a50
alter session set nls_date_format='mm/dd HH24:MI:SS';

accept userid prompt     'Schema Name (same as the db user id): '
accept modulename prompt 'Module Name (You can find it from v$session): '

select t.last_active_time lastRun, t.EXECUTIONS times, t.sql_fulltext
from v$sqlarea t
where t.parsing_schema_name=upper('&userid')
and t.module like '&modulename%'      
and t.last_active_time >= (sysdate-1/24)
order by t.last_active_time desc;

특정 유저 세션의 사용시간과 사용 프로그램 보기 

/* Simple query for BIZFLOW seesion using time.

LAST_CALL_ET column means 
the session's elapse time in seconds since the session has become active or inactive.

*/

column username format a10
column program  format a25
column module   format a25
alter session set nls_date_format='mm/dd HH24:MI:SS';

accept userid prompt 'USER ID: '

select s.sid, s.LOGON_TIME, s.LAST_CALL_ET "Last_call_elapse_time", s.module
from v$session s
where s.username=upper('&userid')
order by s.module, last_call_et;

한 Schema 내의 모든 Table들의 row count 출력 

accept schemaName prompt 'SCHEMA NAME: '

exec dbms_utility.analyze_schema(upper('&schemaName'),'COMPUTE');

select table_name, num_rows, avg_space, avg_row_len from user_tables;



/*   for DB SIZE increase comparison */
/*
create table data_0 as select table_name, num_rows, avg_space, avg_row_len from user_tables;

select a.table_name, a.num_rows before, b.num_rows after
from data_0 a, data_1 b where a.table_name=b.table_name
and a.num_rows <> b.num_rows;
*/

Export dump, Import dump for Oracle10g

/* expdp (export dump), impdp (import dump) 
   It's fast and has more advanced options such as filtering, sqlfile.
   
   by Jeyong Park
   It's for Oracle 10.
*/
-- Create a directory for export dump file first!.
CREATE DIRECTORY dump_dir AS '/roam/home/jypark/test' ;
GRANT read, write ON DIRECTORY dump_dir TO scott ;


-- Then from now on, execute commands on command console

-- Export all scott's objects
$ expdp scott/tiger directory=dump_dir dumpfile=backup.dmp job_name=test_exp

-- Export with table filters.
$ expdp scott/tiger directory=dump_dir dumpfile=backup.dmp job_name=test_exp 
include=TABLE:"IN ('EMPLOYEES', 'DEPT')"

$ expdp scott/tiger directory=dump_dir dumpfile=backup.dmp job_name=test_exp 
include=TABLE:"LIKE 'DEPT%'"

-- Export with filter. procedures and functions only
$ expdp scott/tiger directory=dump_dir dumpfile=backup.dmp job_name=test_exp include=PROCEDURE, FUNCTION

-- You can import them in a sql file such as..
$ expdp scott/tiger directory=dump_dir dumpfile=backup.dmp sqlfile=procedures.sql

Select한 데이터를 세로가 아닌 가로쓰기로 보기편하게 보여주는 SQL.

/*    Source: Asktom.oracle.com
      http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1035431863958

   ???? row?? ??? ?? ??? ????..
   example: exec print_table( 'select * from v$database')
*/

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;
/

테이블과 인덱스를 다른 tablespace로 옮기는 스크립트

/*
**  Table을 다른 테이블 스페이스로 옮기기 위한 스크립트를 만들어주는 스크립트...  <<박제용>>
**
**  사용법 : 1) DBA 권한으로 로그인한다.
**           2) 아래의 SQL 스크립트를 실행하여 생성된 moveall.sql 을 적절히 수정해서 사용
**
*/
set echo off

column order_col1 noprint
column order_col2 noprint

set heading off
set verify off
set feedback off
set echo off

spool mvoeall.sql

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 ' ) || chr(10) ||
       ' tablespace &1 ' || chr(10) ||
       ' storage ( initial ' || initial_extent || ' next ' || next_extent || chr(10) ||
       ' minextents ' || min_extents || ' maxextents ' || max_extents || chr(10) ||
       ' pctincrease ' || pct_increase || ' freelists ' || freelists || ');'
from   user_segments, 
       (select table_name, index_name from user_indexes )
where   segment_type in ( 'TABLE', 'INDEX' )
and     segment_name = index_name (+)
order by 1, 2
/

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 tmp.sql, modify as needed and run it

테이블의 특정 컬럼에 중복된 값을 찾는 SQL.

/*
**=============================================
** 중복된 값 있는지 찾기
**=============================================
**  Usage   :  @중복찾기.sql [테이블명]  [중복을조사할컬럼명]
**
**  Warning : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째 값부터 출력됨. <<Park Je Yong>>
*/

select * from &1 A
where rowid > 
 (SELECT min(rowid) FROM &1 B
  WHERE B.&2 = A.&2)
order by &2;

PK와 FK간의 연관관계를 찾아 보여주는 SQL

/*　　 
**　　 
**　 사용법　　 　 :> @Show_Positions　 Parent_Table　 Child_Table　　 
**　 Description　 :　 Shows Primary And Foreign Key Positions　　 
**　　 
**　 WARNING　　 :　 이 문장은 해당 Table의 Constraint생성시 Naming　　　 
**　　　　　　　　　 Convention을 따른 경우에 적용되도록 되어 있다.　　 
**
*/
SET VERIFY OFF　　 
CLEAR BREAK　　 
BREAK ON CONSTRAINT_NAME ON TABLES
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
       SUBSTR(TABLE_NAME,1,15) TABLES,
       SUBSTR(COLUMN_NAME,1,15) COL_NAME,
       SUBSTR(POSITION,1,3)    POSITION,
       SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&1') AND CONSTRAINT_NAME LIKE 'PK%'
UNION
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
       SUBSTR(TABLE_NAME,1,15) TABLES, 
       SUBSTR(COLUMN_NAME,1,25) COL_NAME,
       SUBSTR(POSITION,1,3) POSITION,
       SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&2') AND CONSTRAINT_NAME LIKE 'FK%'
ORDER BY 1 DESC,4 ASC;