Oracle 相关操作指令

1. oracle 查询表空间占用情况

SELECT a.tablespace_name "表空间名称", 
total / (1024 * 1024) "表空间大小(M)", 
free / (1024 * 1024) "表空间剩余大小(M)", 
(total - free) / (1024 * 1024 ) "表空间使用大小(M)", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;
​

2.创建用户并赋予默认表空间，赋予 dba 权限

create user rh_prod identified by rh_prod default tablespace RH_PROD;
grant connect,resource,dba to rh_prod;
​

3.删除用户

drop user rh_prod cascade;
​

4.创建表空间

create tablespace "RH_PROD" datafile 'D:\app\Administrator\oradata\orcl\RH_PROD_1.DBF' size 500M autoextend on;
​

5.修改表空间，增加数据文件，在数据块为 8k 的情况下，单个数据文件的最大容量为 8K*2^22 = 32G

alter tablespace "RH_PROD" add datafile 'D:\app\Administrator\oradata\orcl\RH_PROD_2.DBF' size 500M autoextend on;
​

6.查询用户下对 session 会话

select username,sid,serial#,paddr from v$session where username='RH_UAT';
alter system kill session '634,7';--杀死
​

7.重新配置监听命令

netca
​

8.创建 dblink

create public database link mylink connect to rh_prod identified by rh_prod using 'PROD';
--PROD需要去tns配置
drop public database link mylink;--删除
select * from dual@mylink;--测试是否有效
​

9.dblink + expdp 导出数据

create directory MY_DIR as 'd:\dump';--创建目录
grant write,read on directory MY_DIR to rh_prod;--授权
select * from dba_directories;--查看是否创建
expdp rh_prod/rh_prod@orcl network_link=mylink schemas=rh_prod dumpfile=expdp.dmp parallel=4 DIRECTORY=MY_DIR logfile=result.log--导出 
​

10.impdp 导入数据

impdp rh_prod/rh_prod@orcl DIRECTORY=MY_DIR DUMPFILE=expdp.dmp SCHEMAS=rh_prod logfile=impdp.log
​