oracle 建库、用户、权限分配 sql

 

--登录
sqlplus /nolog
conn hubeiyusuan02/hubeiyusuan02@localhost:1521/orcl

--创建表空间
CREATE TABLESPACE fujian_hkl_new1 LOGGING DATAFILE 'F:\database\oracle\app\oradata\ORCL\fujian_hkl_new1.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M maxsize unlimited EXTENT MANAGEMENT LOCAL;
--创建临时表空间
create temporary tablespace fujian_hkl_new1_temp tempfile 'F:\database\oracle\app\oradata\ORCL\fujian_hkl_new1_tmp.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
--创建用户
CREATE USER fujian_hkl_new1 IDENTIFIED BY fujian_hkl_new1 DEFAULT TABLESPACE fujian_hkl_new1 TEMPORARY TABLESPACE fujian_hkl_new1_temp;
--授权
GRANT CREATE USER,DROP USER,ALTER USER,CREATE ANY VIEW,DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,DBA,CONNECT,RESOURCE,CREATE SESSION TO fujian_hkl_new1 ;

--查询 directories
select * from all_directories ;

1、新建directory的语法

CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';

 

例如：

create or replace directory dump_dir as 'D:\dump\dir'

这样把目录d:\dump\dir设置成dump_dir代表的directory

 

2、查询有哪些directory

select * from dba_directories

 

3、赋权

grant read,write on directory dump_dir to user01

 

4、删除

drop directory dump_dir

--impdp
--cmd 非登录执行
-- E:\dir
impdp jianguoqiche02/jianguoqiche02@orcl directory=IMPDIR dumpfile=exp_jg.dmp REMAP_SCHEMA=bt:jianguoqiche02 remap_tablespace=BTDATA:jianguoqiche02 table_exists_action=replace

update sys_user set PASSWORD='0cdc40950bf6e2c934ae553af877bf1a',SALT='7c8efc420e23409798be8f3c836fc772';

--查询表空间
select file_name,tablespace_name,round(bytes/(1024*1024)) from dba_data_files;

--删除表空间
DROP TABLESPACE JIANGUOQICHE02 including contents and datafiles cascade constraint;
--删除用户
drop user JIANGUOQICHE02 cascade;

--停止oracle
shutdown immediate;
--启动oracle
startup;

--导出整个库
expdp zjadmin/zjadmin@localhost:1521/orcl directory=DATA_PUMP_DIR  dumpfile=20210623.dmp  logfile=aa20210623.log
--导出某个用户的库
expdp jianguoqiche01/jianguoqiche01@localhost:1521/orcl schemas=jianguoqiche01 directory=ORACLE_DMP_DIR  dumpfile=20211129jianguoqiche01.dmp

--导出屏蔽某些表

expdp jianguoqiche01/jianguoqiche01@localhost:1521/orcl schemas=jianguoqiche01 directory=ORACLE_DMP_DIR  dumpfile=20211129jianguoqiche01.dmp
 exclude=TABLE:\"IN \'BIS_BANK_RECEIPT_DTL\'\'BIS_BANK_RECEIPT_DTL_HIS\'\'BIS_ACC_DTL\'\"

 

--查询某个实例的表

select distinct table_name
from dba_tables
where owner in ('JIANGUOQICHE02')
AND table_name not in ('BIS_BANK_RECEIPT_DTL', 'BIS_BANK_RECEIPT_DTL_HIS','BIS_ACC_DTL')
order by owner;

--exp导出。排除某些表
exp 用户名/密码@实例 file=C:\Users\Administrator\Desktop\ceshi.dmp TABLES= (表1，表2，...)

 

--查询表空间

　　SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

SELECT * FROM DBA_TABLESPACES; --查看有哪些表空间
SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME='CHENMU'; --查看CHENMU表空间有哪些表
SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS; --查看用户所属的默认表空间、临时表空间

ALTER TABLESPACE USERS ADD DATAFILE 'D:\SOFTWARE\DATABASE\ORACLE\ORADATA\ORCL\USERS01_extend.DBF' SIZE 500M AUTOEXTEND ON;//给表空间增加数据文件并自动增长

select
b.tablespace_name --表空间名
,b.m_bytes --表空间大小
,b.m_bytes-nvl(a.mbytes_free,0) used --已使用空间
,nvl(a.mbytes_free,0) free --剩余空间
,round(((b.m_bytes-nvl(a.mbytes_free,0))/b.m_bytes),2)*100||'%' pct_used --使用率
from
(select sum(bytes)/(1024*1024) mbytes_free,max(bytes)/(1024*1024) largest,tablespace_name
from sys.dba_free_space group by tablespace_name)a,
(select sum(bytes)/(1024*1024) m_bytes,sum(maxbytes)/(1024*1024) mbytes_max,tablespace_name
from sys.dba_data_files group by tablespace_name
union all
select sum(bytes)/(1024*1024) m_bytes,sum(maxbytes)/(1024*1024) mbytes_max,tablespace_name
from sys.dba_temp_files group by tablespace_name)b
where a.tablespace_name (+)= b.tablespace_name order by a.tablespace_name asc;

 

select b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes / 1024 / 1024 大小M,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.tablespace_name;

 

select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;//查询 表空间对应的数据文件，用户和表空间对应关系

ALTER DATABASE DATAFILE 'D:\SOFTWARE\DATABASE\ORACLE\ORADATA\ORCL\USERS01.DBF' AUTOEXTEND ON;//允许已存在的数据文件自动增长
ALTER TABLESPACE USERS ADD DATAFILE 'D:\SOFTWARE\DATABASE\ORACLE\ORADATA\ORCL\USERS01.DBF' SIZE 50M AUTOEXTEND ON;//给表空间增加数据文件并自动增长

--imp 命令：https://blog.csdn.net/lsyuan1989/article/details/50418665

 

　　oracle 删除表空间、对象以及数据文件

oracle 删除表空间（tablespace）及文件的方法

一、drop user xxxx cascade;

二、drop tablespace xxxx including contents and datafiles

drop user xxxx cascade;
drop tablespace xxxx including contents and datafiles;

1.首先看一下是不是已经使用了OMF

sql>show parameter db_create

查看参数db_create_file_dest,如果已经设置

则：drop tablespace tablespacename 就可以直接删除表空间以及相应的数据文件

2.如果没使用OMF,则：

drop tablespace tablespacename including contents and datafiles
————————————————
原文链接：https://blog.csdn.net/zhangchen124/article/details/129657267

---待验证
drop tablespace test; //这样删除表空间，数据文件还是存在，如果以后创建同名的表空间，该数据文件自动归类到该表空间里
drop tablespace test including contents; //删除表空间和表空间里的对象，经过创建默认表空间和临时表空间并指定给用户test，在里面创建一个表后执行该语句，test用户还存在也还可以连接，但是创建的表不存在了，也不能创建表了
drop tablespace FUJIAN_HKL including contents and datafiles; //删除表空间、对象以及数据文件

 

 

--查询被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看是哪个session引起的
select b.username,b.sid,b.serial#,logon_time from  v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--杀掉对应进程即解锁
alter system kill session 'sid,serial#';

 

•  其他库的权限 给到 某个用户

　　

--把A、B、C、D、E 赋权给 DEF

SELECT
    'grant select,insert,update on '||a.owner ||'.'||a.table_name||' to SAAS_DEF;'
FROM
    all_tables a
WHERE
    a.owner IN ('SAAS_A',
                'SAAS_B',
                'SAAS_C',
                'SAAS_D',
                'SAAS_E');                  

 

　　

sid