1)触发器的基本操作
Select object_name From user_objects Where object_type='TRIGGER'; --所有触发器
CCbMS:/home/ap/ecds/> urce where name='T_ECD_SYS_UPDATE';" <
TEXT
TRIGGER t_ecd_sys_update
AFTER UPDATE OF plat_date ON t_plat_para
for each row
begin
update t_ecd_sys set wk_date=TO_CHAR(sysdate,'YYYYMMDD'),nxt_date=TO_CHAR(sysdate+1,'YYYYMMDD');
end;
6 rows selected.
select * from user_triggers where table_name='表名';(查询特定表下所有的触发器)
alter trigger 触发器名称 disable ;(禁止触发器)
alter trigger 触发器名称 enable ;(激活触发器)
alter table 表名 disable|enable all trigger (禁止或激活表上所有触发器)
alter trigger 触发器 compile;(编译触发器)
drop trigger 触发器;
show error;(查询编译错误)
2)存储过程常见操作
Select object_name From user_objects Where object_type='PROCEDURE'; --所有存储过程
编译存储过程:
alter procedure 存储过程名 compile;
show error;(查询编译错误)
CCbMS:/home/ap/ecds/test/simala/> sql "Select object_name From user_objects Where object_type='PROCEDURE';"
OBJECT_NAME
PROC_GETLASTMONTHNUMBALANCE
PROC_ECD_CHECKFUND_TMP
PROC_BRCH_STAT
PROC_ECD_ODAYBOOK_REPORT
PROC_ECD_DAYBOOK_REPORT
PROC_ECD_CHECKFUND_REPORT
PROC_ECD_CHECKDETAIL_REPORT
PROC_ECD_BILLMAIN_REPORT
PROC_GETPAGESEQ
PROC_GETNUMBALANCE
PROC_INSERTLOG
PROC_GETBALANCE
PROC_BRCH_LEVEL
PROC_ECD_DISCOUNT_MB
PROC_ECD_STOCKS_REPORT
PROC_ECD_CHECKDETAIL_TMP
PROC_ECD_DAYBOOK_TMP
PROC_ECD_ODAYBOOK_TMP
PROC_REPORT_CREPY
19 rows selected.
select test from user_source where name='过程名';
CCbMS:/home/ap/ecds/test/simala/> sql "desc user_source;"
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
3)视图和表
Select object_name From user_objects Where object_type='VIEW'; --所有视图
Select object_name From user_objects Where object_type='TABLE'; --所有表
4).查询一个用户用到所有表空间:
select distinct tablespace_name from dba_segments where owner ='用户名';(用户名要大写并且加入新建一个用胡户还没建表插入数据到时候是查不出结果)
bjwl:/home/ap/ecds/> sql "select distinct tablespace_name from dba_segments where owner ='ECDS';"
TABLESPACE_NAME
TBS_IDX1
DATDBS01
TBS_DATA1
TBS_DATA2
4 rows selected.
select table_name from all_tables where owner='用户名' and tablespace_name='表空间名称';
bjwl:/home/ap/ecds/> sql "select table_name from all_tables where owner='ECDS' and tablespace_name='DATDBS01';"
TABLE_NAME
T_DYN_AUTH
T_CHK_DETAIL
T_CHK_STAT
JRNL
T_ECD_BKING_REGBK
T_ECD_CCBS_TASK
T_ECD_TASK_ACTION
7 rows selected.