oracle 工作中常用sql及操作

select status from v$instance; 是否启动成功，ORA-01034: ORACLE not available，试试 startup; 启动
select * from v$version 查看版本
Select * from v$diag_info 查看ADR Home，Diag Alert
select * FROM V$PARAMETER where name like '%background_dump_dest%';

select * from v$reserved_words 查询oracle保留关键字
select * from ALL_DB_LINKS;
select * from dba_db_links; 查看database link
select * from user_sequences 查看当前用户的所有序列，last_number就是此刻执行nextval的值，last_number - increment_by 就是当前值

select value from v\$parameter where name ='processes'–数据库允许的最大连接数
select count(*) from v\$session --当前的session连接数
select count(*) from v\$session where status='ACTIVE'　--并发连接数

select * from v\$locked_object a,dba_objects b where b.object_id=a.object_id 查看被锁的表，system
select c.sid, c.serial# from v\$locked_object a,dba_objects b,v$session c where b.object_id=a.object_id and a.session_id=c.sid 查询sid,serial#,为了 解锁
alter system kill session 'sid,serial#' 解锁被锁的表

drop public database link dblinkname;
drop database link dblinkname 删除database link

create database link mydblink connect to "username" identified by password using '127.0.0.1/orcl' 建立dblink

select * from dual@mydblink 测试dblink连接，如果tns 无监听程序，使用Oracle Net Configuration Assistance，监听程序配置–重新配置，本地网络服务名配置–重新配置，服务名选择数据库名，上面的orcl

select tablespace_name from dba_tablespaces; 所有表空间

SELECT * from dba_tables; 查询所有表空间和表名

select * from all_tables WHERE owner='SCOTT'; 查询库里所有表

create table BranchAccount as select * from TATA.dbo.BranchAccount; 创建查询出的表，表转移

select SUBSTR(dzmc, 0,instr(dzmc,'老街')-1) bf,SUBSTR(dzmc, instr(dzmc,'新街')+5) af,dzmc from BLD_ROOM WHERE id=1026; 修改字段中间的值

select * from table start with ID=5924 connect by prior ID=PARENT_ID id为一级祖父id，一次查出所有下级级联，部门多级等等，cascade查询

SELECT * from tab; 查询所有表，包括【垃圾桶中的表】

select * from recyclebin; 查询刚drop的表（非truncate table）

select count(*) from "BIN$ZqEUzpP4wZbgUAB/AQCoyA==$0"; 查询表数据量

flashback table "BIN$Zs0mMWPwizrgUAB/AQDbpw==$0" to before drop; 恢复表到当前库，

flashback table "BIN$Zs0mMWPwizrgUAB/AQDbpw==$0" to before drop rename to SYS_LOG_03071351;如果当前库有同名表，重命名

exp aa/123456@orcl file=E:/sampleDB.dmp 导出用户aa的所有表
expdp operate_db/oracle dumpfile=operate_db.dmp logfile=operate_db.log

imp bb/123456@orcl file=tank.dmp log=tank fromuser=aa touser=bb 导入其他用户的表数据

impdp system/passwd directory=expdp dumpfile=expdp.dmp remap_schema='usera':'userb' logfile=/oracle/exp.log;
impdp \"/ as sysdba\" dumpfile=dump:dump_query.dmp logfile=dump:impsql.log sqlfile=dump:imp.sql sql文件
select * from all_tables where owner='TEST'; 用户test的所有表

expdp operate_db/oracle@127.0.0.1/orcl schemas=operate_db dumpfile=operate_db12.dmp
impdp operate_db/oracle dumpfile=operate_db1.dmp schemas=operate_db logfile=opimp2.log

select * from dba_directories; impdp设置directory

select count(*) from v$process; 取得数据库目前的进程数。

select value from v$parameter where name = 'processes'; 取得进程数的上限。

select * from table as of timestamp sysdate -1/24; 一小时前表数据，数据误删除恢复
SELECT * FROM FLASH_TBL AS OF TIMESTAMP SYSDATE-5/1440; 5分钟前的表数据
select name from V$Database; SID

select user from dual 当前用户名
select * from user_tables 用户所有表
select * from user_tab_columns where table_name ='' 表的所有字段

分页，pageSize从1开始
select * from (
    select rownum r,e. * from (
        your sql
    ) e where rownum<=pageSize*pageNum
) t where r>pageSize*pageNum-pageSize;

生成连续时间区间内时间

SELECT to_char( to_date( '2019-03-13 15', 'yyyy-mm-dd hh24' ) + ( ROWNUM - 1 ) / 24, 'yyyy-mm-dd hh24' ) sdate 
FROM dual
CONNECT BY ROWNUM <= (to_date( '2020-03-13 15', 'yyyy-mm-dd hh24' ) - to_date( '2019-03-13 15', 'yyyy-mm-dd hh24' )) * 24 + 1 

takeDate between to_date('downloadDateBeginStr','yyyy-mm-dd hh24:mi:ss') and to_date('downloadDateEndStr','yyyy-mm-dd hh24:mi:ss' )