Oracle的一些常用语句

一、创建dblink

CREATE PUBLIC DATABASE LINK  dblink_name CONNECT TO username IDENTIFIED BY "password" USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =visit_ip)(PORT =visit_port ))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db_name)
    )
  )';
  --参数说明
  --username 需要访问数据用户名， password  密码， visit_ip  需要访问数据库的ip，visit_port端口号， db_name数据库名称

二、获取注释

  SELECT t3.comments ||T1.TABLE_NAME 表名,
T1.COLUMN_NAME  字段名,
T1.DATA_TYPE || '(' || T1.DATA_LENGTH || ')'  类型,
t1.nullable 是否为空,
T2.COMMENTS 字段注释
FROM USER_TAB_COLS T1, USER_COL_COMMENTS T2,user_tab_comments t3
WHERE T1.TABLE_NAME = T2.TABLE_NAME
AND T1.COLUMN_NAME = T2.COLUMN_NAME
and t1.table_name=t3.table_name
AND T1.TABLE_NAME like 'IF_ZCFZ_P%' ;

 --自动拼接表的字段
SELECT  t2.column_id A,
       LOWER(t.column_name )|| ',--' || t.comments B,
       'null '||LOWER(t.column_name ) || ',--' || t.comments C,
           ' --' ||t.comments D

  FROM user_col_comments t, user_tab_columns t2
 WHERE t.table_name = t2.table_name
   AND t.column_name = t2.column_name
   AND t.table_name = upper('ods_plyhi_policy') --表名
 ORDER BY t2.column_id; 

SELECT t.column_name 字段,
       t1.comments 注释,
       t.data_type || CASE
           WHEN t.data_type = 'DATE' THEN
            NULL
           ELSE
            '(' || nvl(data_precision, data_length) || nvl2(data_precision, ',', '') ||
            decode(t.data_scale, 0, NULL, t.data_scale) || ')'
       END 字段类型,
       t.data_length 字段长度,
       t.data_precision 小数前位数， t.data_scale 小数
  FROM user_tab_cols t, user_col_comments t1
 WHERE t.table_name = 'tablename'
   AND t.column_name = t1.column_name
   AND t.table_name = t1.table_name;

三、导出表结构

SELECT t1.Table_Name || chr(13) || t3.comments       AS "表名称及说明",
       --t3.comments                                 AS "表说明",
       t1.Column_Name                                AS "字段名称",
       t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')'  AS "数据类型",
       t1.NullAble                                   AS "是否为空",
       t2.Comments                                   AS "字段说明",
       t1.Data_Default                               as "默认值"
       --t4.created                                    AS "建表时间"
  FROM cols t1
  LEFT JOIN user_col_comments t2 
         ON t1.Table_name = t2.Table_name
        AND t1.Column_Name = t2.Column_Name
  LEFT JOIN user_tab_comments t3 
         ON t1.Table_name = t3.Table_name
  LEFT JOIN user_objects t4 
         ON t1.table_name = t4.OBJECT_NAME
  WHERE NOT EXISTS (SELECT t4.Object_Name
          FROM User_objects t4
         WHERE t4.Object_Type = 'TABLE'
           AND t4.Temporary = 'Y'
           AND t4.Object_Name = t1.Table_Name)
  ORDER BY t1.Table_Name, t1.Column_ID;

四、批量替换同义词

SELECT 'CREATE  OR REPLACE SYNONYM ' || t.synonym_name || ' FOR ' || synonym_name || 'new_synonym;'
  FROM user_synonyms t
 WHERE t.db_link = 'old_synonym';

五、delete之后恢复数据方法

1.0 、找出删除的数据：

select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss');

1.1、把删除的数据重新插入原表：

   insert into 表名 (select * from 表名 as of timestamp to_timestamp('删除时间点','yyyy-mm-dd hh24:mi:ss'));

2.1、如果表结构没有发生改变，还可以直接使用闪回整个表的方式来恢复数据。
具体步骤为：
表闪回要求用户必须要有flash any table权限
–开启行移动功能

alter table 表名 enable row movement

–恢复表数据

 flashback table 表名 to timestamp to_timestamp(删除时间点','yyyy-mm-dd hh24:mi:ss')

–关闭行移动功能

alter table 表名 disable row movement

六、drop之后恢复数据方法

1、查询这个“回收站”或者查询user_table视图来查找已被删除的表:

 select table_name,dropped from user_tables
 select object_name,original_name,type,droptime from user_recyclebin

在以上信息中，表名都是被重命名过的，字段table_name或者object_name就是删除后在回收站中的存放表名
2、如果能记住表名，可以用下面语句直接恢复：

flashback table 原表名 to before drop

如果忘记表名，也可以直接使用回收站的表名进行恢复，然后再重命名

  flashback table "回收站中的表名(如：Bin$DSbdfd4rdfdfdfegdfsf==$0)" to before drop rename to 新表名

oracle的闪回功能除了以上基本功能外，还可以闪回整个数据库：
使用数据库闪回功能，可以使数据库回到过去某一状态, 语法如下：

SQL>alter database flashback on
SQL>flashback database to scn SCNNO;
SQL>flashback database to timestamp to_timestamp('2007-2-12 12:00:00','yyyy-mm-dd hh24:mi:ss');