1、查询所有表空间:
select * from v$tablespace
2、查看用户:
select * from ALL_USERS;
3、创建表空间:
CREATE TABLESPACE DATA_SPACE --DATA_SPACE为表空间名
DATAFILE '/u01/app/oracle/oradata/orcl/SICP_DATA.DBF' --自动在该文件夹下创建SICP_DATA.DBF
SIZE 10120M
AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
3、创建用户
CREATE USER test IDENTIFIED BY test --用户名 密码
ACCOUNT UNLOCK
DEFAULT TABLESPACE DATA_SPACE-- 上面的表空间
TEMPORARY TABLESPACE TEMP;
4、给用户赋权限
GRANT CONNECT TO test ;
GRANT RESOURCE TO test ;
GRANT CREATE ANY VIEW TO test ;
GRANT CREATE DATABASE LINK TO test ;
GRANT CREATE ANY SYNONYM TO test ;
GRANT DEBUG CONNECT SESSION TO test ;
GRANT CREATE JOB TO test ;
GRANT SCHEDULER_ADMIN TO test ;
GRANT MANAGE SCHEDULER TO test ;
grant dba to test ;
注:连接数据库 sqlplus / as sysdba
5、查看用户的连接的sid和seria
select username,sid,serial# from v$session where username = 'TEST';
-- 注:上面是完整的sql,serial#是连在一起的
6、根据sid和seria进行删除
alter system kill session 'sid,serial';
7、删除用户
drop user ncc cascade