学步园

一、查询外键对应的表及字段

1、通过下面SQL语句查询外键对应的表及字段 P代表主键 R代表外键
select a.constraint_name, a.table_name, b.constraint_name 
from user_constraints a, user_constraints b 
where a.constraint_type = 'R' 
and b.constraint_type = 'P'  and a.r_constraint_name = b.constraint_name 

2、查找出相应的表及列 根据外键编码 删除外键表数据 然后再删除主表数据

3、显示外键表及外键约束名称
select a.*
  from user_constraints a, user_constraints b
 where a.constraint_type = 'R'
  and b.constraint_type = 'P'
  and b.table_name='主表名称'
  and a.r_constraint_name = b.constraint_name

二、如果知道入职日期(start_date)如何知道周几入职（用英文全拼），显示顺序从周一至周日。

三、Oracle内部如何查看包体源码

SELECT *  FROM ALL_SOURCE

 WHERE TYPE = 'PACKAGE BODY'

  and owner = 'DEV_SPS'  and name = 'WO_VALIDATE_SQL'

 ORDER BY NAME, LINE

四、用dbms取出表的建表语句

select dbms_metadata.get_ddl('TABLE', 'TEST_YIXL_T2') from dual;

--’TEST_YIXL_T2’，表名要大写。

结果如下：

 CREATE TABLE "APPS"."TEST_YIXL_T2"

   (     "T1" VARCHAR2(10),

          "T2" VARCHAR2(10),

          "T3" VARCHAR2(10),

          "T4" VARCHAR2(10),

          "T5" VARCHAR2(10),

          "T0" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "APPS_TS_TX_DATA"

 "

也可以执行下面语句取出自定义类型的DDL：

select dbms_metadata.get_ddl('TYPE', 'YIXL_TABLE_TYPE') from dual;

结果是：

CREATE OR REPLACE TYPE "APPS"."YIXL_TABLE_TYPE" IS TABLE OF VARCHAR2(200);

五、取两个日期之间相差几个月

select  months_between(sysdate,sysdate-180) from dual;

六、转义字符

      下面用法都可以实现查找zdlx字段中是否含有’JOB_O’字符串

select * from t_zd where zdlx like '%JOB/_O%' escape '/';

select * from t_zd where instr(zdlx,'JOB_O')>0; 

select * from t_zd where regexp_like(zdlx,'JOB_O'); 

七、DROP表后恢复的过程

（详细语法说明参见：《ORACLE回收站.doc》）

1. 创建表A， create table A(t1 varchar2(10));

    insert into A values('aaaaaaaaa');

    commit;

2.删除表A   drop table A

3.再次创建表A  create table A(t1 varchar2(10), t2 number(10));

   insert into A values ('bbbbbbbbbb', 2);

   commit;

4.此次发现还想找回步骤1建的表A及数据，则

(1)首先把步骤3建的表A重命名，语法如下：

    alter table A rename to A_bak; --如果不做RENAME操作，下个(2)恢复的时候，会报错

(2)执行步骤1中表A的表结构及数据的恢复：

    flashback table A to before drop rename to A;

(3)完事儿喽。。。这时， SELECT * from A的数据，就是步骤1中插入的数据。

八、get_line和put_line

九、CAST用法：

select to_char(cast(0.567 as decimal(10,3)),'0.000') from dual

十、刚删除一行怎么恢复

第一步:找到删除数据的操作时间从( v$sql 或者 v$sqlarea 视图里面查询 )
select r.FIRST_LOAD_TIME,r.SQL_TEXT,r.optimizer_mode,r.module,r.action,r.LAST_ACTIVE_TIME 
from v$sqlarea r 
order by r.FIRST_LOAD_TIME desc ;
第二步:
create table t_table_recove
as
select * from t_table 
as of timestamp to_timestamp('2010-06-02 11:36:53','yyyy-mm-dd hh24:mi:ss');

to_timestamp('2010-06-02 11:36:53.000000','yyyy-mm-dd hh24:mi:ss.ff')

再将恢复后的数据放到原来表就可以了.

十一、获取查询结果集中后20条记录很典型的写法

SELECT * FROM (

  SELECT ROWNUM AS my_rownum,table_a.* FROM

  ( select * from user_objects where object_type = 'TABLE') table_a  

  WHERE ROWNUM <= 160 ) WHERE my_rownum > 140;