Oracle常用sql

1. oracle根据查询结果创建新表【创建新表并复制旧表的表结构】
   将查询结果直接插入新表

create table new_table_name as select *  from xxx where 1=2

2. 合并表数据

merge into table1 t1
using (select * from table2) t2
on (t1.column = t2.column)
when matched then 语句1
when not matched then 语句2
--这里的语句1和语句2只能为insert操作或是update操作

3. 检查是否锁表
   此处只是查看是否有表被锁，未涉及其他操作

select * from V$LOCKED_OBJECT a,DBA_OBJECTS b 
where b.object_id = a.object_id

4. 查询数据库当前时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

5. 查看表空间

select * from DBA_DATA_FILES where TABLESPACE_NAME = 'USERS';

6. 查看sql是否走索引

explain plan for 查询语句
select * from TABLE(dbms_xplan.display);

7. 创建dblink

create database link to_table
connect to 用户
identified by 密码
using 'ip:1521/实例名';

8. 获取指定时间的快照

select * from SYSTEM_SETUP as of timestamp
to_timestamp('2023-05-30 20:00:00','yyyy-mm-dd hh24:mi:ss');