ORCL创建用户相关操作

ORCL创建用户相关操作

-- 0.查询表空间文件保存路径
select * from v$datafile 
--1. 创建表空间
create tablespace jcsjv1 
datafile 'D:\app\Administrator\oradata\orcl\sfrz\jcsjv1.dbf' size 2048M --存储地址 初始大小1G
autoextend on next 200M maxsize unlimited   --每次扩展50M，无限制扩展
EXTENT MANAGEMENT local  autoallocate
segment space management auto;

--2.创建用户
 create user jcsjv1  identified by jcsjv1 
default tablespace jcsjv1
  profile DEFAULT;

--3.授权
grant connect,resource,create database link, create view to jcsjv1;
grant dba to jcsjv1;

--4设置普通用户不限配额
grant unlimited tablespace to kites_test;

--5数据备份恢复
expdp sys/h71IEDnVvcFbNpC7 file=expdb_system_dev_2.dmp directory=bak_dir schemas=system_dev;
impdp sys/h71IEDnVvcFbNpC7 file=expdb_system_dev_2.dmp directory=bak_dir REMAP_SCHEMA=system_dev:kites_test ;

expdp sys/h71IEDnVvcFbNpC7 file=expdb_exam_dev.dmp directory=bak_dir FULL=y;
impdp sys/h71IEDnVvcFbNpC7 file=expdb_20220809.dmp directory=bak_dir REMAP_SCHEMA=exam_dev:kites_test ;

--6删除重复数据
delete from JCSJV1.ZH_JG_SJBMSXXB where rowid not in
(select min(rowid) from JCSJV1.ZH_JG_SJBMSXXB group by sjbmsid )

创建只能查询不能修改用户

CREATE USER "OPENEXAM" IDENTIFIED BY "OPENEXAM" DEFAULT TABLESPACE "JCSJV2";
grant connect to "OPENEXAM" ;
grant create synonym to "OPENEXAM";
grant create session to "OPENEXAM";

- 表授权
select 'grant select on '||owner||'.'||object_name||' to "OPENEXAM";'
from dba_objects  where owner in ('JCSJV2')
and object_type='TABLE';
grant select on JCSJV2.BUS_TASK to "OPENEXAM";

- 同义词创建

select 'create or replace synonym '||owner||'.'||object_name||' for "OPENEXAM".'||object_name||';' 
from dba_objects  where owner in ('JCSJV2')
and object_type='TABLE';
create or replace synonym JCSJV2.BUS_TASK for OPENEXAM.BUS_TASK ;

表空间修改

SELECT TABLE_NAME,TABLESPACE_NAME FROM USER_TABLES  -- WHERE TABLE_NAME='test' --可以加上条件
比如我们看到我的库里有两个表表空间是USERS，而非我们自建的FCCMS

SELECT 'alter table '||TABLE_NAME||' move tablespace FCCMS;' FROM USER_TABLES WHERE TABLESPACE_NAME = 'USERS'

SELECT 'alter index '|| INDEX_NAME ||' rebuild tablespace FCCMS;' FROM user_indexes;
PS：这里需要注意TEST2表里包含的CLOB字段，针对Clob、Blob字段需单独做修改处理 

ALTER TABLE TEST2 MOVE TABLESPACE USERS LOB(col_lob1,col_lob2) STORE AS(TABLESPACE FCCMS);

删除没有ID的重复相同数据

方法一

--查询
select * from zjkb where lxdh in (
select lxdh from zjkb group by lxdh having count(lxdh)>1
)
and rowid not in (
select min(rowid)  from zjkb group by lxdh having count(lxdh)>1
)

--删除
delete from zjkb where lxdh in (
select lxdh from zjkb group by lxdh having count(lxdh)>1
)
and rowid not in (
select min(rowid)  from zjkb group by lxdh having count(lxdh)>1
)

方法二

-- 查询
select * from JOB_USER_LINK a where rowid !=(select max(rowid)
from JOB_USER_LINK b where a.user_dm=b.user_dm and a.job_dm=b.job_dm and a.EXAM_DM=b.EXAM_DM)

--删除
delete from JOB_USER_LINK a where rowid !=(select max(rowid)
from JOB_USER_LINK b where a.user_dm=b.user_dm and a.job_dm=b.job_dm and a.EXAM_DM=b.EXAM_DM)

正则替换

select REGEXP_REPLACE('sip:6305q.6hlsl.wh118.whjbjy.whjy.hubjy.cnjy@wh118.whjbjy.whjy.hubjy.cnjy:9902', ':[0-9]+$', '') from dual

select REGEXP_REPLACE('sip:6305q.6hlsl.wh118.whjbjy.whjy.hubjy.cnjy@wh118.whjbjy.whjy.hubjy.cnjy:9902', '^sip:', '') from dual

select REGEXP_REPLACE('6305q.6hlsl.wh118.whjbjy.whjy.hubjy.cnjy@wh118.whjbjy.whjy.hubjy.cnjy:9902', '^[0-9]+', '') from dual

删除用户和表空间

DROP USER SYSMNG CASCADE
删除表空间。
DROP TABLESPACE SYSMNG INCLUDING CONTENTS AND DATAFILES;

用户密码过期操作
1、切换用户为oracle
su - oracle
2、以dba登录
conn / as sysdba
3、更改用户密码
alter user 用户名 identified by <密码>
4、给用户解锁
alter user sysmng account unlock;
不用换新密码解锁
alter user 用户名 identified by <原来的密码> account unlock; ----不用换新密码

Linux设置oracle开机自启

1、以root身份登录到linux系统，使用vi编辑器编辑文件/etc/oratab（N改为Y）

orcl:/data/oracle/product/11.2.0/db_1:Y

说明：orcl为实例名；/data/oracle/product/11.2.0/db_1为oracle安装目录