/*第1步：创建临时表空间 */ create temporary tablespace tv_temp tempfile 'D:\orcldata\tv\temp.dbf' size 20480M autoextend on next 500M maxsize 30720M extent management local; /*第2步：创建数据表空间 */ create tablespace tv_data nologging datafile 'D:\orcldata\tv\data.dbf' size 4096M autoextend on next 200M maxsize 20480m extent management local; /*第3步：创建用户并指定表空间 */ create user tv identified by tv default tablespace tv_data temporary tablespace tv_temp; /*第4步：给用户授予权限 */
grant connect,resource,dba to username;
grant create session,create table,unlimited tablespace to tv;
1、查看表空间
select tv.tablespace_name "TABLESPACE_NAME",totalspace "TOTALSPACE/M",freespace "FREESPACE/M",round((1-freespace/totalspace)*100,2) "USED%" from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) tv, (select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where tv.tablespace_name=fs.tablespace_name;
TABLESPACE_NAME TOTALSPACE/M FREESPACE/M USED% ------------------------------ ------------ ----------- ---------- KB 1024 1023 .1 SYSAUX 320 3 99.06 SYSTEM 450 9 98 TV 4096 128 96.88 UNDOTBS1 5760 5670 1.56 USERS 5 5 0
6 rows selected.
2、查看临时表空间
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME ------------------------------ SYSTEM UNDOTBS1 SYSAUX TEMP USERS TV KB
7 rows selected.
SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
TABLESPACE_NAME FILE_NAME FILE_SIZE AUT --------------------------------------------------------------------------- TEMP E:\ORCLDATA\TV\TEMP01.DBF 1834 YES
3、增加表空间大小
alter tablespace tv add datafile 'E:\orcldata\tv\data1.dbf' size 4096M;
4、增加临时表空间大小
SQL> alter database tempfile 'E:\orcldata\tv\temp01.dbf' resize 8192M;
Database altered.
