oracle表空间（ 查看路径，修改，创建）

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