oracle常用技巧

1.      oracle去除表重复数据... 1

2.      SQLServer和oracle查看锁解锁... 1

3.      获取当前年份月份... 2

4.      获取当前数据库中所有的用户存储过程名称... 2

5.      获取数据库中所有的用户定义函数名称和定义... 2

6.      监控SQL Server正在执行的SQL语句和死锁情况... 3

7.      导入导出数据... 5

8.      处理除数为0. 6

9.      四舍五入... 6

10.        显示小数时处理个位数为0时不显示... 6

11.        解锁，修改数据... 6

12.        排列分组（根据某列分组后，再已某列排序）... 7

13.        过滤非纯数字... 7

14.        单引号“’”转义... 7

15.        行转列（pivot）... 7

16.        用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询select...start with... connect by ...prior  7

17.        oracle将数据按逗号拆分成多行... 8

18.        表空间... 8

19.        新建对象... 9

20.        直接在windows窗口运行sql文件... 9

21.        修改表结构... 9

 

1.    oracle去除表重复数据

DELETEFROM tb WHERE ROWID NOT IN (

     SELECT MAX(ROWID) FROM tb GROUP BYcol1,col2...

)

2.   SQL Server和oracle查看锁解锁

oracle数据库

select'alter system kill session'''||+b.sid||','||b.SERIAL#||''';',b.username,b.sid,b.serial#,logon_time

fromv$locked_object a,v$session b

wherea.session_id = b.sid order by b.logon_time;

 

sqlserver数据库

execp_lockinfo 0,0;--查看死锁

execp_lockinfo 1,1;--解锁

3.   获取当前年份月份

withtemps as

 (select to_char(sysdate, 'yyyy') ||lpad(level, 2, 0) mon

    from dual

  connect by level < 13)

selectmon from temps

4.   获取当前数据库中所有的用户存储过程名称

SELECTsp.NAME

FROMsys.all_objects AS sp

WHERE (

        (

            sp.type = N'P'

            OR sp.type = N'RF'

            OR sp.type = N'PC'

            )

        AND (

            CAST(CASE

                    WHEN sp.is_ms_shipped = 1

                        THEN 1

                    WHEN (

                            SELECT major_id

                            FROMsys.extended_properties

                            WHERE major_id =sp.object_id

                                AND minor_id =0

                                AND class = 1

                                AND NAME =N'microsoft_database_tools_support'

                            ) IS NOT NULL

                        THEN 1

                    ELSE 0

                    END AS BIT) = 0

            )

        )

5.   获取数据库中所有的用户定义函数名称和定义

SELECTudf.NAME,

     SCHEMA_NAME(udf.schema_id),

     m.DEFINITION

 FROM [sys].[all_sql_modules] m

 INNER JOIN [sys].[all_objects] udf ONm.object_id = udf.object_id

 WHERE DEFINITION LIKE '%create function%'

     AND udf.type IN (

         'TF',

         'FN',

         'IF',

         'FS',

         'FT'

         )

     AND CAST(CASE

             WHEN udf.is_ms_shipped = 1

                 THEN 1

             WHEN (

                     SELECT major_id

                     FROMsys.extended_properties

                     WHERE major_id = udf.object_id

                         AND minor_id = 0

                         AND class = 1

                         AND NAME =N'microsoft_database_tools_support'

                     ) IS NOT NULL

                 THEN 1

             ELSE 0

             END AS BIT) = 0

6.   监控SQL Server正在执行的SQL语句和死锁情况

SELECT[Individual Query] = SUBSTRING(qt.TEXT, er.statement_start_offset / 2, (

            CASE

                WHEN er.statement_end_offset =- 1

                    THENLEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2

                ELSE er.statement_end_offset

                END - er.statement_start_offset

            ) / 2),

    [Parent Query] = qt.TEXT,

    [Spid] = session_Id,

    ecid,

    [Database] = DB_NAME(sp.dbid),

    [User] = nt_username,

    [Status] = er.STATUS,

    [Wait] = wait_type,

    Program = program_name,

    Hostname,

    nt_domain,

    start_time

FROMsys.dm_exec_requests er

INNERJOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSSAPPLY sys.dm_exec_sql_text(er.sql_handle) AS qt

WHEREsession_Id > 50 /* Ignore system spids.*/

    AND session_Id NOT IN (@@SPID)

------------------------------------------------------

--每秒死锁数量

SELECT *

FROMsys.dm_os_performance_counters

WHEREcounter_name LIKE 'Number of Deadlocksc%';

 

--查询当前阻塞

WITHCTE_SID(BSID, SID, sql_handle) AS (

        SELECT blocking_session_id,

            session_id,

            sql_handle

        FROM sys.dm_exec_requests

        WHERE blocking_session_id <> 0

        UNION ALL

        SELECT A.blocking_session_id,

            A.session_id,

            A.sql_handle

        FROM sys.dm_exec_requests A

        JOIN CTE_SID B ON A.SESSION_ID = B.BSID

        )

 

SELECTC.BSID,

    C.SID,

    S.login_name,

    S.host_name,

    S.STATUS,

    S.cpu_time,

    S.memory_usage,

    S.last_request_start_time,

    S.last_request_end_time,

    S.logical_reads,

    S.row_count,

    q.TEXT

FROMCTE_SID C

JOINsys.dm_exec_sessions S ON C.sid = s.session_id

CROSSAPPLY sys.dm_exec_sql_text(C.sql_handle) Q

ORDER BYsid

 

--检查表的更新排他锁

DECLARE@t_lock AS TABLE (

    [spid] [smallint] NULL,

    [dbid] [smallint] NOT NULL,

    [ObjId] [int] NOT NULL,

    [IndId] [smallint] NOT NULL,

    [Type] [nvarchar](4) NULL,

    [Resource] [nvarchar](32) NULL,

    [Mode] [nvarchar](8) NULL,

    [Status] [nvarchar](5) NULL

    )

 

INSERTINTO @t_lock

EXECsp_lock

 

SELECT*,

    [Database] = DB_NAME([dbid]),

    [Object] = OBJECT_NAME([ObjId], [dbid])

FROM@t_lock

WHERE[spid] > 50 /* Ignore system spids.*/

    AND [spid] NOT IN (@@SPID)

    AND [Type] = 'TAB'

    AND [Mode] IN (

        'U',

        'IU',

        'SIU',

        'UIX',

        'BU',

        'RangeS_U',

        'RangeI_U',

        'X',

        'IX',

        'SIX',

        'UIX',

        'RangeI_X',

        'RangeX_S',

        'RangeX_U',

        'RangeX_X'

        )

7.   导入导出数据

1>    整库导

导入：impdpuserid='system/oracle@orcl' schemas=pamsTest directory=DATA_FILE_DIRdumpfile=PAMS20170922.DMP version=10.2.0.1.0

 

导出：expdpuserid='pams/pams@orcl' schemas=pams directory=DATA_FILE_DIRdumpfile=PAMS20170508.DMP version=10.2.0.1.0

 

2>    导出表

expuserid=pamsTest/pams_db@orcl tables=(pams_svg_info) file=E:\test.dmp

 

3>    exp/imp命令

unix下：

exp\'sys/口令 as sysdba\' file=a.dmp owner=导出用户 rows=N

imp\'sys/口令 as sysdba\' file=a.dmp fromuser=导出用户 touser=导入用户

windows下：

exp'sys/口令 as sysdba' file=a.dmp owner=导出用户 rows=N

imp'sys/口令 as sysdba' file=a.dmp fromuser=导出用户 touser=导入用户

rows=y表示数据和结构都导出

rows=n只导出结构，不导数据

 

4>    同一个数据库下，数据在用户之间迁移

impdpuserid='pams_wy/pams_db@orcl' directory=DATA_FILE_DIR dumpfile=PAMS20171114.DMPREMAP_SCHEMA = pams:pams_lt

 

5>    查看数据库导出文件的路径

SELECT *FROM dba_directories

http://www.cnblogs.com/mabaishui/archive/2011/09/30/2196802.html    Oracle11g需要用数据泵来备份和还原数据

8.   处理除数为0

decode(czys.ysgyjf,0, '0%', round(gkzf.gyjf / czys.ysgyjf * 100, 2)||'%') as ZXLGYJF

9.   四舍五入

round(gkzf.ryjf/ czys.ysryjf * 100, 2)||'%'

10.             显示小数时处理个位数为0时不显示

selectto_char(0.1,'fm9999990.9999') from dual;

11.             解锁，修改数据

selectt.id,t.htqdrq,t.bz,t.rowid from fao_cz_zfcg t-----查询rowid才可以执行edit操作

 

select *from fao_cz_zfcg order by zbbh

 

commit;

 

selectt2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$sessiont2 where t1.session_id=t2.sid;---查询sid

 

altersystem kill session '73,2098';----输入sid，serial#解锁

12.             排列分组（根据某列分组后，再已某列排序）

select *

  from (select row_number() over(partition byproject order by to_number(type) desc) rn,

               d.*

          from pams_project_date d)

 where rn = 1;

13.             过滤非纯数字

selectcode from pams_filecode where regexp_replace(code,'^[-\+]?\d+(\.\d+)?$','') isnot null;

14.             单引号“’”转义

sql语句单引号转义使用'单引号'，sql语句中单引号括的字符串只包含最外层的一对，内部的单引号都是转义符，但是此转义符只对单引号有用，eg: select '''' from dual;输出：‘； select '11'''from dual;输出：11‘； select '''11' from dual;输出：‘11；

15.             行转列（pivot）

select *from () pivot (fun() for column in ('','',...))

16.             用于存在父子，祖孙，上下级等层级关系的数据表进行层级查询select...start with... connect by ...prior

{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITHcondition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [ANDcondition]...
}

1>    connect by中的条件就表示了父子之间的连接关系，比如 connect by id=prior  pid

2>    prior，表示在一表上，prior所在列pid的某一值A的记录行的父亲是列id上值等于列pid上值A的记录行。

3>    LEVEL伪列表示树的深度（或叫高度）。

4>    oracle中的select语句可以用STARTWITH...CONNECT BY子句实现递归查询（或叫树状查询），connect by 是结构化查询中用到的；

select ... from <TableName>

where <Conditional-1>     --过滤条件，用于对返回的所有记录进行过滤。

start with <Conditional-2>         --该限定条件，表示查询结果以谁作为起始根结点的。当然可以放宽该限定条件，以取得多个根结点，实际就是多棵树。

connect by <Conditional-3>        --连接条件，即表示不同节点间以该连接条件构成一个父子关系

Eg:select id, pid, name

from table

start with  pid = ‘ROOT’

connect by prior id = pid

1)      查找树中的所有顶级父节点（辈份最长的人）。

SELECT  * FROM  flfl  WHERE sjflid  IS NULL; 

2)      查找一个节点的直属子节点（所有儿子）。

SELECT  * FROM  flfl  WHERE sjflid  =  819459;  

3)      查找一个节点的所有 直属子节点（所有后代）。

SELECT  * FROM  flfl  START  WITH  ID = 819459 CONNECT BY sjflid = PRIOR ID; 

4)      查找一个节点的直属父节点（父亲）。

SELECT  b.* FROM  flfl  a  JOIN  flfl  b  ON a.sjflid = b.ID WHERE a.ID = 6758;  

5)      查找一个节点的所有直属父节点（祖宗）。

SELECT * FROM flfl START WITH ID = 6758 CONNECT BY PRIOR sjflid = ID;

请参考：https://blog.csdn.net/qiange520/article/details/50515317

17.             oracle将数据按逗号拆分成多行

selectt1.id,wmsys.wm_concat(t2.name) replyname from (select * from (selectid,regexp_substr(reply, '[^,]+', 1, level, 'i') as reply

frompams_project

connectby level <=

length(nvl(reply,' ')) - length(regexp_replace(nvl(reply, ' '), ',', '')) + 1

) groupby id,reply) t1 left join pams_project_type t2 on t1.reply = t2.id

group byt1.id

18.             表空间

查看表空间下数据文件路径：select *from dba_data_files where tablespace_name = 'USERS'

 

添加表空间数据文件：altertablespace USERS add datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS04.DBF'size 2000M autoextend on;

一、创建一个表空间my_space,该表空间有两个数据文件，f:\oradata\myo1.dbf(5M)和f:\oradata\my02.dbf(10M)自动增长，每次增长1M,最大增长至20M

 

createtablespace my_space datafile 'f:\oradata\my01.dbf' size 5M autoextendoff,'f:\izoradata\my02.dbf' size 10M autoextend on next 1M maxsize 20M;

 

二、系统运行了一阵时间，表空间容量不够需要扩充，决定将数据文件f:\oradata\my01.dbf从5M调整到20M

 

alterdatabase datafile 'f:\oradata\my01.dbf' resize 20M

 

三、系统继续运行，表空间容量再次紧张而且F盘也没有足够的空间扩容，决定为表空间在E盘增加一个数据文件e:\oradata\my01.dbf(10M)，不自动增长

 

altertablespace my_space add datafile 'E:\oradara\my03.dbf' size 10M autoextend off;

 

删除表空间;

droptablespace 表空间名称

19.             新建对象

1>    新建表空间

create tablespace user_space_1 datafile'E:\Test\user_1.dbf' size 20M

--user_space_1表对象名(自定义)'E:\Test\user_1.dbf'存储位置(自定义) 20M表对象大小

--其他均为固定语法

2> 新建用户

create user jy identified by 123456 defaulttablespace user_space_1

--jy(用户名) 123456(密码)user_space_1所属表空间

--其他均为固定于法

3> 为用户赋权限

grant connect,create table,unlimitedtablespance to jy

-- grant dba to jy 一般赋予dba权限

-- grant 权限名称 to 用户名称

4> 移除权限

revoke 权限名称 from 用户名称

20.             直接在windows窗口运行sql文件

start E:\Test\sql.sql

21.             修改表结构

增加列

alter table 表名 add 列名 数据类型 约束

alter table 表名 notity 

 

主外键

创建表时：

1.列名 references 表名(列名)

2.constraint fk_type foreign key(列名)references 表名(列名)

创建临时表

create global temporary table 表名(列名、列名...)(只存活于一次事务之间)

create global temporary table 表名(列名、列名...) oncommit preserve rows(只存活于一次会话之间)

 

伪列

select rowId,product.* from product

select distinct,product.* from product