Oracle中大字容量字段

LONG
可变长的字符串数据,最长2G,LONG具有VARCHAR2列的特性,可以存储长文本一个。
表中最多一个LONG列

LONG RAW
可变长二进制数据,最长2G

CLOB
字符大对象Clob 用来存储单字节的字符数据

NCLOB
用来存储多字节的字符数据

BLOB
用于存储二进制数据

BFILE
存储在文件中的二进制数据,这个文件中的数据只能被只读访。但该文件不包含在数据库内。
bfile字段实际的文件存储在文件系统中,字段中存储的是文件定位指针。
bfile对oracle来说是只读的,也不参与事务性控制和数据恢复。
  
其中CLOB,NCLOB,BLOB都是内部的LOB(Large Object)类型,最长4G,没有LONG只能有一列的限制。

Oracle ORA-01461

昨天做联调时,在10g上测试一点儿问题都没有,但切到9i的库上,却直接报错:

ORA-01461:仅可以为插入 LONG 列的 LONG 值赋值

查找资料后发现,是Oracle9i的一个bug,当Clob字段长度在1000~2000之间时,
就会出现这个错误。

解决方法有两个:
1、替换驱动为神奇的版本ojdbc14-10.2.0.3.0.jar,可以避免这个问题。
2、利用Spring+Hibernate解决这个问题

EventSendInfo.hbm.xml

-<property name="MSG" type="java.lang.String">
+<property name="MSG" type="org.springframework.orm.hibernate3.support.ClobStringType">

applicationContext.xml

...
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
...
+<property  name="lobHandler"  ref="oracleLobHandler"/> 
</bean>

...
+<bean id ="oracleLobHandler" class ="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init ="true">  
+<property name ="nativeJdbcExtractor"  ref ="nativeJdbcExtractor"/>
+</bean >     
+<bean id ="nativeJdbcExtractor" class ="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" lazy-init="true"/>

...

Oracle的DBF文件减肥

使用OB9,DBA身份登录:
表空间信息-》双击需要RESIZE的表空间-》定义信息-》修改大小后-》创建

或者用SQL:

--枚举DBF文件
SELECT * FROM dba_data_files
--缩小文件
ALTER DATABASE DATAFILE 'DBF_FILE_PATH' RESIZE 512m

常见错误:
ORA-03297: 文件包含在请求的RESIZE值以外使用的数据
这个错误产生的原因是,虽然DBF文件使用率很低,但一些数据存储在了RESIZE值以外,
无法直接进行缩小DBF文件的操作。

要么把数据导出,缩小DBF文件后倒入,再倒入;
要么需要查出哪些表和索引在RESIZE值以外,移动到临时空间后,进行RESIZE操作,再移动回来。

Oracle回收站

Oracle10g中,经常可以看到BIN$开头的表,这些表是在回收站中的。
回收站常用命令:

--查询回收站
SELECT t.object_name,t.type ,t.original_name FROM user_recyclebin t;

--清空回收站
PURGE recyclebin;
--删除回收站中表,使用删除前表名
PURGE table origenal_tableName;
--删除回收站中索引,使用删除前索引名
PURGE index origenal_indexName;

--彻底删除一个表
drop table tableName purge;

Oracle游标

游标处理数据范例(在原示例上有删减):

CREATE OR REPLACE PROCEDURE ATS_GUEST.GATETOSERVER
(
  HOSPITAL_DOMAIN             IN VARCHAR2,
  START_DATE                  IN VARCHAR2,
  END_DATE                    IN VARCHAR2
)
AS
PARM_SQL                      VARCHAR2(4000);
PARM_BODY_PART                VARCHAR2(256);
PARM_METHOD_CODE              VARCHAR2(256);
PARM_REPEAT_NUMBER            VARCHAR2(256);
PARM_MACHINE_NAME             VARCHAR2(256);
PARM_DEVICE_NAME              VARCHAR2(256);
PARM_STUDY_UID                VARCHAR2(256);
PARM_UNIQUE_ID                VARCHAR2(256);
PARM_DOC_ROOT                 VARCHAR2(256);

PARM_CURSOR                   SYS_REFCURSOR;

BEGIN

    IF (HOSPITAL_DOMAIN ='2.16.840.1.113883.4.487.1.4.1') THEN

    PARM_SQL:= 'SELECT CDA_UNIQUE_ID,DOC_AUTHORITY_ROOT,DICOM_BODY_PART,DIAGNOSIS_METHOD_CODE,REPEAT_NUMBER,MACHINE_ROOM_NAME,DEVICE_NAME,DICOM_STUDY_UID FROM ADGATE.REPORTDOC@GATETOSERVER WHERE DOC_AUTHORITY_ROOT=''' || HOSPITAL_DOMAIN || '''';

    END IF;

    PARM_SQL:= PARM_SQL || ' AND EFFECTIVE_TIME > TO_DATE(''' || START_DATE || ''',''yyyymmddhh24miss'') AND EFFECTIVE_TIME < TO_DATE(''' || END_DATE || ''',''yyyymmddhh24miss'')';

    DBMS_OUTPUT.PUT_LINE(PARM_SQL);

    OPEN PARM_CURSOR FOR PARM_SQL;

    LOOP

    FETCH PARM_CURSOR INTO PARM_UNIQUE_ID,PARM_DOC_ROOT,PARM_BODY_PART,PARM_METHOD_CODE,PARM_REPEAT_NUMBER,PARM_MACHINE_NAME,PARM_DEVICE_NAME,PARM_STUDY_UID;

    PARM_SQL:= 'UPDATE AXDS.DOCUMENTSCATTER SET BODY_PART = ''' || PARM_BODY_PART || ''',';

    PARM_SQL:= PARM_SQL || 'DIAGNOSIS_METHOD_CODE = ''' || PARM_METHOD_CODE || ''',';

    PARM_SQL:= PARM_SQL || 'REPEAT_NUMBER = ''' || PARM_REPEAT_NUMBER || ''',';

    PARM_SQL:= PARM_SQL || 'MACHINE_ROOM_NAME = ''' || PARM_MACHINE_NAME || ''',';

    PARM_SQL:= PARM_SQL || 'DEVICE_NAME = ''' || PARM_DEVICE_NAME || ''',';

    PARM_SQL:= PARM_SQL || 'STUDY_UID = ''' || PARM_STUDY_UID || ''',';

    PARM_SQL:= PARM_SQL || 'CUSTOM1 = ''TEST''';

    PARM_SQL:= PARM_SQL || ' WHERE AXDS.DOCUMENTSCATTER.CDA_UNIQUE_ID = ''' || PARM_UNIQUE_ID || ''' AND AXDS.DOCUMENTSCATTER.DOC_AUTHORITY_ROOT = ''' || PARM_DOC_ROOT || '''';

    --next line will break this PROCEDURE 
    --DBMS_OUTPUT.PUT_LINE(PARM_SQL);

    EXECUTE IMMEDIATE PARM_SQL;

    COMMIT;

    IF (HOSPITAL_DOMAIN ='2.16.840.1.113883.4.487.1.4.1') THEN

    PARM_SQL:= 'UPDATE ADGATE.REPORTDOC@GATETOSERVER SET CUSTOM_5 = ''TEST'' WHERE ADGATE.REPORTDOC.CDA_UNIQUE_ID = ''' || PARM_UNIQUE_ID || ''' AND ADGATE.REPORTDOC.DOC_AUTHORITY_ROOT = ''' || PARM_DOC_ROOT || '''';

    EXECUTE IMMEDIATE PARM_SQL;

    COMMIT;
 
    END IF;

    EXIT WHEN PARM_CURSOR%NOTFOUND;

    END LOOP;

    CLOSE PARM_CURSOR;

    EXCEPTION

    WHEN OTHERS THEN

    NULL;

END;

Oracle、SQL Server、MySQL的语句变化总结

调整内容 Oracle SQL Server MySQL
数据类型:字符 VARCHAR2 NVARCHAR
数据类型:数字 NUMBER tinyint,smallint,int,bigint
数据类型:时间 TIMESTAMP DATATIME
列自增 sqeuence.nextval identity(1,1) identity(1,1)
约束主键 CONSTRAINT 表名 PRIMARY KEY (列名) USING INDE PRIMARY KEY
约束唯一 CONSTRAINT 表名 UNIQUE (列名) USING INDEX UNIQUE INDEX
注释 commnet sp_addextendedproperty
函数时间 sysdate getdate()
查询分页 rownum top limit
查询跨库 库名.表名 库名.dbo.表名 limit
执行存储过程 call exec

Oracle Job 101

1、新建一个存储过程

create or replace procedure p_insert_into_t1
as
begin
     insert into t1
     (select * from t where STATUS=1);
end;

2、新建一个作业

variable job_abc number;
begin
      sys.dbms_job.submit(:job_abc,
                         'p_insert_into_t1;',
                          sysdate,
                         'sysdate+1/1440');
       commit;
end; 

其中,
参数1表示作业名字,参数2表示执行的存储过程,
参数3表示开始执行的时间,参数4表示执行的时间间隔

commit表示立即开始执行

执行成功后,返回job的ID

3、查询作业

select job,
       log_user,
       to_char(last_date,'yyyy-mm-dd hh24:mi:ss') last_date,
       to_char(next_date,'yyyy-mm-dd hh24:mi:ss') next_date,
       interval,
       what
from user_jobs

4、执行作业

execute dbms_job.run(job_id);

5、删除作业

execute dbms_job.remove(job_id);

6、暂停和继续作业

execute dbms_job.broken(job_id,true);
execute dbms_job.broken(job_id,false);