oracle 相关

• 插入特殊字符 ’

create table TMP_IELTS
(
  sid         NUMBER,
  sname       VARCHAR2(2000),
  surl        VARCHAR2(2000),
  create_time DATE default sysdate
)

insert into TMP_IELTS (SID, SNAME, SURL)
values (
(SELECT max(sid)+1 FROM TMP_IELTS), 
'Anti-government protesters gather outside New Zealand''s parliament', 
'https://www.reuters.com/world/asia-pacific/anti-government-protesters-gather-outside-new-zealands-parliament-2022-08-23/'
);

 

1. sqlplus /nolog 报错

[oracle@fb659a0bb493 /]$ sqlplus /nolog
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

出错原因：

crontab里面的脚本，通常读取的是默认的环境变量，PATH里面不包含oracle数据库的路径。

解决办法：

在shell脚本里前面加上:

source /home/oracle/.bash_profile

前提是/home/oracle/.bash_profile已经配置好了ORACLE_HOME和PATH。

如：

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$PATH:$ORACLE_HOME/bin

 

2. 创建 schema

-- 查看当前已有的用户
SELECT Username FROM dba_users;

-- 创建临时
CREATE USER SR_PL_SHIA_SIT IDENTIFIED BY PASSWORD;

-- 授权
GRANT CREATE SESSION TO SR_PL_SHIA_SIT;

CREATE TABLESPACE shia_sit_wk DATAFILE 'shia_sit_wk.dat' SIZE 10M AUTOEXTEND ON;

CREATE TEMPORARY TABLESPACE shia_sit_tablespace_temp TEMPFILE 'shia_sit_tabspace_temp.dat' SIZE 5M AUTOEXTEND ON;

DROP USER SR_PL_SHIA_SIT;

-- 开始创建数据库
CREATE USER SR_PL_SHIA_SIT IDENTIFIED BY 111 DEFAULT TABLESPACE shia_sit_wk TEMPORARY TABLESPACE shia_sit_tablespace_temp;

-- 授权
grant create session to SR_PL_SHIA_SIT;

grant create table to SR_PL_SHIA_SIT;

grant unlimited tablespace to SR_PL_SHIA_SIT;

-- 最后修改一下密码
ALTER USER SR_PL_SHIA_SIT IDENTIFIED BY 111;


========================================================================================================================================================================================================================================================================================

-- 查看当前已有的用户
SELECT Username FROM dba_users;

-- 创建临时
CREATE USER gzmpc IDENTIFIED BY 111;

-- 授权
GRANT CREATE SESSION TO gzmpc;

CREATE TABLESPACE gzmoc_wk DATAFILE 'gzmoc_wk.dat' SIZE 10M AUTOEXTEND ON;

CREATE TEMPORARY TABLESPACE gzmoc_wk_tablespace_temp TEMPFILE 'gzmoc_tabspace_temp.dat' SIZE 5M AUTOEXTEND ON;

DROP USER gzmpc;

-- 开始创建数据库
CREATE USER gzmpc
IDENTIFIED BY 111
DEFAULT TABLESPACE gzmoc_wk
TEMPORARY TABLESPACE gzmoc_wk_tablespace_temp;

-- 授权
grant create session to gzmpc;

grant create table to gzmpc;

grant unlimited tablespace to gzmpc;

-- 最后修改一下密码
ALTER USER gzmpc IDENTIFIED BY 111;

--------------------------------------------------------------


-- 创建临时
CREATE USER SR_PL_PRP_SIT IDENTIFIED BY PASSWORD;

-- 授权
GRANT CREATE SESSION TO SR_PL_PRP_SIT;

CREATE TABLESPACE prp_sit_wk DATAFILE 'prp_sit_wk.dat' SIZE 10M AUTOEXTEND ON;

CREATE TEMPORARY TABLESPACE prp_sit_tablespace_temp TEMPFILE 'prp_sit_tabspace_temp.dat' SIZE 5M AUTOEXTEND ON;

DROP USER SR_PL_PRP_SIT;

-- 开始创建数据库
CREATE USER SR_PL_PRP_SIT IDENTIFIED BY 111 DEFAULT TABLESPACE prp_sit_wk TEMPORARY TABLESPACE prp_sit_tablespace_temp;

-- 授权
grant create session to SR_PL_PRP_SIT;

grant create table to SR_PL_PRP_SIT;

grant unlimited tablespace to SR_PL_PRP_SIT;

-- 最后修改一下密码
ALTER USER SR_PL_PRP_SIT IDENTIFIED BY 111;