【转】经典SQL语句(Oracle)

最新推荐文章于 2024-09-20 11:12:39 发布

Mike.Lee

最新推荐文章于 2024-09-20 11:12:39 发布

阅读量716

点赞数

文章标签： oracle sql function hex exception 数据库

查询新建用户

select username
from dba_users
where username not in
      ('TEXT', 'RMAN_USER', 'TEST', 'SCOTT', 'TSMSYS', 'MDDATA', 'DIP',
        'DBSNMP', 'SYSMAN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS',
        'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
        'OLAPSYS', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'OUTLN');

查询那些用户，操纵了那些表造成了锁机
SELECT s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,all_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null
其中 TM 为表锁定 TX 为行锁定

看锁阻塞的方法是
SELECT (select username FROM v$session WHERE sid=a.sid) blocker,
      a.sid,
      'is blocking',
      (select username FROM v$session WHERE sid=b.sid) blockee,
      b.sid
FROM v$lock a, v$lock b
WHERE a.block = 1
AND b.request > 0
AND a.id1 = b.id1
AND a.id2 = b.id2

--各进制之间转换
CREATE OR REPLACE TYPE type_str_agg AS OBJECT
(
total VARCHAR2(4000),

STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
    RETURN NUMBER,

MEMBER FUNCTION odciaggregateiterate
(
    SELF IN OUT type_str_agg,
    VALUE IN VARCHAR2
) RETURN NUMBER,

MEMBER FUNCTION odciaggregateterminate
(
    SELF        IN type_str_agg,
    returnvalue OUT VARCHAR2,
    flags      IN NUMBER
) RETURN NUMBER,

MEMBER FUNCTION odciaggregatemerge
(
    SELF IN OUT type_str_agg,
    ctx2 IN type_str_agg
) RETURN NUMBER
)
/
CREATE OR REPLACE TYPE BODY type_str_agg IS

STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)
    RETURN NUMBER IS
BEGIN
    sctx := type_str_agg(NULL);
    RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregateiterate
(
    SELF IN OUT type_str_agg,
    VALUE IN VARCHAR2
) RETURN NUMBER IS
BEGIN
    SELF.total := SELF.total || VALUE;
    RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregateterminate
(
    SELF        IN type_str_agg,
    returnvalue OUT VARCHAR2,
    flags      IN NUMBER
) RETURN NUMBER IS
BEGIN
    returnvalue := SELF.total;
    RETURN odciconst.success;
END;

MEMBER FUNCTION odciaggregatemerge
(
    SELF IN OUT type_str_agg,
    ctx2 IN type_str_agg
) RETURN NUMBER IS
BEGIN
    SELF.total := SELF.total || ctx2.total;
    RETURN odciconst.success;
END;

END;
/
CREATE OR REPLACE FUNCTION f_stragg(p_input VARCHAR2) RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING type_str_agg;
/
CREATE OR REPLACE PACKAGE pkg_number_trans IS

FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_bin_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_oct_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_oct_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_hex_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_dec_to_oct(p_int IN VARCHAR2) RETURN VARCHAR2;

FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2;

END pkg_number_trans;
/
CREATE OR REPLACE PACKAGE BODY pkg_number_trans IS

FUNCTION f_bin_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_bin_to_oct
    -- 对象描述: 二进制转换八进制
    -- 输入参数: p_str 二进制字符串
    -- 返回结果: 八进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
    -- 备    注: 需要定义f_stragg函数和type_str_agg类型
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
    v_bin    VARCHAR2(4000);
BEGIN
    v_bin := substr('00' || p_str, -3 * ceil(length(p_str) / 3));
    SELECT f_stragg(data1) INTO v_return
      FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3))
                    WHEN '000' THEN '0'
                    WHEN '001' THEN '1'
                    WHEN '010' THEN '2'
                    WHEN '011' THEN '3'
                    WHEN '100' THEN '4'
                    WHEN '101' THEN '5'
                    WHEN '110' THEN '6'
                    WHEN '111' THEN '7'
                  END) data1
              FROM dual
            CONNECT BY rownum <= length(v_bin) / 3);
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_bin_to_oct;

FUNCTION f_bin_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_bin_to_dec
    -- 对象描述: 二进制转换十进制
    -- 输入参数: p_str 二进制字符串
    -- 返回结果: 十进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_bin_to_dec('11110001010') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
BEGIN
    SELECT SUM(data1) INTO v_return
      FROM (SELECT substr(p_str, rownum, 1) * power(2, length(p_str) - rownum) data1
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_bin_to_dec;

FUNCTION f_bin_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_bin_to_hex
    -- 对象描述: 二进制转换十六进制
    -- 输入参数: p_str 二进制字符串
    -- 返回结果: 十六进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_bin_to_oct('11110001010') FROM dual;
    -- 备    注: 需要定义f_stragg函数和type_str_agg类型
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
    v_bin    VARCHAR2(4000);
BEGIN
    v_bin := substr('000' || p_str, -4 * ceil(length(p_str) / 4));
    SELECT f_stragg(data1) INTO v_return
      FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 4 + 1, 4))
                    WHEN '0000' THEN '0'
                    WHEN '0001' THEN '1'
                    WHEN '0010' THEN '2'
                    WHEN '0011' THEN '3'
                    WHEN '0100' THEN '4'
                    WHEN '0101' THEN '5'
                    WHEN '0110' THEN '6'
                    WHEN '0111' THEN '7'
                    WHEN '1000' THEN '8'
                    WHEN '1001' THEN '9'
                    WHEN '1010' THEN 'A'
                    WHEN '1011' THEN 'B'
                    WHEN '1100' THEN 'C'
                    WHEN '1101' THEN 'D'
                    WHEN '1110' THEN 'E'
                    WHEN '1111' THEN 'F'
                  END) data1
              FROM dual
            CONNECT BY rownum <= length(v_bin) / 4);
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_bin_to_hex;

FUNCTION f_oct_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_oct_to_bin
    -- 对象描述: 八进制转换二进制
    -- 输入参数: p_str 八进制字符串
    -- 返回结果: 二进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_oct_to_bin('3612') FROM dual;
    -- 备    注: 需要定义f_stragg函数和type_str_agg类型
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
BEGIN
    SELECT to_char(to_number(f_stragg(data1))) INTO v_return
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
                    WHEN '0' THEN '000'
                    WHEN '1' THEN '001'
                    WHEN '2' THEN '010'
                    WHEN '3' THEN '011'
                    WHEN '4' THEN '100'
                    WHEN '5' THEN '101'
                    WHEN '6' THEN '110'
                    WHEN '7' THEN '111'
                  END) data1
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_oct_to_bin;

FUNCTION f_oct_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_oct_to_dec
    -- 对象描述: 八进制转换十进制
    -- 输入参数: p_str 八进制字符串
    -- 返回结果: 十进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_oct_to_dec('3612') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
BEGIN
    SELECT SUM(data1) INTO v_return
      FROM (SELECT substr(p_str, rownum, 1) * power(8, length(p_str) - rownum) data1
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_oct_to_dec;

FUNCTION f_oct_to_hex(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_oct_to_bin
    -- 对象描述: 八进制转换十六进制
    -- 输入参数: p_str 八进制字符串
    -- 返回结果: 十六进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_oct_to_hex('3612') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
    v_bin    VARCHAR2(4000);
BEGIN
    SELECT pkg_number_trans.f_oct_to_bin(p_str) INTO v_bin FROM dual;
    SELECT pkg_number_trans.f_bin_to_hex(v_bin) INTO v_return FROM dual;
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_oct_to_hex;

FUNCTION f_dec_to_bin(p_int IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_dec_to_bin
    -- 对象描述: 十进制转换二进制
    -- 输入参数: p_str 十进制字符串
    -- 返回结果: 二进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_dec_to_bin('1930') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
    v_hex    VARCHAR2(4000);
BEGIN
    SELECT pkg_number_trans.f_dec_to_hex(p_int) INTO v_hex FROM dual;
    SELECT pkg_number_trans.f_hex_to_bin(v_hex) INTO v_return FROM dual;
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_dec_to_bin;

FUNCTION f_dec_to_oct(p_int IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_dec_to_oct
    -- 对象描述: 十进制转换八进制
    -- 输入参数: p_str 十进制字符串
    -- 返回结果: 八进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_dec_to_oct('1930') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
    v_bin    VARCHAR2(4000);
BEGIN
    SELECT pkg_number_trans.f_dec_to_bin(p_int) INTO v_bin FROM dual;
    v_bin := substr('00' || v_bin, -3 * ceil(length(v_bin) / 3));
    SELECT f_stragg(data1) INTO v_return
      FROM (SELECT (CASE upper(substr(v_bin, (rownum - 1) * 3 + 1, 3))
                    WHEN '000' THEN '0'
                    WHEN '001' THEN '1'
                    WHEN '010' THEN '2'
                    WHEN '011' THEN '3'
                    WHEN '100' THEN '4'
                    WHEN '101' THEN '5'
                    WHEN '110' THEN '6'
                    WHEN '111' THEN '7'
                  END) data1
              FROM dual
            CONNECT BY rownum <= length(v_bin) / 3);
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_dec_to_oct;

FUNCTION f_dec_to_hex(p_int IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_dec_to_oct
    -- 对象描述: 十进制转换十六进制
    -- 输入参数: p_str 十进制字符串
    -- 返回结果: 十六进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_dec_to_hex('1930') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
BEGIN
    SELECT upper(TRIM(to_char(p_int, 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'))) INTO v_return FROM dual;
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_dec_to_hex;

FUNCTION f_hex_to_bin(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_dec_to_oct
    -- 对象描述: 十六进制转换二进制
    -- 输入参数: p_str 十六进制字符串
    -- 返回结果: 二进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
BEGIN
    SELECT to_char(to_number(f_stragg(data1))) INTO v_return
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
                    WHEN '0' THEN '0000'
                    WHEN '1' THEN '0001'
                    WHEN '2' THEN '0010'
                    WHEN '3' THEN '0011'
                    WHEN '4' THEN '0100'
                    WHEN '5' THEN '0101'
                    WHEN '6' THEN '0110'
                    WHEN '7' THEN '0111'
                    WHEN '8' THEN '1000'
                    WHEN '9' THEN '1001'
                    WHEN 'A' THEN '1010'
                    WHEN 'B' THEN '1011'
                    WHEN 'C' THEN '1100'
                    WHEN 'D' THEN '1101'
                    WHEN 'E' THEN '1110'
                    WHEN 'F' THEN '1111'
                  END) data1
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_hex_to_bin;

FUNCTION f_hex_to_oct(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_dec_to_oct
    -- 对象描述: 十六进制转换八进制
    -- 输入参数: p_str 十六进制字符串
    -- 返回结果: 八进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_hex_to_oct('78A') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
    v_bin    VARCHAR2(4000);
BEGIN
    SELECT pkg_number_trans.f_hex_to_bin(p_str) INTO v_bin FROM dual;
    SELECT pkg_number_trans.f_bin_to_oct(v_bin) INTO v_return FROM dual;
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_hex_to_oct;

FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_hex_to_dec
    -- 对象描述: 十六进制转换十进制
    -- 输入参数: p_str 十六进制字符串
    -- 返回结果: 十进制字符串
    -- 测试用例: SELECT pkg_number_trans.f_hex_to_dec('78A') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return VARCHAR2(4000);
BEGIN
    SELECT SUM(data1) INTO v_return
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
                    WHEN 'A' THEN '10'
                    WHEN 'B' THEN '11'
                    WHEN 'C' THEN '12'
                    WHEN 'D' THEN '13'
                    WHEN 'E' THEN '14'
                    WHEN 'F' THEN '15'
                    ELSE substr(p_str, rownum, 1)
                  END) * power(16, length(p_str) - rownum) data1
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
END f_hex_to_dec;

END pkg_number_trans;
/

获取系统信息：
select
          SYS_CONTEXT('USERENV','TERMINAL') terminal,
          SYS_CONTEXT('USERENV','LANGUAGE') language,
          SYS_CONTEXT('USERENV','SESSIONID') sessionid,
          SYS_CONTEXT('USERENV','INSTANCE') instance,
          SYS_CONTEXT('USERENV','ENTRYID') entryid,
          SYS_CONTEXT('USERENV','ISDBA') isdba,
          SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
          SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
          SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
          SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
          SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
          SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
          SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
          SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
          SYS_CONTEXT('USERENV','SESSION_USER') session_user,
          SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
          SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
          SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
          SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
          SYS_CONTEXT('USERENV','DB_NAME') db_name,
          SYS_CONTEXT('USERENV','HOST') host,
          SYS_CONTEXT('USERENV','OS_USER') os_user,
          SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
          SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
          SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
          SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
          SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
          SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
authentication_type,
          SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')
authentication_data
from dual

Oracle计算时间差表达式 --获取两时间的相差豪秒数 select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 FROM DUAL; /* 相差豪秒数 ---------- 86401000 1 row selected */ --获取两时间的相差秒数 select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL; /* 相差秒数 ---------- 86401 1 row selected */ --获取两时间的相差分钟数 select ceil(((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss'))) * 24 * 60) 相差分钟数 FROM DUAL; /* 相差分钟数 ---------- 1441 1 row selected */ --获取两时间的相差小时数 select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24) 相差小时数 FROM DUAL; /* 相差小时数 ---------- 25 1 row selected */ --获取两时间的相差天数 select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss'))) 相差天数 FROM DUAL; /* 相差天数 ---------- 2 1 row selected */ --获取两时间月份差 select (EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd'))) * 12 + EXTRACT(month FROM to_date('2008-05-01','yyyy-mm-dd')) - EXTRACT(month FROM to_date('2008-04-30','yyyy-mm-dd')) months from dual; /* MONTHS ---------- 13 1 row selected */ --获取两时间年份差 select EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd')) years from dual; /* YEARS ---------- 1 1 row selected */

--Insert multiple rows into different tables with a single statement: INSERT ALL WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val) WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val) WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val) ELSE INTO tab4 VALUES (myseq.NEXTVAL, val) SELECT type, val FROM source_tab;
1、跟踪当前对话下用户的SQL脚本

select sql_text from v$sqltext_with_newlines where (hash_value,address)
in (select sql_hash_value,sql_address from v$session where sid=&sid)
order by address,piece;

SID
由这得到
select sid,machine from v$session;
手动创建数据库方法
在WINDOWS NT/2000下手动创建数据库
我机子里已经有一个数据库为sjpdb,现在要手动再建第二个数据库sjpdb02 步骤如下：
步骤一：确定数据库名实例名服务名
步骤二：编辑修改参数文件
步骤三：创建数据库实例
步骤四：启动新创建的实例，创建数据库
步骤五：创建数据字典
步骤六：网络配置即配置监听器(lisener.ora)和网络连接解析配置（tnsnames.ora）
具体步骤如下：

步骤一：确定数据库名实例名服务名

因为我是在自己机子上创建DB 所以域名取为空，所以服务名和数据库名相同参数格式如下：
db_name=sjpdb02
instance_name=sjp02
db_domain=""
service_names=sjpdb02

步骤二：编辑修改参数文件

从其他WINDOWS NT/2000的数据库ORACLE 9I中拷贝一个参数文件作为基础修改。一般先要修改以下一些参数其他参数在数据库创建后再修改。

我的ORACLE 安装目录为F盘根目录下
db_name=sjpdb02
insance_name=sjpdb02
db_domain=
service_names=sjpdb02
control_files=("F:/oracle/oradata/sjpdb02/CONTROL01.CTL", "F:/oracle/oradata/sjpdb02/CONTROL02.CTL",

"F:/oracle/oradata/sjpdb02/CONTROL03.CTL")
db_block_size=4096
background_dump_dest=F:/oracle/admin/sjpdb02/bdump
core_dump_dest=F:/oracle/admin/sjpdb02/cdump
user_dump_dest=F:/oracle/admin/sjpdb02/udump
修改完后保存,文件名为init.ora保存路径
f:/oracle/admin/sjpdb02/pfile/init.ora

步骤三：创建数据库实例

    创建数据库实例的原因是数据库启动时首先要启动实例，若对应此数据库的实例启动失败或每没启动，则数据库不能正常启动发生协议适

配器错误，数据库实例在WINDOW下就是服务，本步骤要利用创建数据库实例的程序Oradim.exe创建实例OracleServiceSJPDB02。首先要在
F:/oracle/ora90/database下创建initsjpdb02.ora文件，因为用oradim命令创建服务时要用到参数文件，initsjpdb02.ora文件内容为
IFILE='F:/ORACLE/ADMIN/SJPDB02/PFILE/INIT.ORA' 这句话实际上是对上面我们建的参数文件的一个引用。
使用ORAPWD命令创建数据库中具有SYSDBA权限的口令字文件，该用户可以启动和关闭数据库，此文件保存目录：
f:/oracle/ora90/database/PWDsjpdb02.ora
创建口令字方法为：
C:/>rapwd file=f:/oracle/ora90/database/PWDsjpdb02.ora password=sjp entries=30
使用ORADIM命令创建数据库实例
c:/>set oracle_sid=sjpdb02    在这之前ORACLE_SID=SJPDB
C:/>ORADIM -NEW -SID SJPDB02 -STARTMODE M -PFILE F:/oracle/ora90/database/initsjpdb02.ora 启动模式为手动
创建成功后系统自动在WINDOWS的服务表中增加一个新的服务“OracleServiceSJPDB02” 并且自动启动了，虽然启动模式为手动。
其实创建口令字过程可以添在创建实例语句中两个参数－intpwd sjp -maxusers 30

步骤四：启动新创建的实例，创建数据库步骤五：创建数据字典这两个步骤用一个脚本实现

在创建数据库之前要以新的参数文件启动数据库实例，即nomount数据库，因没有控制文件还不能MOUNT或OPEN
c:/>sqlplus "/as sysdba"
SQL>startup nomount
ORACLE 例程已经启动。

Total System Global Area 118255568 bytes
Fixed Size                  282576 bytes
Variable Size              83886080 bytes
Database Buffers          33554432 bytes
Redo Buffers                532480 bytes
若数据库参数文件不在F:/oracle/ora90/database下，或参数文件名称没有按initSID.ora取名，则启动实例时要加参数PFILE指出参数文件路

径，例SQL>startup pfile=路径/参数文件名 nomount
运行脚本文件名为createdb.sql 其中文件最后是加载一些程序包以创建数据字典
SQL>存放路径/createdb.sql
createdb.sql文件内容为：

create database sjpdb02
logfile
      group 1 ('f:/oracle/oradata/sjpdb02/redo01a.log',
              'f:/oracle/oradata/sjpdb02/redo01b.log') size 5M,
      group 2 ('f:/oracle/oradata/sjpdb02/redo02a.log',
              'f:/oracle/oradata/sjpdb02/redo02b.log') size 5M,
      group 3 ('f:/oracle/oradata/sjpdb02/redo03a.log',
              'f:/oracle/oradata/sjpdb02/redo03b.log') size 5M
maxlogfiles 32
maxlogmembers 3
maxloghistory 1
datafile 'f:/oracle/oradata/sjpdb02/system01.dbf' size 100M
autoextend on next 512k maxsize unlimited
maxdatafiles 254
maxinstances 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET UTF8;

alter tablespace system default storage
(initial 64K minextents 1
maxextents unlimited pctincrease 50);
alter tablespace system minimum extent 64k;

create undo tablespace undotbs
datafile 'f:/oracle/oradata/sjpdb02/undotbs01.dbf' size 50M
autoextend on next 512k
extent management local;
create temporary tablespace temp
Tempfile 'f:/oracle/oradata/sjpdb02/temp01.dbf' size 32M
autoextend on next 512k
extent management local;

create tablespace tools
datafile 'f:/oracle/oradata/sjpdb02/tools01.dbf' size 32M
autoextend on next 512k
extent management dictionary
minimum extent 32k
default storage(initial 32k next 32k minextents 1
maxextents 4096 pctincrease 0);

create tablespace indx
datafile 'f:/oracle/oradata/sjpdb02/indx01.dbf' size 32M
autoextend on next 512k
minimum extent 128k
default storage(initial 128k next 128k minextents 1
maxextents 4096 pctincrease 0);

create tablespace users
datafile 'f:/oracle/oradata/sjpdb02/users01.dbf' size 20M
autoextend on next 512k
extent management dictionary
minimum extent 128k
default storage(initial 128k next 128k minextents 1
maxextents 4096 pctincrease 0);

@f:/oracle/ora90/rdbms/admin/catalog.sql
@f:/oracle/ora90/rdbms/admin/catproc.sql
@f:/oracle/ora90/rdbms/admin/catexp7.sql
@f:/oracle/ora90/rdbms/admin/catrep.sql
@f:/oracle/ora90/rdbms/admin/caths.sql
connect system/manager
@f:/oracle/ora90/sqlplus/admin/pupbld.sql

步骤六：网络配置即配置监听器(lisener.ora)和网络连接解析配置（tnsnames.ora）

可以使用NET MANAGER来配置
以工作分组，按名称排序，取出汇总每个工作下的人员名称，并以“,”符号作为分隔符

SQL code

    select job, ltrim(sys_connect_by_path(ename, ','), ',') scbp from (select job, ename, row_number() over(partition by job order by ename) rn, count(*) over(partition by job) cnt from scott.emp) t where rn = cnt start with rn = 1 connect by prior job = job and prior rn = rn - 1 order by job

JOB      SCBP
--------- --------------------------------------------------------------------------------
ANALYST FORD,SCOTT
CLERK    ADAMS,JAMES,MILLER,SMITH
MANAGER BLAKE,CLARK,JONES
PRESIDENT KING
SALESMAN ALLEN,MARTIN,TURNER,WARD

如何查找、删除表中重复的记录

方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，
　 rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。

2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中
　那些具有最大rowid的就可以了，其余全部删除。

实现方法：
SQL> create table a (
2 bm char(4), --编码
3 mc varchar2(20) --名称
4 )
5 /

表已建立.

SQL> insert into a values('1111','1111');
SQL> insert into a values('1112','1111');
SQL> insert into a values('1113','1111');
SQL> insert into a values('1114','1111');

SQL> insert into a select * from a;

插入4个记录.

SQL> commit;

完全提交.

SQL> select rowid,bm,mc from a;

ROWID              BM MC
------------------ ---- -------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111

查询到8记录.

查出重复记录
SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

ROWID              BM MC
------------------ ---- --------------------
000000D5.0000.0002 1111 1111
000000D5.0001.0002 1112 1111
000000D5.0002.0002 1113 1111
000000D5.0003.0002 1114 1111

删除重复记录
SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc);

删除4个记录.

SQL> select rowid,bm,mc from a;

ROWID              BM MC
------------------ ---- --------------------
000000D5.0004.0002 1111 1111
000000D5.0005.0002 1112 1111
000000D5.0006.0002 1113 1111
000000D5.0007.0002 1114 1111

/*查询指定表约束*/
sp_helpconstraint emp
/*执行指定的SQL指令*/
sp_executesql N'select * from emp'
/*N表示后面的字符串采用unicode编码体系（无论中英文均一字符双字节）*/
/*模糊查询like*/
select * from emp where eid not like '[0-8][0-8][0-12]'
/*中括号内代表‘一’位字符的范围，[0-12]即[0-1]||[0-2]*/
select * from emp where ename like 'w_'      下划线
/*"_"通配符，代表所有字符，就是麻将里的“混”*/
select * from emp where ename like 'w%'
/*"%"代表任意位的任意字符*/
select * from emp where not ename='we'
/*not不等于*/
select * from emp where ename is null
/*is null不是＝null！*/
select * from emp where ename is not null
/*is not null不是not is null*/
select * from emp where eid not in (2002)
/*in 包含于*/
select * from emp where eid between 1001 and 1002
/*between 在两值之间的范围内取值*/

desc table;检查表结构
select * from tab where tabtype='TABLE';显示当前用户下的所有表。
select count(*) from table;显示此表的数据行数；
spool c:/tony.txt;日记路径
spool off;关闭记录后可以看到日记文件里的内容。
alter table stu add(classid number(2));添加字段
alter table stu modify(xm varchar2(12));修改字段的长度
alter table stu drop column sal;
drop table stu;
rename student to stu;
alter table student drop column sal; alter table stu add(salary number(7,2));
insert into stu values('A001','张三','男','01-5月-05',10);
insert into stu(xh,xm,sex) values ('A003','JOHN','女');
insert into student(xh,xm,sex,birthday) values ('A004','MARTIN','男',null);
修改
update
update stu set sex='女' where xh='A001';
update student set sex='男'，birthday='1980-04-01'where xh='A001';
update student set classid=20 where birthday is null;
delete from stu;drop table student;delete from stu where xh='A001';
truncate table stu;删除表中的所有记录，表结构还在不写日记无法找回记录
select * from stu;
select * from student where classid like '1%';
select * from student where xh like '%A%';
select * from student where xh like 'A%';
select * from student where xh like '%A';
select * from student where xh = 'A%';
select * from student order by birthday;
select * from student order by birthday desc,xh asc; --按birthday 降序按xh升序(asc/默认)
select * from student where sex='女' or birthday='1999-02-01';
select * from student where sex='女' and birthday='1999-02-01';
select * from student where salary > 20 and xh <> 'B002'; (!=)
oracle
函数的学习
单行函数返回值只有一个
分组函数返回值是多条记录
      group by
      sum
      avg
select sysdate from dual;dual哑元素没有表需要查询的时候
select xm||'-----'||classid from stu;
select 1+1 from dual;
select job,next_date,next_sec,failures,broken from user_jobs;
show parameter job_queue_processes;
alter system set job_queue_processes=5;
select owner,object_name from dba_objects where object_type='DATABASE LINK';
select * from dual@gis3; 判断是否创建
select object_name,object_type
    from user_objects
    order by object_type; --用户所拥有的对象

    select object_name,object_type
    from user_objects
    WHERE object_type='TABLE'; --用户所拥有的表
另外一种方法
select table_name from user_tables;

    select object_name,object_type
    from user_objects
    WHERE object_type='INDEX'; --用户所拥有的索引
INSERT INTO Store_Information (store_name, Sales, Date)
      SELECT store_name, Sales, Date
      FROM Sales_Information
        WHERE to_char(date,'yyyy')=1998;
exp/imp help=y 显示exp/imp命令下的参数
imp tony/gisinfo file=tony.dmp log=loga show=y full=y
if--then--end if的使用if v_test=1 then begin ..... end; end if;
while--loop--end loop的使用if v_test=1 then begin ........end;end if;
CASE的使用
update jzqld1 set bcrq=case
when id like '3%' then '09010102'
when id like '4%' then '09010204'
else '09010805'
end;
删除用户drop user tony/create user tony
showdown abort; startup;
希望在scott用户下能看到mk用户中test表的数据???
connect mk/m123
grant select on test to scott; //授权scott能select
connect scott/tiger
select * from mk.test;

create synonym mtest for mk.test; //为表建立的
                      //可以为任何对象建立同义词
select * from mtest; //相当于select * from mk.test;
位图索引
      create bitmap index ind_aa on emp(job);
create index ind_dept on dept(dname,loc);
      //联合索引分次序的
      create index ind_dept1 on dept(loc,dname);
show parameter db_name数据库名

DBA要及时查看数据库中数据文件的状态(如被误删除)，根据实际情况决定如何进行处理，检查数据文件的状态的SQL如下:

select file_name,status from dba_data_files;

如果数据文件的STATUS列不是AVAILABLE，那么就要采取相应的措施，如对该数据文件进行恢复操作，
或重建该数据文件所在的表空间。
//给用户授予权限

grant connect,dba,resource to testserver_user;

//创建用户并指定表空间
create user ORACLE8I identified by ORACLE8I
default tablespace ORACLE8I
temporary tablespace test_temp ;

//创建数据表空间
create tablespace oracle8i
logging
datafile 'E:/oracle/product/10.2.0/oradata/testserver/test_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建临时表空间

create temporary tablespace test_temp
tempfile 'E:/oracle/product/10.2.0/oradata/testserver/test_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;
表锁 : lock table emp in exclusive mode;
      对emp表加的表锁
      一般不使用表锁;
alter table aa move tablespace bb

取oracle 字段及其备注等。举例CALL getfd('TABLENAME',4,'--',0);

create or replace procedure GETFD(p_TableName in varchar2,iMode in number, x in varchar2,Cols in number)
AS
CURSOR c_Col IS
    select TABLE_NAME,COLUMN_NAME,COMMENTS from user_col_comments
    where upper(TABLE_NAME) =upper(p_TableName)
    order by COLUMN_NAME;

v_Col c_Col%ROWTYPE;
v_cols varchar2(20000);
v_Start varchar2(20);--开始符
v_end varchar2(20);--结束符

i      number(8);
iCount number(8);
BEGIN

v_cols:=' ';
i:=0;
iCount:=5;
v_end:='," +';
v_Start:='        " ';
if Cols>0 then
iCount:=Cols;
end if;
Open c_Col;
Fetch c_Col into v_Col;

while c_Col%Found Loop

    if imode=0 then -- x0,x1,x2,x3
      if v_cols <>' ' then
        v_cols := v_cols||','||v_Col.COLUMN_NAME;
      end if;
      if v_cols=' ' then
        v_cols:=v_Start||v_Col.COLUMN_NAME;--||',';
      end if;
    end if;

    if imode=1 then -- a.x0,a.x1,a.x2,a.x3
      if v_cols <>' ' then
        v_cols := v_cols||','||x||'.'||v_Col.COLUMN_NAME;
      end if;
      if v_cols=' ' then
        v_cols:=v_Start||x||'.'||v_Col.COLUMN_NAME;
      end if;
    end if;

    if imode=2 then -- x0?,x1?
      if v_cols <>' ' then
        v_cols := v_cols||'?,'||v_Col.COLUMN_NAME;
      end if;

      if v_cols=' ' then
        v_cols:=v_Start||v_Col.COLUMN_NAME;
      end if;
    end if;

    if imode=3 then -- x0=x0?,x1=x1?
      if v_cols <>' ' then
        v_cols := v_cols||'?,'||v_Col.COLUMN_NAME||'='||v_Col.COLUMN_NAME;
      end if;

      if v_cols=' ' then
        v_cols:=v_Start||v_Col.COLUMN_NAME||'='||v_Col.COLUMN_NAME;
      end if;
    end if;

    if imode=4 then -- delphi 注释 //x0
        v_cols:=x||v_Col.column_name||' - '||v_Col.comments;
        dbms_output.put_line(v_cols);
        v_cols:=' ';
    end if;

    i:=i+1;

    Fetch c_Col into v_Col;
    if i>iCount then
      if imode in(2,3) then
      v_cols:=v_cols||'?';
      end if;
      if imode <>4 then
        v_cols:=v_cols||v_end;
        dbms_output.put_line(v_cols);
      end if;
      v_cols:=' ';
      i:=0;
    end if;

end Loop;
Close c_Col;

if imode in(2,3) then
    v_cols:=v_cols||'?';
end if;

if v_cols <>' ' then
    v_cols:=v_cols||v_end;
    dbms_output.put_line(v_cols);
end if;
END;

分析sql语句的执行效率
1.先建一个表：
CREATE TABLE "PLAN_TABLE"
      ("STATEMENT_ID" VARCHAR2(30),
      "TIMESTAMP" DATE,
      "REMARKS" VARCHAR2(80),
      "OPERATION" VARCHAR2(30),
      "OPTIONS" VARCHAR2(30),
      "OBJECT_NODE" VARCHAR2(128),
      "OBJECT_OWNER" VARCHAR2(30),
      "OBJECT_NAME" VARCHAR2(30),
      "OBJECT_INSTANCE" FLOAT,
      "OBJECT_TYPE" VARCHAR2(30),
      "OPTIMIZER" VARCHAR2(255),
      "SEARCH_COLUMNS" FLOAT,
      "ID" FLOAT,
      "PARENT_ID" FLOAT,
      "POSITION" FLOAT,
      "OTHER" LONG) ;

2.把以下内容存一文件中，需要时调出来填上要分析的语句执行它：
explain plan set statement_id = 'Allan' into plan_table for

/*此处放上要分析的sql语句*/

select lpad(' ',3*(level - 1))||operation||' '||options "query plan",object_name//,position
from plan_table
start with id = 0 and statement_id = 'Allan'
connect by prior id = parent_id
and statement_id = statement_id ;

-外部表的使用

1.备份数据表

CREATE TABLE HKH_LT ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY hkh_import
LOCATION ('HKH.DMP')
)
AS SELECT * FROM HKH_厦门8月未拨打

2.导文本文件到ORACLE

CREATE TABLE HKH_厦门8月未拨打
(
PHONE_NUM VARCHAR2(11)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY TEMP
ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',')
LOCATION('HKH_181.TXT')
);

--SQL LOADER 命令应用

一.将EXCEL导入到oracle

1.创建表结构

CREATE TABLE HKH_LM
(
ACCT_MONTH VARCHAR2(8),
PHONE_NUM VARCHAR2(11),
PLAN_PRODUCT VARCHAR2(20)
)

2.编写控制文件
load data
infile 'D:/TEMP/联秘9.8号无效号码.CSV'
append into table hkh_lm
fields terminated by ","
(ACCT_MONTH,PHONE_NUM,PLAN_PRODUCT)

--查询用户表空间
select username,default_tablespace from user_users;

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表：
CREATE TABLE TBL_TEST
(
ID    NUMBER,
NAME VARCHAR2(100 BYTE),
PID NUMBER                                  DEFAULT 0
);

插入测试数据：
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');

从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid

从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id

显示叶子结点和路径
SELECT A.*,CONNECT_BY_ISLEAF"ISLEAF",SYS_CONNECT_BY_PATH( ID, '/') "Path"
FROM TBL_TEST A
WHERE CONNECT_BY_ISLEAF = 1
start with A.ID='1' connect by prior A.ID = A.PID