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' )