1、切换到Oracle用户
su - oracle
2、登录sys用户
sqlplus / as sysdba
3、创建表空间
#先查询用户表空间文件的路径
select name from v$datafile;
#创建表空间文件,并将文件放置在对应得文件路径(上限5g)
CREATE TABLESPACE test LOGGING DATAFILE '/home/oracle/oradata/orcl/test.dbf'
SIZE 500M AUTOEXTEND ON NEXT 32M MAXSIZE 5000M EXTENT MANAGEMENT LOCAL;
#创建表空间,上限无限制,但是Oracle的表空间文件上线为32G
create tablespace icc_qzyh_xn datafile '/data/oracle/oradata/orcl/qzyh_xn.dbf' size 5000M autoextend on next 512m maxsize unlimited;
#如果一个表空间文件无法支撑,需要给表空间增加多个数据文件
ALTER TABLESPACE test ADD DATAFILE '/home/oracle/oradata/ORCL/test1.dbf' size 5000M autoextend on next 500M maxsize unlimited;
#创建临时表空间,首先查询临时表空间文件的路径
select name from v$tempfile;
#创建临时表空间,大小1G左右基本可以满足
create temporary tablespace test_temp tempfile '/data/oracle/oradata/orcl/test_temp.dbf'
size 100m autoextend on next 32m maxsize 1000m extent management local;
4、创建用户密码与上面创建的表空间形成映射关系
create user ump_test identified by 123456 default tablespace test
temporary tablespace test_tem
5.给创建用户授权
grant all privileges to ump_test;
6.删除表空间
drop tablespace test including contents and datafiles;
有3个选项需要注意:
INCLUDING CONTENTS:指删除表空间中的segments;
INCLUDING CONTENTS AND DATAFILES:指删除segments和datafiles;
CASCADE CONSTRAINTS:删除所有与该空间相关的完整性约束条件。
例:
DROP TABLESPACE FESCO ‘表空间名’ CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
7.修改表空间文件大小
ALTER DATABASE DATAFILE '/home/oracle/oradata/ORCL/test.dbf' RESIZE 32000M;
8.删除用户
DROP USER ump_test CASCADE;
如果提示进程正在使用,通过查看用户的进程,并kill用户进程,然后删除用户。
select sid,serial# from v$session where username='ump_test';
kill进程
alter system kill session '793,12705';
9.数据库乱码解决:
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
10.查看日志文件
SELECT MEMBER FROM v$logfile;
11.查用户和表空间映射关系
select username,default_tablespace,temporary_tablespace from dba_users;
12.查表空间使用情况:
SELECT SUM(bytes) / (1024 * 1024* 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;