Oracle之常用查询语句

 --查询表下所有父分区
 SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='tablename';

 --------------------------------------
 --查询表下的所有子分区
 SELECT * FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='tablename';

 --------------------------------------
 --删除指定分区
 alter table tablename drop partition(P2019);

 --------------------------------------
 --查询指定分区数据
 select count(*) from tablenpartition(P1);  

 -------------------------------------
 --查找指定日期的数据
 select * from tablenameb where b.statisticstime between to_date('2018-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2018-12-31 00:00:00','yyyy-mm-dd hh24:mi:ss')
 
 -------------------------------------
 --查看job状态
 SELECT * FROM DBA_DATAPUMP_JOBS 
 
 -------------------------------------
 --查看指定名称的job
 select * from user_jobs where job='526';
 
 -------------------------------------
 --游标查询字段测试
 select to_char(bjch.TJSJ, 'yyyy') dt from tablename bjch group by to_char(bjch.TJSJ, 'yyyy') order by to_char(bjch.TJSJ, 'yyyy');

 --------------------------------------
 --导出数据库指定表数据
  exp ntswl/ntswl@orcl file=d:\tablename.dmp tables=(tablename)

 --------------------------------------
 --导入指定表数据 表存在则导入数据
  imp signal/signal@orcl file=d:\tj11001.dmp tables=(tablename) ignore=y

 --------------------------------------
 --删除指定表
 drop table tablename

 --------------------------------------
 --查询语句
 select count(*) from tablename

 --------------------------------------
 --查询索引
 select * from user_ind_columns where table_name = 'tablename'
 and column_name = 'username';

 ---------------------------------------
 --查看索引名称 
 select * from user_indexes where index_name = 'SYS_C00150530'

 ---------------------------------------
 --删除指定索引
 --（无法删除,唯一约束）
 drop index SYS_C00154024;

 ----------------------------------------
 --查询表下的所有分区
 SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='tablename';

 ----------------------------------------
 --删除指定分区
 alter table tablename drop partition(P4);

 -----------------------------------------
 --查看索引是否有效
 select status,T.* from user_indexes T where table_name='tablename'

 -----------------------------------------
 --会话
 select l.session_id,o.owner,o.object_name from v$locked_object l,dba_objects o where l.object_id=o.object_id
 
 -----------------------------------------
 --会造成wait的会话
 select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;

 -----------------------------------------
 --结束指定会话
 alter system kill session '1258,34133';
 
 -----------------------------------------
 --查看指定名称的存储过程文本
 select text from user_source where type='PROCEDURE' and name='CREATEPARTITION_BJZH';
 
 -----------------------------------------
 --强制结束会话
 alter system kill session '5344,9737' immediate;
 
 -----------------------------------------
 --查看表空间大小
 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size 
 FROM dba_tablespaces t, dba_data_files d 
 WHERE t.tablespace_name = d.tablespace_name 
 GROUP BY t.tablespace_name; 
 
 ------------------------------------------
 --查询删除的内容
 select * from user_recyclebin where ORIGINAL_NAME > '2018-10-18 15:00:00';
 
 ------------------------------------------
 --查询删除的表
 select * from user_recyclebin t where t.TYPE='TABLE'
 
 ------------------------------------------
 --查询指定表空间
 SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS 
 FROM DBA_TABLESPACES T,DBA_DATA_FILES D 
 WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 
 and T.TABLESPACE_NAME='username'
 ORDER BY TABLESPACE_NAME,FILE_NAME;

 -------------------------------------------------------------------
 --查询表空间大小以及使用比
 SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
       D.TOT_GROOTTE_MB "表空间大小(M) ",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
                     2),
               '990.99') "使用比",
       F.TOTAL_BYTES "空闲空间(M) ",
       F.MAX_BYTES "最大块(M) "
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC
 
 ----------------------------------------------------------
 --增加数据文件并允许自动增长 
 ALTER TABLESPACE TRAFFICINDEX ADD DATAFILE 
 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\TRAFFICINDEX.DBF' SIZE 1024M AUTOEXTEND ON NEXT 8M MAXSIZE 10240M;
 
 -----------------------------------------
 --查询触发器
 select * from all_triggers where table_name='tablename';  

 

转载于:https://www.cnblogs.com/assistants/p/9890943.html