Category Archives: Database

Oracle回收站

Posted on by — No Comments ↓

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;

MySQL忘记管理员密码

Posted on by — No Comments ↓

方法一：
1、关闭mysql授权

#关闭mysql
/etc/init.d/mysql stop

#以不需要授权的模式启动mysql
#修改/etc/mysql/my.cnf添加下面的内容
[mysqld]
skip-grant-tables

#开启mysql
/etc/init.d/mysql start

2、修改密码

#登录mysql
mysql
mysql> use mysql;
#旧版本
mysql> UPDATE user SET password=password("xxx") WHERE user='root';
#新版本
mysql> update user set authentication_string=PASSWORD('xxx') where User='root';
mysql> flush privileges;
mysql> exit;

3、开启mysql授权

#关闭mysql
/etc/init.d/mysql stop

#以不需要授权的模式启动mysql
#修改/etc/mysql/my.cnf禁用skip-grant-tables
[mysqld]
#skip-grant-tables

#开启mysql
/etc/init.d/mysql start

4、搞定

方法二
1、关闭mysql授权

#关闭mysql
service mysql stop

#以不需要授权的模式启动mysql
mysqld_safe --skip-grant-tables

2、修改密码

#登录mysql
mysql
mysql> use mysql;
#旧版本
mysql> UPDATE user SET password=password("xxx") WHERE user='root';
#新版本
mysql> update user set authentication_string=PASSWORD('xxx') where User='root';
mysql> flush privileges;
mysql> exit;

3、开启mysql授权

#关闭mysql
service mysql stop

#开启mysql
service mysql start

4、搞定

Oracle游标

Posted on by — No Comments ↓

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

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的语句变化总结

Posted on by — No Comments ↓
调整内容 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

Posted on by — No Comments ↓

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

Oracle创建用户空间

Posted on by — No Comments ↓ 
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;

RAC连接字符串

Posted on by — No Comments ↓

前几天用OB9导入导出表空间的时候，发现一个问题，
如果RAC连接字符串写的不全，是无论如何都导不进去的，
而OB9大哥的连接字符串却又有长度限制，可怜我一个晚上啊。
最后改用监听，OK了。

(DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.220)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.221)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.222)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.223)(PORT=1521))(LOAD_BALANCE=yes)(FAILOVER=on))(CONNECT_DATA=(SERVICE_NAME=ractest)))

OB9导入导出数据库，也是用了oracle自带的命令行工具（imp.exe和exp.exe）

导入时，其调用命令行就是：

D:\oracle\product\10.2.0\db_1\BIN\imp.exe 'user2/pass2@(DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.220)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.221)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.222)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.223)(PORT=1521))(LOAD_BALANCE=yes)(FAILOVER=on))(CONNECT_DATA=(SERVICE_NAME=ractest)))' PARFILE="D:\OB9\temp\Imp.PRM"

而Imp.PRM是一个配置文件

FILE="D:\OB9\XXX.DMP"
LOG="D:\OB9\IMP.LOG"
BUFFER=4096
RECORDLENGTH=1024
GRANTS=Y
INDEXES=Y
ROWS=Y
IGNORE=Y
COMMIT=N
DESTROY=N
FROMUSER=(
"user1"
)
TOUSER=(
"user2"
)

导出时，其调用命令行就是：

D:\oracle\product\10.2.0\db_1\BIN\exp.exe 'user1/pass1@(DESCRIPTION=(ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.220)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.221)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.222)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.120.223)(PORT=1521))(LOAD_BALANCE=yes)(FAILOVER=on))(CONNECT_DATA=(SERVICE_NAME=ractest)))' PARFILE="D:\OB9\temp\Exp.PRM"

而Exp.PRM是一个配置文件

FILE="D:\OB9\xxx.DMP"
LOG="D:\OB9\EXP.LOG"
BUFFER=4096
RECORDLENGTH=1024
COMPRESS=Y
GRANTS=Y
INDEXES=Y
ROWS=Y
CONSTRAINTS=Y
RECORD=Y
CONSISTENT=N
STATISTICS=NONE
OWNER=(
"user1"
)