▣ 관리자 입장에서 필요한 PL/SQl 내공
▶ 개발자가 만든 pl/sql 스크립트 이해할 것
▶ Built-in Package 분석 => 실행
=> package 검색(DBMS_??) => 함수, 프로시져, Argument 분석
▶ 튜닝용 패키지 설치 및 운영
- 오라클에서 제공하는 : statspack
- 고수들이 제공하는
- 개인적으로 만든
▶ 작업 및 튜닝을 위한 업무 자동화
▶ JobScheduler를 운영 by pl/sql
▣ Analyze Object
SQL> begin
2 dbms_ddl.analyze_object('TABLE','HR','EMP','COMPUTE');
3 end;
4 /
PL/SQL procedure successfully completed.
S SYS> select table_name, num_rows from dba_tables
where owner='HR' and table_name='EMP';
TABLE_NAME NUM_ROWS
------------------------------ ----------
EMP 14
S SYS> truncate table hr.emp;
Table truncated.
SQL> begin
2 dbms_ddl.analyze_object('TABLE','HR','EMP','COMPUTE');
3 end;
4 /
PL/SQL procedure successfully completed.
S SYS> select table_name,num_rows from dba_tables where owner='HR' and table_name='EMP';
TABLE_NAME NUM_ROWS
------------------------------ ----------
EMP 0
▣ Script
S SYS> @fp
Enter value for key: DDL
OBJECT_NAME
------------------------------
OWM_DDL_PKG
WM_DDL_UTIL
LCR$_DDL_RECORD
DBMS_DDL_INTERNAL
LTDDL
DBMS_DDL
NameFromLastDDL
7 rows selected.
S SYS> spool DBMS_DDL
S SYS> desc DBMS_DDL
S SYS> spool off
S SYS> !vi DBMS_DDL.lst
PROCEDURE ALTER_COMPILE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TYPE VARCHAR2 IN
SCHEMA VARCHAR2 IN
NAME VARCHAR2 IN
REUSE_SETTINGS BOOLEAN IN DEFAULT
PROCEDURE ALTER_TABLE_NOT_REFERENCEABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN
TABLE_SCHEMA VARCHAR2 IN DEFAULT
AFFECTED_SCHEMA VARCHAR2 IN DEFAULT
PROCEDURE ALTER_TABLE_REFERENCEABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN
TABLE_SCHEMA VARCHAR2 IN DEFAULT
AFFECTED_SCHEMA VARCHAR2 IN DEFAULT
PROCEDURE ANALYZE_OBJECT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TYPE VARCHAR2 IN
SCHEMA VARCHAR2 IN
NAME VARCHAR2 IN
METHOD VARCHAR2 IN
ESTIMATE_ROWS NUMBER IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT
PARTNAME VARCHAR2 IN DEFAULT
PROCEDURE CREATE_WRAPPED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DDL VARCHAR2 IN
PROCEDURE CREATE_WRAPPED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DDL TABLE OF VARCHAR2(256) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN
PROCEDURE CREATE_WRAPPED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DDL TABLE OF VARCHAR2(32767) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN
FUNCTION IS_TRIGGER_FIRE_ONCE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TRIG_OWNER VARCHAR2 IN
TRIG_NAME VARCHAR2 IN
FUNCTION IS_TRIGGER_FIRE_ONCE_INTERNAL RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TRIG_OWNER VARCHAR2 IN
TRIG_NAME VARCHAR2 IN
PROCEDURE SET_TRIGGER_FIRING_PROPERTY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TRIG_OWNER VARCHAR2 IN
TRIG_NAME VARCHAR2 IN
FIRE_ONCE BOOLEAN IN
FUNCTION WRAP RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DDL VARCHAR2 IN
FUNCTION WRAP RETURNS TABLE OF VARCHAR2(256)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DDL TABLE OF VARCHAR2(256) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN
FUNCTION WRAP RETURNS TABLE OF VARCHAR2(32767)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DDL TABLE OF VARCHAR2(32767) IN
LB BINARY_INTEGER IN
UB BINARY_INTEGER IN
S SYS> begin
2 dbms_ddl.ANALYZE_OBJECT(?,'HR','EMP',?); => 들어가는 ? 는 DBA_SOURCE에서 검색한다.
S SYS> @fv
Enter value for key: DBA_%SOUR%
VIEW_NAME
------------------------------
DBA_SOURCE_TABLES
DBA_SOURCE
_DBA_APPLY_SOURCE_SCHEMA
_DBA_APPLY_SOURCE_OBJ
DBA_TSM_SOURCE
DBA_HIST_RESOURCE_LIMIT
DBA_RESOURCE_INCARNATIONS
7 rows selected.
S SYS> desc DBA_SOURCE
Name Null? Type
----------------------------------------------------------- -------- ----------------------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
S SYS> select count(*) from dba_source where name like '%DBMS_DDL%';
COUNT(*)
----------
288
S SYS> select count(*) from dba_source where name like '%ANALYZE_OBJECT%';
COUNT(*)
----------
0 => 카운트가 놓게 나온 것을 다시 검색한다
검색방법
select text from dba_source where name like '%DBMS_DDL%' and type='PACKAGE' order by line;
▣ 참고
▶ set serveroutput on/off 와 같은 역할을 하는 pl/sql문
S SYS> exec dbms_output.disable;
PL/SQL procedure successfully completed.
S SYS> exec dbms_output.put_line('aa');
PL/SQL procedure successfully completed.
S SYS> exec dbms_output.enable;
PL/SQL procedure successfully completed.
S SYS> exec dbms_output.put_line('aa');
aa
PL/SQL procedure successfully completed.
▶ 이어 붙이기 (띄어쓰기 안되게 하는 방법)
S SYS> r
1 begin
2 for i in 1..10 loop
3 dbms_output.put(i);
4 end loop;
5 dbms_output.new_line;
6* end;
12345678910
PL/SQL procedure successfully completed.
댓글 없음:
댓글 쓰기