Oracle常用代码总结

1、用户

创建用户

create user dm identified by dm
default tablespace BIGDATA_DM
temporary tablespace DM_TEMP
profile default；

修改用户名密码

alter user kettle identified by kettle; 

删除用户

drop user dm cascade;

2、权限

连接权限

grant connect to datatrans;

数据权限

grant resource to datatrans;

DBA权限

grant dba to datatrans;

全局表空间权限

grant unlimited tablespace to datatrans;

3、表空间

查询表空间剩余字节大小

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;

创建表空间

CREATE TABLESPACE BIGDATA_KETTLE --表空间名
DATAFILE 'E:\ORADATA\DB201\SW\WORKDB_BW\KETTLE.DBF' --表空间对应的数据文件
SIZE 1024M --数据文件大小
AUTOEXTEND ON NEXT 10M --数据文件不够用自动扩展，每次扩展大小
--MAXSIZE 1000M --数据文件最大文件大小
--LOGGING --启动重做日志
--PERMANENT --指定表空间为永久性的表空间
--EXTENT MANAGEMENT LOCAL AUTOALLOCATE --指定新建表空间为本地管理方式的表空间
BLOCKSIZE 1M --块大小
SEGMENT SPACE MANAGEMENT auto --指定本地管理表空间中段的存储管理方式，AUTO自动，MANUAL手工

create tablespace BIGDATA_KETTLE 
datafile 'E:\ORADATA\DB201\SW\WORKDB_BW\KETTLE.DBF' 
size 1024M  
autoextend on next 50M 
extent management local uniform size 256k;

修改表空间名称

alter tablespace DATATRANS rename to BIGDATA_DATATRANS;

查看用户对应的表空间

select username,default_tablespace from dba_users;

查看表空间对应的数据文件

select tablespace_name,file_name from dba_data_files;

修改用户对应的表空间

alter user bqsj default tablespace NNC_DATA01;

4、临时表空间

查看临时表空间的大小和数据文件路径

select tablespace_name, file_id, file_name, bytes/1024/1024 as "space(m)"
from dba_temp_files

创建临时表空间

create temporary tablespace TEMP_NNC_DATA01
tempfile 'E:\oracledata\ORCL\TEMP_NNC_DATA01.DBF'
size 10240M

5、数据文件

表空间追加数据文件

alter tablespace NNC_DATA01
add datafile 'E:\oracledata\ORCL\NNC_DATA03.DBF' 
size 10240M  
autoextend on next 50M

修改数据文件大小

alter database datafile 'D:\ORACLE2\ORADATA\ORCL\USERS01.DBF' resize 100m;

6、DBLink

查询所有dblink

select * from dba_db_links;

创建dblink

create public database link de_mes connect to tdm201 identified by tdm201
using '(description = (address_list = (address = (protocol = tcp)(host = 10.10.163.103)(port = 1521))) (connect_data = (service_name = orcl)))'

删除dblink

drop public database link de_mes;

7、数据导入导出

导出

exp bqsj01/bqsj01@10.10.163.125:1521/orclpdb file='E:\bqsj01.dmp' owner = 'bqsj01'

导入

imp bqsj01/bqsj01@10.10.163.104:1521/orcl file='E:\databak\bqsj01.dmp' fromuser = 'bqsj01' touser = 'bqsj01'

8、死锁

查看死锁进程

select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locked_object lo,
dba_objects ao,
v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

杀进程

 alter system kill session '132,1167';