Category Archives: Oracle

昨天做联调时，在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"/>

...

使用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操作，再移动回来。

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;

游标处理数据范例（在原示例上有删减）：

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;

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);

1、tns命令台

lsnrctl

2、检查tns是否可用

tnsping

3、查询SQL历史记录

select * from v$sql where parsing_schema_name='USERID' order by last_active_time desc

4、查询所有配置

show parameter

Oracle密码忘了，怎么办？

sqlplus/as sysdba
alter user sys identified by xxx;

CREATE TEMPORARY TABLESPACE ATS_TEMP
TEMPFILE 'D:\Oracle11g\oradata\orcl\ATS_TEMP.DBF' SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE TABLESPACE ATS_PACS
DATAFILE 'D:\Oracle11g\oradata\orcl\xxxx.DBF' 
SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT MANUAL;

CREATE TABLESPACE ATS_AIGATE
DATAFILE 'D:\oracle\product\10.2.0\db_1\oradata\orcl\xxxxx.DBF' 
SIZE 200M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT MANUAL;


CREATE USER AIGATE IDENTIFIED BY AIGATE         
DEFAULT TABLESPACE ATS_AIGATE
TEMPORARY TABLESPACE ATS_TEMP
PROFILE DEFAULT;


GRANT DBA TO AIGATE WITH ADMIN OPTION;