一、创建表空间
--创建用户表空间
CREATE TABLESPACE mytablespace LOGGING --nologging 则为不生重做日志,在TABLESPACE前
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\mytp01.DBF' SIZE 10M
AUTOEXTEND ON NEXT 5M
MAXSIZE 20M --MAXSIZE UNLIMITED 大小不受限制
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
说明:
第一行CREATE TABLESPACE默认为CREATE PERMANENT TABLESPACE永久表空间(表空间类型分为PERMANENT永久,TEMPORARY临时,UNDO撤销)
第五行local表示区管理类型为本地管理(local本地管理,TEMPORARY字典管理),AUTOALLOCATE表示段管理类型为自动管理(段管理类型分为AUTO和MANUAL)
--创建临时表空间
CREATE TEMPORARY TABLESPACE mytemporary
TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\mytemp01.DBF' SIZE 10M
AUTOEXTEND ON NEXT 5M
MAXSIZE 10M
EXTENT MANAGEMENT LOCAL;
--创建用户
CREATE USER liut IDENTIFIED BY liut --用户名liut密码liut
DEFAULT TABLESPACE mytablespace
TEMPORARY TABLESPACE mytemporary;
--授予角色(多种权限)
GRANT CONNECT,RESOURCE,DBA TO liut;
二、增加表空间
ALTER TABLESPACE tablespace_name ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M; --有这一行则允许文件自动增长,没有则不允许
三、修改表空间
1.允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
2.手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF' RESIZE 100M;
四、查看表空间大小
SELECT tablespace_name,sum_m as total_m, (sum_m-free_m) as used_m,free_m,
to_char(100*(sum_m-free_m)/sum_m, '99.99') || '%' AS pct_used,
to_char(100*free_m/sum_m, '99.99') || '%' AS pct_free
FROM
( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m
FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name,sum(bytes/1024/1024) AS free_m
FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name(+)
order by free_m;
--创建用户表空间
CREATE TABLESPACE mytablespace LOGGING --nologging 则为不生重做日志,在TABLESPACE前
DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\mytp01.DBF' SIZE 10M
AUTOEXTEND ON NEXT 5M
MAXSIZE 20M --MAXSIZE UNLIMITED 大小不受限制
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
说明:
第一行CREATE TABLESPACE默认为CREATE PERMANENT TABLESPACE永久表空间(表空间类型分为PERMANENT永久,TEMPORARY临时,UNDO撤销)
第五行local表示区管理类型为本地管理(local本地管理,TEMPORARY字典管理),AUTOALLOCATE表示段管理类型为自动管理(段管理类型分为AUTO和MANUAL)
--创建临时表空间
CREATE TEMPORARY TABLESPACE mytemporary
TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\mytemp01.DBF' SIZE 10M
AUTOEXTEND ON NEXT 5M
MAXSIZE 10M
EXTENT MANAGEMENT LOCAL;
--创建用户
CREATE USER liut IDENTIFIED BY liut --用户名liut密码liut
DEFAULT TABLESPACE mytablespace
TEMPORARY TABLESPACE mytemporary;
--授予角色(多种权限)
GRANT CONNECT,RESOURCE,DBA TO liut;
二、增加表空间
ALTER TABLESPACE tablespace_name ADD DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP04.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M; --有这一行则允许文件自动增长,没有则不允许
三、修改表空间
1.允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;
2.手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF' RESIZE 100M;
四、查看表空间大小
SELECT tablespace_name,sum_m as total_m, (sum_m-free_m) as used_m,free_m,
to_char(100*(sum_m-free_m)/sum_m, '99.99') || '%' AS pct_used,
to_char(100*free_m/sum_m, '99.99') || '%' AS pct_free
FROM
( SELECT tablespace_name,sum(bytes)/1024/1024 AS sum_m
FROM dba_data_files GROUP BY tablespace_name),
( SELECT tablespace_name AS fs_ts_name,sum(bytes/1024/1024) AS free_m
FROM dba_free_space GROUP BY tablespace_name )
WHERE tablespace_name = fs_ts_name(+)
order by free_m;