Oracle 常用语句

1、Oracle中查看所有表和字段 - 作者emanlee - 博客园 地址链接：

http://www.cnblogs.com/emanlee/archive/2011/12/02/2272629.html

2、取时间的序列：

select to_number(to_char(sysdate,'yyyymmddhh24mimiss')|| lpad(round(dbms_random.value(1,999999)),4,0))from dual;

3、字符全角半角之间的转换：

将全角字符转为半角字符可使用： to_singal_byte(c)

将半角字符转为全角字符可使用： to_multi_byte(c)

4、查看游标执行情况

--查看游标使用情况
 select o.sid, osuser, machine, count(*) num_curs
   from v$open_cursor o, v$session s
  where user_name = 'USERNAME'
    and o.sid = s.sid
  group by o.sid, osuser, machine
  order by num_curs desc;
----查看游标执行的sql情况
select q.sql_text 
from v$open_cursor o, v$sql q 
where q.hash_value=o.hash_value and o.sid = '913';
 

5、使用timestamp闪回查看某个时间点的数据情况：

select * from tables as of timestamp to_timestamp('2013-06-29 22:57:47', 'yyyy-mm-dd hh24:mi:ss');

6、将查询出来的某列使用逗号分隔：

select    TO_CHAR(WM_CONCAT(AAE140)) from ab02 where aab001 = '20183733' 

7、expdp  和 impdp的使用

----------------------------------------------------------------------------------------
---------------------------------    导入     ------------------------------------------
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--解压文件到相应的路径、一般取DATA_PUMP_DIR
select '取 '||directory_name || ' 请解压文件到 '|| directory_path ||' 目录下' from dba_directories;
 
--导入语句选择
select 'impdp '||'用户名/密码@实例名 ' || 'directory=' || directory_name || ' ' || 'dumpfile=' || '文件名' ||
       ' REMAP_SCHEMA=导出用户' || ':' || '导入用户' || ' ' ||
       'remap_tablespace=导出表空间'||':'||'导入表空间'||' '||'full=y;'
  from dba_directories;
 
 
----------------------------------------------------------------------------------------
---------------------------------    导出     ------------------------------------------
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
--expdp test/test DIRECTORY=expdp_dir DUMPFILE=test.dmp logfile=testexpdp.log
 
----导出文件到相应的路径、一般取DATA_PUMP_DIR
--导出语句选择 CONTENT={ALL | DATA_ONLY | METADATA_ONLY} 当设置CONTENT为ALL时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义
select 'expdp '||'用户名/密码@实例名 ' || 'directory=' || directory_name || ' ' || 'dumpfile=' || '文件名' ||
       ' content=all;'
  from dba_directories;

8、查询剩余空间

select total.tablespace_name,
　　round(total.MB, 2) as Total_MB,
　　round(total.MB - free.MB, 2) as Used_MB,
　　round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct
　　from (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　from dba_free_space
　　group by tablespace_name) free,
　　(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
　　from dba_data_files
　　group by tablespace_name) total
　　where free.tablespace_name = total.tablespace_name;

9、特殊字符的转换

select ascii('&') from dual；

10、oracle查看某个时间点的包的内容

select  *
from dba_source AS OF TIMESTAMP TO_TIMESTAMP('2013-11-22 22:51:00','YYYY-MM-DD HH24:MI:SS')
where owner='USERNAM'
and name=upper('PKG_LAG_FEEAUDITING') 
and type='PACKAGE BODY'  --PACKAGE   PACKAGE BODY
order by line;

11、查看表结构的最近修改时间

SELECT *
  FROM dba_tables a, SYS.dba_objects b
WHERE a.tablespace_name = 'USERNAME'
   AND a.owner = b.owner
   AND a.table_name = b.object_name 
   AND object_type = 'TABLE' order by LAST_DDL_TIME 

12、查询日期转换成中文年月

select to_char(sysdate,'yyyy"年"mm"月"dd"日"') from dual; 
 

13、在某个字段的排序情况下，取其中的某几条

select * from(
   select a.*, row_number() over(order by rowid) num_id from tables a
) where num_id <=100;