数据库细节

oracle 查询所有表并删除：
select 'drop table '||table_name||';' as sqlscript from user_tables;
删除：：：：：
select 'drop table '||table_name||' cascade constraints purge;' as sqlscript from user_tables;
orcle 创建用户并授权
create user tms identified by tmsneu; 
grant connect,resource,dba to tms;
commit;
触发器ID自增：
create or replace TRIGGER "TMS"."TRIGGER_ORDER_DETAIL_ID"
before   insert   on   "T_TMS_ORDER_DETAIL" for each row  
  begin  
    select sys_guid() into:new.id from dual;
    end;
创建数据库公共链接：
  CREATE PUBLIC DATABASE LINK "BEYOND_DATABASE"
   CONNECT TO "TMS" IDENTIFIED by VALUES ':1'
   USING '(DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.106)(PORT = 1521))
     )
     (CONNECT_DATA =
(SERVICE_NAME = tms)
     )
)';
数据库同步__定时任务：
1.创建链接：
create database link invoice     --输入所要创建dblink的名称，自定义
connect to DATATRANS identified by "DATATRANS"  --设置连接远程数据库的用户名和密码
using '192.168.3.31/DATATRANS';   --指定目标数据库的连接方式，可用tns名称
存储过程：
create or replace procedure proc001 is
begin
    EXECUTE IMMEDIATE ' delete from 表@dblink’   --删除
   EXECUTE IMMEDIATE ' insert into T_TMS_BASE_ROUTE_PLAN@localhost select * from T_TMS_BASE_ROUTE_PLAN '; --添加
  COMMIT;
end proc001 ;
--建立定时任务：
DECLARE
job number;
begin
  sys.dbms_job.submit(job,
     'proc001;',
     sysdate,
    'trunc(sysdate) + 1');
  commit;
end;



select * from user_jobs;