Oracle数据库

数据回填

数据回填

-- 查询某日数据
-- SELECT JCJXX.* FROM JCJXX WHERE JCJXX.BJSJ BETWEEN '2023-09-08 00:00:00' AND '2023-09-08 23:59:59'
-- 删除某日数据
-- DELETE FROM JCJXX WHERE JCJXX.BJSJ BETWEEN '2023-09-19 00:00:00' AND '2023-09-19 23:59:59';


	
-- 回填去年某日数据
INSERT INTO "YLP"."JCJXX" 
SELECT
	CONCAT( 'ht', JCJXX.JJDBH ) JJDBH,
	TO_CHAR(to_date( JCJXX.BJSJ, 'yyyy-mm-dd hh24:mi:ss' ) + 365,'yyyy-mm-dd hh24:mi:ss') BJSJ,
	JCJXX.CHUJING_DWDM,
	JCJXX.JQLBDM,
	JCJXX.JQLXDM,
	JCJXX.JQ_DZ,
	JCJXX.JQXFDM,
	JCJXX.COPY_DATE
FROM
	JCJXX 
WHERE
	JCJXX.BJSJ BETWEEN '2023-01-01 00:00:00'  AND '2023-01-01 23:59:59';

Oracle 序列 主键自增

创建序列

create sequence seq_sys_dept    -- seq_[表名字]
 increment by 1
 start with 200
 nomaxvalue
 nominvalue
 cache 20;

插入时查询

<selectKey keyProperty="id" order="BEFORE" resultType="Long">
    select seq_BUSS_idx_standard.nextval as id from DUAL
</selectKey>

删除序列DROP SEQUENCE seq_BUSS_data_yq;

Oracle sys忘记密码重置

E:\app\Administrator\product\11.2.0\dbhome_2\database\PWDorcl.ora

1. 先备份删除 PWDorcl.ora 文件
2. 管理员cmd：orapwd file=E:\app\Administrator\product\11.2.0\dbhome_2\database\PWDorcl.ora

• 解锁用户 ALTER USER system ACCOUNT UNLOCK;
• 改密码 ALTER USER system IDENTIFIED BY zj12345;

DMP导入导出

DMP导入导出

由dba导出的只能由dba导入：设置为dba： GRANT DBA TO [user];

政务外网用户：USER2

公司局域网用户：USER2TEST

ignore=y 忽略创建表时的错误

commit=y 插入立即提交（尽可能多的导入数据）

fromuser 导出用户

touser 目标用户

buffer 导入缓冲区大小（2GB），调大提升大数据量表的导入速度

log 日志文件

ignore=y# 忽略“表已存在”的错误（表已存在时会覆盖数据，不中断导入）

tables=XXXX 仅导入XXXX这一张表

-- 导出命令  STATISTICS=NONE（跳过统计信息） ROWS=Y（只导数据） CONSTRAINTS=N（跳过约束） OWNER=USER1（导出整个用户）
exp sys/【你的sys密码】@127.0.0.1:1521/orcl file=D:\export\gov_final.dmp log=D:\export\gov_exp.log OWNER=USER1 STATISTICS=NONE ROWS=Y

-- 导出命令 指定表 （导出表要移除OWNER）
exp sys/【你的sys密码】@127.0.0.1:1521/orcl file=D:\export\xxx.dmp log=D:\export\xxx.log TABLES=表1,表2 STATISTICS=NONE ROWS=Y
-- 对应导入
imp sys/【你的sys密码】@127.0.0.1:1521/orcl file=D:\export\xxx.dmp log=D:\export\xxx.log TABLES=PPIDQQQ FROMUSER=USER3 TOUSER=USER ROWS=Y IGNORE=Y


-- 导入命令
set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
imp sys/【你的sys密码】@127.0.0.1:1521/orcl file=D:\export\xxx.dmp fromuser=USER1 touser=USER2 log=D:\export\xxx.log ignore=y

-- 仅导入一张表
imp sys/【你的sys密码】@127.0.0.1:1521/orcl file=D:\export\xxx.dmp fromuser=USER1 touser=USER2 log=D:\export\xxx.log ignore=y tables=T_SJD  buffer=2000000000

imp sys/【你的sys密码】@127.0.0.1:1521/orcl file=D:\export\xxx.DMP LOG=D:\export\G.DATA_J_IMP.LOG TABLES=(DATA_J,DATA_J_MAP) IGNORE=Y FULL=N buffer=2000000000

创建表空间用户授权

详情

1.创建新模式

-- 创建模式管理员
CREATE USER receive_XXXX IDENTIFIED BY PASSWORD12345  -- 用户名（模式）receive_XXXX 密码 PASSWORD12345
-- 授权所有权限
GRANT ALL PRIVILEGES TO receive_XXXX; -- receive_XXXX用户所有权限

使用创建的模式用户连接数据库并创建 EW_POLLUTION 表

2.创建子用户

-- 创建模式受限用户 推送数据用户
CREATE USER prpln IDENTIFIED BY PLN123; -- 创建推送数据用户   表用户
-- 仅部分授权
GRANT CREATE SESSION TO prpln; -- 基本连接权限
GRANT SELECT,INSERT,UPDATE,DELETE ON receive_XXXX.EW_POLLUTION TO prpln; -- receive_XXXX模式下EW_POLLUTION表增删改查权限

3.子用户测试sql

'select * from BUSS_receive.ew_pollution'

--创建表空间
CREATE TABLESPACE receive_XXXX DATAFILE 'E:\app\Administrator\oradata\orcl\BUSS_receive.dbf' SIZE 200M AUTOEXTEND ON NEXT 200M;
--创建模式管理员
CREATE USER receive_XXXX IDENTIFIED BY PASSWORD12345
DEFAULT TABLESPACE receive_XXXX QUOTA UNLIMITED ON receive_XXXX;
--授权所有权限
GRANT ALL PRIVILEGES TO receive_XXXX;



--创建模式受限用户 推送数据用户
--创建推送数据用户   空气污染表用户
CREATE USER prpln IDENTIFIED BY PLN123
DEFAULT TABLESPACE receive_XXXX QUOTA UNLIMITED ON receive_XXXX;
--给部分授权
GRANT CREATE SESSION TO prpln;
GRANT SELECT,INSERT,UPDATE,DELETE ON receive_XXXX.EW_POLLUTION TO prpln;

****oracle 授权

GRANT CONNECT TO testpln;
GRANT CREATE SESSION TO testpln;
GRANT SELECT, INSERT, UPDATE, DELETE ON BUSSgov.EW_POLLUTION TO testpln;

查询varchar2的类型 并修改

-- 查询varchar2 的类型 B是字节 C是字符
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    CHAR_LENGTH,
    CHAR_USED,
    DATA_LENGTH
FROM USER_TAB_COLS 
WHERE TABLE_NAME = 'DATA_J' 
  AND COLUMN_NAME = 'CJQK';

-- 修改为字符模式
ALTER TABLE DATA_J MODIFY CJQK VARCHAR2(4000 CHAR);

复合主键

Details

-- 检查要添加的复合主键是否有重复
SELECT ORGANFULL, BJSJ, COUNT(*)
FROM BUSS_MONTH_24_MAIN
GROUP BY ORGANFULL, BJSJ
HAVING COUNT(*) > 1;

-- 添加复合主键
ALTER TABLE BUSS_MONTH_24_MAIN
ADD CONSTRAINT PK_BUSS_MONTH_24_MAIN PRIMARY KEY (ORGANFULL, BJSJ);

-- 查看主键约束名称
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'BUSS_MONTH_24_MAIN'
  AND constraint_type = 'P';


-- 查看主键包含哪些字段
SELECT column_name
FROM user_cons_columns
WHERE constraint_name = 'PK_BUSS_MONTH_24_MAIN';



-- 设置复合主键
-- BUSS_MONTH_24_MAIN
ALTER TABLE BUSS_MONTH_24_MAIN
ADD CONSTRAINT PK_BUSS_MONTH_24_MAIN PRIMARY KEY (ORGANFULL, BJSJ);

-- BUSS_REF_ORGAN
ALTER TABLE BUSS_REF_ORGAN ADD PRIMARY KEY ("ORGAN")