1 查看表空间路径
select * from dba_data_files;
2 修改表空间大小
ALTER TABLESPACE MAXDATA ADD DATAFILE '/oradata/XX/MAXDATA02.DBF' SIZE 1000M;
可以通过PL/SQL查看。
1. 查看所有表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;
2. 未使用的表空间大小
select tablespace_name,sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;
查看当前用户每个表占用空间的大小:
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name
select 'alter table' ||table_name|| 'move tablespace tbs_name;' table_name from dba_tables;--数据表的表空间
select 'alter table' ||table_name|| 'move lob('||index_name||') store as (tablespace tbs_name);' from dba_indexes;--带lob字段
select 'alter index' ||index_name|| 'rebuild tablespace tbs_name;' index_name from dba_indexes;--索引表空间
参考:http://www.cnblogs.com/xd502djj/archive/2010/11/21/1883467.html
3.创建表空间
-- 创建 DZJC 表空间
CREATE SMALLFILE TABLESPACE "LHCZW" DATAFILE 'F:\oracle\product\10.2.0\oradata\LH\LHCZW' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_INDEX 表空间
CREATE SMALLFILE TABLESPACE "LHCZW_INDEX" DATAFILE 'F:\oracle\product\10.2.0\oradata\LH\LHCZW_index' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_TEMP 表空间
CREATE SMALLFILE TEMPORARY TABLESPACE "LHCZW_TEMP" TEMPFILE 'F:\oracle\product\10.2.0\oradata\LH\LHCZW_temp' SIZE 100M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
-- 创建 DZJC 用户
CREATE USER "LHCZW" PROFILE "DEFAULT" IDENTIFIED BY "123456" DEFAULT TABLESPACE "LHCZW" TEMPORARY TABLESPACE "LHCZW_TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "LHCZW" WITH ADMIN OPTION;
GRANT "DBA" TO "LHCZW" WITH ADMIN OPTION;
4表空间使用率
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024),
2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024),
2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;
5表空间创建存储过程
create or replace procedure PRO_CREATE_TABLESPACE(ts_name in varchar2 --表空间名字
,ts_type in varchar2 default 0 --表空间的类型(0实体表空间,1 临时表空间)
,ts_size in varchar2 default '100' --表空间的大小(M)
,ts_nextsize in varchar2 default '100' --表空间的大小(M)
) authid current_user is
m_sql_st varchar2(300);
m_sql_temp varchar2(300);
m_sql varchar2(300);
m_ts_path varchar2(300);
m_ts_name varchar2(300);
begin
begin
--0 表空间大写
select upper(ts_name) into m_ts_name from dual;
--1 表空间路径
select SUBSTR(FILE_NAME, 1, instr(FILE_NAME, '\', -1))||m_ts_name||'.DBF'
into m_ts_path
from (select t.FILE_NAME from dba_data_files t order by t.FILE_ID desc)
where rownum < 2;
--2 表空间语句
--2.1实体表空间
m_sql_st :='CREATE SMALLFILE TABLESPACE "'||m_ts_name||'" DATAFILE '''||m_ts_path||''' SIZE '||ts_size||'M AUTOEXTEND ON NEXT '
||ts_nextsize||'M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ';
--2.2临时表空间
m_sql_temp :='CREATE SMALLFILE TEMPORARY TABLESPACE "'||m_ts_name||'" TEMPFILE '''||m_ts_path||''' SIZE '||ts_size||'M AUTOEXTEND ON NEXT '
||ts_nextsize||'M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M ';
if ts_type=0 then
m_sql:=m_sql_st;
else
m_sql:=m_sql_temp;
end if;
--打印 set serveroutput on;
dbms_output.put_line(m_ts_path);
dbms_output.put_line(m_sql);
--执行
execute immediate m_sql ;
end;
commit;
end PRO_CREATE_TABLESPACE;
/*
-- 创建 DZJC 表空间
CREATE SMALLFILE TABLESPACE "LH" DATAFILE 'D:\workAppliction\orcl\oradata\LH'
SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_INDEX 表空间
CREATE SMALLFILE TABLESPACE "LH_INDEX" DATAFILE 'D:\workAppliction\orcl\oradata\LH_index'
SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
-- 创建 DZJC_TEMP 表空间
CREATE SMALLFILE TEMPORARY TABLESPACE "LH_TEMP" TEMPFILE 'D:\workAppliction\orcl\oradata\LH_temp'
SIZE 100M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
*/
http://blog.csdn.net/pioayang/article/details/24357201
6删除表空间
1.首先看一下是不是已经使用了OMF
sql>show parameter db_create
查看参数db_create_file_dest,如果已经设置
则:drop tablespace tablespacename 就可以直接删除表空间以及相应的数据文件
2.如果没使用OMF,则:
drop tablespace tablespacename including contents and datafiles