Oracle:更新多行同列不同值

遇到个问题……

这几天遇到个问题,就是在访问后台的时候被强制使用了SSL,所以导致上不了后台.我是如下这样解决的:


// 下面这个函数在:[wp-includes/functions.php]中
/**
* Whether to force SSL used for the Administration Screens.
*
* @since 2.6.0
*
* @staticvar bool $forced
*
* @param string|bool $force Optional. Whether to force SSL in admin screens. Default null.
* @return bool True if forced, false if not forced.
*/
function force_ssl_admin( $force = null ) {
static $forced = false;

if ( !is_null( $force ) ) {
$old_forced = $forced;
$forced = $force;
return $old_forced;
}
return false;
//return $forced; 注释掉这一行,然上面直接添加一句 return false;即可.
}

只能这样临时先访问了…

Oracle LOGO
image-2460

关于题目的问题

其实对于题目,说起来也很简单了,大概是这样一个场景:

比如我们正在构建一个信息系统,每个用户每个月有一次抽奖机会,假设我们需要给每次最新的抽奖记录一个标记为1,那么之前的就要标识为0.

好了,上面这个场景就是这个问题.一句话就是:更新某个用户最新一条记录为1,之前的为0.

解决了.


update users_activity
set lastupdate=(
case
when id=#{id} then 1
when id!=#{id} then 0
else 0
end
) where userid=#{userid}

--
update users_activity
set 要更新的字段=(
case
when id=最新记录ID then 1
when id!=最新记录ID then 0
else 0
end
) where userid=用户ID

如上,问题解决.

配置Oracle服务器与客户端字符集

问题描述

一开始是设置字符集怎么设置怎么都不对,显示不出来,或者就是显示乱码.

后来,修改了本地的NLS_LANG就报下面的错误了.

ORA-12705: Cannot access NLS data files or invalid environment specified

Oracle LOGO
image-1872

解决方案

步骤[以SYS身份执行]:

shutdown immediate;//如果未开启数据库,可以略过这一句
startup mount;
alter session set sql_trace=true;
alter system enable restricted session;
alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
alter database open;
alter database character set internal_use utf8;
alter session set sql_trace=false;
shutdown immediate;
startup;

select userenv('language') from dual;

//下面是Linux操作

//这一句很重要,可能你设置的是UTF8.
//但这里显示的确不一样,[依据这个结果设置你的本地环境变量]
//我这查询的结果是:AMERICAN_AMERICA.UTF8[如果设置成别的,比如UTF8,运行sqlplus的时候就会报错[ORA-12705]]
//下面可以在另一个终端上进行操作!!
cd ~
vi .bash_profile
//去底部.增加下面的文字.
export NLS_LANG=AMERICAN_AMERICA.UTF8
:wq!//保存退出
//让其配置生效
source .bash_profile
source .bash_profile
//输出看一下是否正确
echo $NLS_LANG

//Linux操作结束

//回到sqlplus界面,重启数据库
STARTUP FORCE
CONN SYS/[PASSWORD] AS SYSDBA
CREATE TABLE TESTS(
SNAME VARCHAR2(50)
);
INSERT INTO TESTS
(SNAME)
VALUES
('测试');
SELECT * FROM TESTS;
//如果输出中文就表示配置正确了.
//另外如果使用工具连接,请在连接属性里将LANGUAGE设置为CHINA[或类似]

完美为Linux增加硬盘并且给Oracle扩展表空间

事情是这样起因的

不久之前(到底多久之前我也不记得了),我向Oracle数据库中插入数据的时候,提示表空间已经满了.果断进行了很多命令(此处不记得了.主要用了很多命令和乱七八糟的参数.),但是你依然可以在你的Linux上运行:


df -l //查看硬盘分配情况,以及使用情况

刚才试验了一下,下面的命令是可以查询Oracle表空间使用情况的:


select a.file_id "FileNo",a.tablespace_name
"Tablespace_name",
a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",
sum(nvl(b.bytes,0)) "Free",
sum(nvl(b.bytes,0))/a.bytes*100 "%free"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+)
group by a.tablespace_name ,
a.file_id,a.bytes order by a.tablespace_name;

然后,然后呢,下面这条SQL,可以修改表空间的大小:


ALTER DATABASE DATAFILE '/ora11gr2/oradata/ORA/ORA/users01.dbf' RESIZE 30G; //这个30G必须是比你的当前分区小

上面这些语句,先留着,接着看把.

黑线表情
image-1856

字符界面无法登录怎么办[64位系统专属]

我前不久遇到这个问题,觉得甚是郁闷,后来在网上看到了解决方案:

最好是在图形化界面下面登录系统,然后用root执行:


vi /etc/pam.d/login
// 在lib后面加上64 session required /lib/security/pam_limits.so
session required /lib64/security/pam_limits.so
session required pam_limits.so

保存退出,重启.

各种修改文件

在得知Oracle表空间不够之后,我对rhel系统的硬盘进行了各种操作,但都无际于事,包括对硬盘直接进行添加(VM虚拟机里面的rhel)!

最后我的做法是[此处假设你的Linux是经过良好分区的]:


//类似下面这个样子:
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 20642428 4950920 14642932 26% /
/dev/sda1 198337 32770 155327 18% /boot
/dev/sdb 61927420 30151108 28630584 52% /home

然后可以这样进行操作[操作前记得先备份一下数据]:

  1. 先分配一个硬盘[区别于现有硬盘,大小自定]
  2. 关机,然后重启至命令行界面[假设你的是虚拟机里面的Linux,因为你可能要查询],进入命令行:1,在终端执行:

    reboot init 3
    //或者执行
    reboot
    //在启动完成之后(也就是看到登录界面的时候,同时按
    alt+ctrl+shirt+F3
    //,切换回图形化界面:
    alt+F7
    [不过貌似不需要这样操作])
  3. 进入命令行界面之后,输入命令[先挂载刚才添加的硬盘]:

    mkdir /mnt/sdb

    接着查看哪个硬盘还没有被挂载:
    fdisk -l

    [一般显示在最下面的那个,注意看这样一行字:
    (Disk /dev/sdb: 64.4 GB, 64424509440 bytes),记住/dev/sdb]

    ,执行挂载:
    mount /dev/sdb /mnt/sdb

    ,执行格式化硬盘:
    mkfs -t ext4 /dev/sdb

    接下来就可以开始复制数据了.
  4. 我的数据文件比较大,复制花了大概10多分钟,比较慢,请耐心等待.使用命令即可复制:
    cp -aP /home/ /mnt/sdb

    然后就慢慢等把.
  5. 复制完成之后,进去看一下:
    cd /mnt/sdb
    //然后执行
    ls

    命令.大致不缺文件[!!!此时暂时不要进行删除任何数据的操作,建议在当前硬盘使用到15天之后再删除原数据,以防不测]
  6. 执行:
    umount -l /home
    //卸载现有分区(!!!假如一开始就在图形化界面操作,会报错!!)
    umount -l /mnt/sdb
    //卸载已经有数据的分区
  7. 接着执行:

    mount /dev/sdb /home
    cd /home
    ls
    //(然后: 看一下是不是加载上了)

  8. 还有一个重要的步骤,接着往下看!!!

fstab出错了怎么处理??

接上面,/etc/fstab是一个在启动系统的时候,自动加载相应分区的配置文件非常重要!!

我们目前是把分区挂载到系统了,想要自动加载,请往下看:


mv fstab fstab.bak //备份数据
vi fstab
//然后在最后一行添加如下数据,注意空格
/dev/sdb /home ext4 defaults 0 0
//然后:ESC -> :wq! 回车退出

接着执行: reboot 重启进入图形化界面.

假如fstab文件丢了,进不了系统怎么办

首先进入字符界面,执行: df -l 命令,输出如下:

Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 20642428 4950936 14642916 26% /
/dev/sda1 198337 32770 155327 18% /boot
/dev/sdb 61927420 45433432 13348260 78% /home

然后手动自己写fstab文件:
vi /etc/fstab

里面添加如下内容{参照你自己的系统配置}:

/dev/sda1 /boot ext4 defaults 0 0
/dev/sda2 / ext4 defaults 1 1
/dev/sdb /home ext4 defaults 0 0
/dev/sda5 swap swap defaults 0 0

然后保存退出.
最基本的,然后执行 reboot 就可以了.[光盘修复是行不通的,我搞了一晚上!!结果……………都不行,于是我苦逼的手写了fstab文件]….

修改Oracle数据文件大小

接着,开启服务,打开数据库,其余的…
不好意思,开头都已经写了.

Oracle创建外部表

写在前面的一些废话

今天帮一个朋友在Windows下面弄Oracle发现我居然不会弄了…[悲催],而且运行起来也比较卡…令:本站备案刚刚通过了.

SQL*Loader创建外部表

在当前目录保存以下内容[文件名:ldr_case1.ctl]:


LOAD DATA
INFILE *
APPEND INTO TABLE BONUS -- 这个表必须是已经存在的
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

可以使用下面的命令去生成相关文件:


[当前路径]: sqlldr SCOTT/TIGER control=ldr_case1.ctl external_table=generate_only

执行之后就会有一个ldr_case1.log里面会有相关脚本.
external_table有三个参数:

  1. NOT_USED:不使用外部表,默认值.
  2. GENERATE_ONLY:SQLLDR并不执行加载,而是生成创建外部表的SQL和处理数据的SQL.并保存在log文件中.[修改后可再SQL*Plus中执行]
  3. EXECUTE:首先创建外部表,然后通过外部表方式加载数据
  4. 执行上述命令之后,ldr_case1.log的内容如下[节选:创建DIRECTORY,创建TABLE部分]:

    CREATE DIRECTORY statements needed for files
    ------------------------------------------------------------------------
    CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/Documents/oracle_product/external'

    CREATE TABLE statement for external table:
    ------------------------------------------------------------------------
    CREATE TABLE "SYS_SQLLDR_X_EXT_BONUS"
    (
    "ENAME" VARCHAR2(10),
    "JOB" VARCHAR2(9),
    "SAL" NUMBER
    )
    ORGANIZATION external
    (
    TYPE oracle_loader
    DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
    ACCESS PARAMETERS
    (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'ldr_case1.bad'
    LOGFILE 'ldr_case1.log_xt'
    READSIZE 1048576
    SKIP 6
    FIELDS TERMINATED BY "," LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (
    "ENAME" CHAR(255)
    TERMINATED BY ",",
    "JOB" CHAR(255)
    TERMINATED BY ",",
    "SAL" CHAR(255)
    TERMINATED BY ","
    )
    )
    location
    (
    'ldr_case1.ctl'
    )
    )REJECT LIMIT UNLIMITED

    INSERT statements used to load internal tables:
    ------------------------------------------------------------------------
    INSERT /*+ append */ INTO BONUS
    (
    ENAME,
    JOB,
    SAL
    )
    SELECT
    "ENAME",
    "JOB",
    "SAL"
    FROM "SYS_SQLLDR_X_EXT_BONUS"

    statements to cleanup objects created by previous statements:
    ------------------------------------------------------------------------
    DROP TABLE "SYS_SQLLDR_X_EXT_BONUS"
    DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

    Oracle LOGO
    image-1845

    CREATE TABLE方式创建外部表

    其实我最喜欢的是这种方式,至少目前是.


    //文件保存为:tes.ctl

    LOAD DATA
    INFILE *
    TRUNCATE INTO TABLE TES
    FIELDS TERMINATED BY ","
    (TNAME,TAGE,TINFO char(2000))
    BEGINDATA
    'tes',20,'dfsafsdafjklsdafdjksalkfjaslkfjkdsalfdjksalkfjsalfkdjsalkfdlsakfjklsd''sas',22,'dfsafsaewqioeuwoiqruewoiqruiejkfsadljfldksjakfldsaljkfjklasdfjksaldfsadsalfkjslkafjkdlsakjfdjlsafkjslkfjdslkafkjdsaf'
    'fdsa',22,'fdsafsdafldsjaklfjsalkfksmncklmsa;djkl;sajfoiewfjsaljfdlskafjlksajflklksjaklfjdslajflksd'
    'fdsasa',25,'dsafsadlkfjlkwejewioqfwiqfl;ksa;klfdslkafdlkjsajlkfdksjlajfkl;sa;jkfdsaldkjsal;fkskj'

    别激动,往下看

    //文件保存为:tes.sql

    CONN / AS SYSDBA; //连接到系统
    STARTUP FORCE //如果数据库已经启动,此行将先关闭数据库,然后再次启动.
    CREATE DIRECTORY TES AS '/home/oracle/Documents/oracle_product/external/test/'; //必须是已经存在的目录,并且有可读写权限
    GRANT READ,WRITE ON DIRECTORY TES TO SCOTT; //授权
    -- CREATE TEST TABLE.
    CREATE TABLE TES(
    TNAME VARCHAR2(10),
    TAGE NUMBER,
    TINFO VARCHAR2(2000)
    )
    -- external table.
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY TES
    -- 加载数据
    ACCESS PARAMETERS(
    RECORDS DELIMITED BY NEWLINE
    SKIP 6
    FIELDS TERMINATED BY ","
    (TNAME,TAGE,TINFO)
    )
    LOCATION('tes.ctl')
    -- end 加载数据
    );
    -- end 声明外部表
    -- output table data.
    SELECT * FROM TES;
    -- delete table.
    DROP TABLE TES;
    -- delete directory.
    DROP DIRECTORY TES;

    可以在进入SQL*Plus界面之后,直接执行

    @tes.sql

    可以完成以下操作:–>启动数据库[已经启动的将先被关闭]–>创建外部表–>授权给SCOTT用户–>创建表–>导入数据–>使用SELECT输出表数据–>删除DIRECTORY对象–>删除创建的表.

SQL*Loader导入大字段

要导入大字段(LOB类型)怎么办


SQL*Loader的大部分方法已经被做成一个HTML,地址.后期将不再更新SQL*Loader导入的系列方法.


LOB作为打字段数据类型,是Oracle新增的数据类型,用来替代long和long raw类型,一般SQLLDR操作中不会涉及大字段类型的操作.

1.数据保存在数据文件中.

这种方式可以按照5.3.10节中提到的方式处理,举个例子:

还是以MANAGER表为例,修改表中的REMARK列为LOB类型,在SQL*Plus命令环境中如下:



SQL> ALTER TABLE MANAGER DROP COLUMN REMARK;

Table altered.

SQL> ALTER TABLE MANAGER ADD REMARK CLOB;

Table altered.

数据文件如下:


10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16,BLAKE,HR MANAGER,"This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:
1. Ensure the effective local implementation of corporate level HR initiatives and new programs.
2. Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing
3. Oversee standard recruiting and procedures to ensure the company¡¯s staffing requirements are met in a timely manne
4. Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.
5. Develop, implement and oversee the training and development programs to upgrade the skills of the future challenges."|

示例代码保存为数据文件: oracledatat14.data

创建控制文件如下:



LOAD DATA
INFILE oracledata14.data "str '|\n'"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(MGRNO,MNAME,JOB,REMARK char(100000))
BEGINDATA


控制文件保存为: ldr_case16.ctl

注意这里REMARK显式指定char(100000),因为Oracle默认所有输入字段都是char(255),如不显式指定类型和长度,一旦加载列的实际长度超出255,则数据加载就会报错:Field in data file exceeds maximum length.

执行SQLLDR加载数据.

数据成功加载!这种方式最关键的地方是必须保证REMARK列定义的长度大于数据文件中文本块的大小.

2. 数据保存在独立的文件中

这种数据相对于第一种更加常见,相应处理也更简单一些(跳过了换行符的处理),SQLLDR中提供了LOBFILE关键字,直接支持加载文件到LOB类型中.

首先再SQL*Plus命令行环境中连接数据库,创建一个新表:



SQL> CREATE TABLE LOBTBL (
FILEOWNER VARCHAR2(30),
FILENAME VARCHAR2(200),
FILESIZE NUMBER,
FILEDATA CLOB,
CREATE_DATE DATE
);

表中共有5列,分别表示文件属主,文件名,文件大小,文件内容和文件创建时间.

创建数据文件,内容如下:


2009-03-17 09:43 154 JUNSANSI /home/oracle/ldr_case1.ctl
2009-03-17 09:44 189 JUNSANSI /home/oracle/ldr_case1.log
2009-03-17 09:44 2,369 JUNSANSI /home/oracle/ldr_case2.ctl
2009-03-16 16:50 173 JUNSANSI /home/oracle/ldr_case2.log
2009-03-16 16:49 204 JUNSANSI /home/oracle/ldr_case3.ctl
2009-03-16 16:50 1,498 JUNSANSI /home/oracle/ldr_case3.log
2009-03-16 17:41 145 JUNSANSI /home/oracle/ldr_case4.ctl
2009-03-16 17:44 130 JUNSANSI /home/oracle/ldr_case4.log
2009-03-16 17:44 1,743 JUNSANSI /home/oracle/ldr_case5.ctl
2009-03-17 11:01 132 JUNSANSI /home/oracle/ldr_case5.log
2009-03-17 11:02 188 JUNSANSI /home/oracle/ldr_case6.ctl
2009-03-17 11:02 1,730 JUNSANSI /home/oracle/ldr_case6.log

示例代码保存为数据文件: oracledata15.data

创建控制文件如下:



LOAD DATA
INFILE oracledata15.data
TRUNCATE INTO TABLE LOBTBL
(
CREATE_DATE position(1:17) date 'yyyy-mm-dd hh24:mi',
FILESIZE position(*+1:24) "to_number(:FILESIZE,'99,999,999')",
FILEOWNER position(*+1:34),
FILENAME position(*+1) char(200) "substr(:FILENAME,instr(:FILENAME,'\\',-1)+1)",
FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF )
BEGINDATA

示例文件保存为控制文件:ldr_case17.ctl

这个控制文件是之前介绍示例应用的集大成者,又有定长处理,又有函数转换,唯一陌生的就是最后一行:LOBFILE就是前面提到的(FILENAME) TERMINATED BY EOF 这LOBFILE关键字,只需要指定FILENAME列,其他都是固定格式,调用时直接按此指定即可.

某些字段无值导致加载报错怎么办

比如你某天拿到了一个这样的一个数据文件:



SMITH,CLEAK,3904
ALLEN,SALESMAN,
WARD,SALESMAN,3128
KING,PRESIDENT,2523

示例代码『未实际保存』.[ora.data]

Oracle LOGO
image-1840

首先按照前面的例子去处理这些数据:



LOAD DATA
INFILE ora.data
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA


控制文件『未实际保存』.[ora.ctl]

执行后报错,错误如下:



Record 2 : Rejected - Error on table BONUS, column SQL.
Column not found before end of logical record (use TRAILING NULLCOLS)

就本例的错误信息来说,SQLLDR提示已经非常清楚:直到行结束也没发现适当的列值.

这是本例中数据文件的第二行没有提供适当的值(这一点都不稀奇,不管数据量庞大与否,DBA绝对不能期望数据文件完全满足要求,因此在编写控制文件时,也要考虑到对意外情况的处理.)

针对这一错误,SQLLDR甚至连解决方案也一并提供:使用TRAILING NULLCOLS.TRAILING NULLCOLS的作用是当某行没有对应的列值时,SQLLDR就会自动将其赋为NULL,而不是报错.



SMITH,CLEAK,3904
ALLEN,SALESMAN,
WARD,SALESMAN,3128
KING,PRESIDENT,2523


数据文件保存: oracledata16.data

控制文件如下:



LOAD DATA
INFILE oracledata16.data
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," TRAILING NULLCOLS
//TRAILING NULLCOLS的作用是当某行没有对应的列值时,SQLLDR就会自动将其赋为NULL,而不是报错.
(ENAME,JOB,SAL)
BEGINDATA


通过这个示例,我们能够得到三点体会:

  1. 执行完操作后一定要验证,就本例来说,从SQLLDR命令的执行看起来一切征程,如果不是到SQL*Plus环境中查看导入的数据,恐怕都不知道有记录未被成功导入.
  2. 一定要注意看日志,SQLLDR虽然算不上智能,但是也不傻,有时候造成错误的原因只是它不知到怎么办好,不过日志文件中一定会留下处理痕迹,不管SQLLDR命令执行是否成功,日志文件总是能告诉我们其执行的更多细节.
  3. 以上全部都是.