oracle常用命令-爱代码爱编程
目录
一、创建表空间 用户 赋权SQL
(1)创建表空间
create tablespace TESTDB logging datafile ' D:\Program\ORACLE\oradata\orcl\TESTBASE.DBF' size 512m autoextend on next 50m maxsize 2048m extent management local;
(2)创建用户
create user BASE01 identified by qlshamstest default tablespace TESTDB ;
(3)用户赋权
grant connect,resource,dba,exp_full_database,imp_full_database to BASE01 ;
(4)查看表空间使用情况
select a.tablespace_name, total, free, total-free as used from (select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name;
二、删除当前链接用户
1、首先查找目标用户的进程
select sid,serial# from v$session where username='CRM';
使用此语句会返回一个进程列表,每行有两个数字,然后用数字替代下面的sid和serial
2、kill会话
alter system kill session '_sid, _serial#'; //
其中_sid和_serial#具体值为上步中查询到的值。
example:alter system kill session '12,235';
三、其他常用查询SQL
(1)查询用户所对应的表空间
select username,default_tablespace from dba_users;
(2)查询该用户会话
select sid,serial# from v$session where username='BASE_DEV';
(3)查询所有用户会话
select sid,serial#,username from v$session;
(4) 删除相应用户
drop user 用户名XX cascade