一些好用的 Oracle 批处理和语句

# 备份脚本 backup.bat

@ECHO OFF
COLOR 0A
SET DaysAgo=1
SET Today=%date:~0,4%%date:~5,2%%date:~8,2%
EXP user/password BUFFER=64000 FILE=D:/database/db_%Today%.dmp OWNER=user
RAR a db_%Today%.rar db_%Today%.dmp
DEL db_20170809.dmp /q
PAUSE

 

# 查询主外键

select a.owner 外键拥有者, a.table_name 外键表, substr(c.column_name,1,127) 外键列, b.owner 主键拥有者, b.table_name 主键表, substr(d.column_name,1,127) 主键列
from user_constraints a, user_constraints b, user_cons_columns c, user_cons_columns d
where a.r_constraint_name=b.constraint_name
  and a.constraint_type='R'
  and b.constraint_type='P'
  and a.r_owner=b.owner
  and a.constraint_name=c.constraint_name
  and b.constraint_name=d.constraint_name
  and a.owner=c.owner
  and a.table_name=c.table_name
  and b.owner=d.owner
  and b.table_name=d.table_name