oracle 软件
/*第1步：创建数据表空间 */create tablespace user_data logging datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf 'size 50m autoextend on next 50m maxsize 20480m extent management local; 如：
/*第2步：创建临时表空间 */create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'size 50m autoextend on next 50m maxsize 20480m extent management local; 如：
/*第3步：创建用户并指定表空间 */create user username identified by password default tablespace user_data /* 数据表空间 */temporary tablespace user_temp; /* 临时表空间 */如：
/*第4步：给用户授予权限 */grant connect,resource,dba to username;完成以上四步即完成用户及表空间的创建。
/*删除用户及表空间*/drop user username cascade;DROP TABLESPACE user_data INCLUDING CONTENTS AND DATAFILES;DROP temporary TABLESPACE user_temp INCLUDING CONTENTS AND DATAFILES;
/*查看表空间和临时表空间使用情况*/select * from (Select a.tablespace_name '表空间名', to_char(a.bytes / 1024 / 1024, '999,999') '表空间总(M)', to_char(b.bytes / 1024 / 1024, '999,999.99') '空闲空间(M)', to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024, '999,999.99') '已使用空间(M)', to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' '使用比' from (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name union all select c.tablespace_name, to_char(c.bytes / 1024 / 1024, '999,999') total_bytes, to_char((c.bytes - d.bytes_used) / 1024 / 1024, '999,999.99') free_bytes, to_char(d.bytes_used / 1024 / 1024, '999,999.99') use_bytes, to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use from (select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) c, (select tablespace_name, sum(bytes_cached) bytes_used from v$temp_extent_pool group by tablespace_name) d where c.tablespace_name = d.tablespace_name) order by '表空间名'
/* 增加数据文件，并设置自动扩展*/alter tablespace dpp_date add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DPP_DATE02.DBF' size 1024m autoextend on next 50m maxsize 200m;
/* 增加数据文件，并设置为无限扩展*/alter tablespace dpp_date add datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\DPP_DATE02.DBF' size 1024m autoextend on next 50m maxsize unlimited;
/*重新分配数据文件大小*/alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF' resize 500m;
/*重新分配临时表空间数据文件大小*/alter database tempfile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF' resize 500M;