Oracle常用查询(持续更新中...)

一、查询表或视图定义

方式一：

– 普通用户

SELECT * FROM USER_VIEWS; 

– dba用户

SELECT * FROM DBA_VIEWS where view_name=(select "UPPER"('xxx') from dual)

方式二：（没有测试成功，先记一下）

查看当前用户有什么表或者视图

select * from tab

查看表的定义

select dbms_metadata.get_ddl('TABLE','T1') from dual;

查看视图的定义语句

select dbms_metadata.get_ddl('VIEW','V1') from dual

二、查询用户下的序列

select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences  where sequence_owner='用户名';

用户名需要大写。

三、oracle instant client配置

1、从Oracle官网下载对应版本，解压。
http://www.oracle.com/technetwork/topics/winx64soft-089540.html
2、配置环境变量
1）ORACLE_HOME==>解压后的目录
2）TNS_ADMIN==>解压后的目录
具体可参考：http://blog.csdn.net/magicboylinw/article/details/7025885

四、查询约束

根据约束名查询

select * from dba_constraints where constraint_name='PK__COMMON_M__470B969101BE3717'select * from dba_cons_columns where constraint_name='PK__COMMON_M__470B969101BE3717'

五、查询表使用情况

select segment_name, bytes  from user_segments where segment_type = 'TABLE'; 

或者

Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

---

查询约束相关的SQL

1、查找表的所有索引（包括索引名，类型，构成列）

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表

2、查找表的主键（包括名称，构成列）

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表

3、查找表的唯一性约束（包括名称，构成列）

select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 要查询的表

4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）

a)

select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 要查询的表

b)查询外键约束的列名

select * from user_cons_columns cl where cl.constraint_name = 外键名称

c)查询引用表的键的列名

select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名

5、查询表的所有列及其属性

select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表

---

随机函数

1、小数( 0 ~ 1)

select dbms_random.value from dual;

2、指定范围内的小数 ( 0 ~ 100 )

select dbms_random.value(0,100) from dual;

3、指定范围内的整数 ( 0 ~ 100 )

select trunc(dbms_random.value(0,100)) from dual;

4、长度为20的随机数字串

select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual;

5、正态分布的随机数

select dbms_random.normal from dual;

6、随机字符串

select dbms_random.string(opt, length) from dual;

opt可取值如下：

  'u','U'    :    大写字母  'l','L'    :    小写字母  'a','A'    :    大、小写字母  'x','X'    :    数字、大写字母  'p','P'    :    可打印字符

7、随机日期

select to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual

通过下面的语句获得指定日期的基数

select to_char(sysdate,'J') from dual;

8、生成GUID

select sys_guid() from dual;

---

导入导出

详细参见：http://blog.csdn.net/zftang/article/details/6387325
1、创建DIRECTORY
create directory dir_dp as ‘D:/oracle/dir_dp’;
2、授权
Grant read,write on directory dir_dp to zftang;
–查看目录及权限
SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d
WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;
3、执行导出
1)、 按表模式导出：
expdp zftang/zftang@fgisdb tables=zftang.biexchinfo,zftang.bi_manhole_info dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job

2)、按查询条件导出：
expdp zftang/zftang@fgisdb tables=zftang.b$i_exch_info dumpfile =expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query=‘“where rownum<11”’

3)、按表空间导出：
Expdp zftang/zftang@fgisdb dumpfile=expdp_tablespace.dmp tablespaces=GCOMM.DBF logfile=expdp_tablespace.log directory=dir_dp job_name=my_job

4)、导出方案
Expdp zftang/zftang DIRECTORY=dir_dp DUMPFILE=schema.dmp SCHEMAS=zftang,gwm

5、导出整个数据库：
expdp zftang/zftang@fgisdb dumpfile =full.dmp full=y logfile=full.log directory=dir_dp job_name=my_job

impdp导入模式：
1、按表导入
p_street_area.dmp文件中的表，此文件是以gwm用户按schemas=gwm导出的：
impdp gwm/gwm@fgisdb dumpfile =p_street_area.dmp logfile=imp_p_street_area.log directory=dir_dp tables=p_street_area job_name=my_job

2、按用户导入（可以将用户信息直接导入，即如果用户信息不存在的情况下也可以直接导入）
impdp gwm/gwm@fgisdb schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log directory=dir_dp job_name=my_job

3、不通过expdp的步骤生成dmp文件而直接导入的方法：
–从源数据库中向目标数据库导入表p_street_area
impdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area logfile=p_street_area.log job_name=my_job
igisdb是目的数据库与源数据的链接名，dir_dp是目的数据库上的目录

4、更换表空间
采用remap_tablespace参数
–导出gwm用户下的所有数据
expdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp SCHEMAS=gwm
注：如果是用sys用户导出的用户数据，包括用户创建、授权部分，用自身用户导出则不含这些内容
–以下是将gwm用户下的数据全部导入到表空间gcomm（原来为gmapdata表空间下）下
impdp system/orcl directory=data_pump_dir dumpfile=gwm.dmp remap_tablespace=gmapdata:gcomm

Oracle死锁查询及处理

原文：
http://xinxiangsui2018.blog.163.com/blog/static/106097856201010304532280/
三、死锁的定位方法
通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。
1）用dba用户执行以下语句
select username,lockwait,status,machine,program from vsessionwheresidin(selectsessionidfromvlocked_object)
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。
2）用dba用户执行以下语句，可以查看到被死锁的语句。
select sql_text from vsqlwherehashvaluein(selectsqlhashvaluefromvsession where sid in
(select session_id from v$locked_object))

四、死锁的解决方法
一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可
能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。
　经常在Oracle的使用过程中碰到这个问题，所以也总结了一点解决方法。

1）查找死锁的进程：
sqlplus “/as sysdba” (sys/change_on_install)
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM VLOCKEDOBJECTl,VSESSION S WHERE l.SESSION_ID=S.SID;

2）kill掉这个死锁的进程：
alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

3）如果还不能解决：
select pro.spid from vsessionses,vprocess pro where ses.sid=XX and ses.paddr=pro.addr;
其中sid用死锁的sid替换: exit
ps -ef|grep spid
其中spid是这个进程的进程号，kill掉这个Oracle进程

from:http://southking.javaeye.com/blog/550832

select A.SQL_TEXT, B.USERNAME, C.OBJECT_ID, C.SESSION_ID,
B.SERIAL#, C.ORACLE_USERNAME,C.OS_USER_NAME,C.Process,
””||C.Session_ID||’,’||B.SERIAL#||””
from vsqlA,vsession B, v$locked_object C
where A.HASH_VALUE = B.SQL_HASH_VALUE and
B.SID = C.Session_ID