数据库笔记：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.youkuaiyun.com/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

---

六、查询表数据行数

select owner,table_name, num_rows from dba_tables 
where owner='用户'
and num_rows is not null
order by num_rows desc

---

七、查询约束相关的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 = 要查询的表

6、查询表的主键被哪些表外键引用

select c.TABLE_NAME  
  from all_constraints p, all_constraints c  
 where p.table_name = 'FW_T_EMS_ORGDD'  
   --and p.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')  
   --and c.OWNER=SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')  
   and c.constraint_type = 'R'  
   and p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME  

7、查询该表中的外键来源于哪些表

select cl.table_name  
  from user_cons_columns cl  
  left join user_constraints c  
    on cl.constraint_name = c.r_constraint_name  
 where c.constraint_type = 'R'  
   and c.table_name = '表名'  

查询未提交的事务

select a.sid,a.blocking_session,a.last_call_et,a.event,
object_name,
dbms_rowid.rowid_create(1,data_object_id,rfile#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) “rowid” ,
c.sql_text,c.sql_fulltext
from v$sessiona,v$sqlarea c ,dba_objects,v$datafile
where a.blocking_session is not null
and a.sql_hash_value = c.hash_value
and ROW_WAIT_OBJ#=object_id and file#=ROW_WAIT_FILE#;

---

八、 随机函数

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.youkuaiyun.com/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.b$i_exch_info,zftang.b$i_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 v$sessionwheresidin(selectsessio{n}_{i}dfromv$locked_object)
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。
2）用dba用户执行以下语句，可以查看到被死锁的语句。

select sql_text from v$sql where hash_value in 
(select sql_hash_value from v$session 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 V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

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

3）如果还不能解决：

select pro.spid from v$session ses,v$process 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 v$sql A, v$session B, v$locked_object C
where A.HASH_VALUE = B.SQL_HASH_VALUE and
B.SID = C.Session_ID

十一、两表关联更新

方式1

UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME);

方式2

UPDATE (
select t1.fmoney  fmoney1,t2.fmoney  fmoney2 from t1,t2 where t1.fname = t2.fname
)t
set fmoney1 =fmoney2;

方式3

merge into t1
using (select t2.fname,t2.fmoney from t2) t
on (t.fname = t1.fname)
when matched then 
  update  set t1.fmoney = t.fmoney;

十二、Oracle查看用户密码过期时间，修改永不过期

01、查看当前open用户
select username,account_status,expiry_date,profile from dba_users;

02、查看目前的密码过期策略
select * from dba_profiles s where s.profile=‘DEFAULT’ and resource_name=‘PASSWORD_LIFE_TIME’;

03、修改密码过期策略
alter profile default limit password_life_time unlimited;

04、过期的账户，重置密码后期不会再过期
$sqlplus / as sysdba

alter user smsc identified by <原来的密码> ----不用换新密码