Oracle 表空间常用操作

-- 1、创建表空间

CREATE TABLESPACE TABLENAME
 
         DATAFILE '/home/oracle/tablespace/TRAFFIC.DBF'
 
         SIZE 32M
 
         AUTOEXTEND ON
 
         NEXT 32M MAXSIZE UNLIMITED;

-- 2、查看所有表空间及表空间大小：

select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name;

-- 3、查看所有表空间对应的数据文件：

select tablespace_name,file_name from dba_data_files;

-- 4、修改数据文件大小：

alter database datafile 'D:\oracle\product\10.2.0\oradata\orcl\UNDOTBS01.DBF' RESIZE 4096M;

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

-- 5、表空间使用情况

select a.tablespace_name,
       a.bytes / 1024 / 1024 "sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc;

--当前的连接数

select count(*) from v$process;

--数据库允许的最大连接数

select value from v$parameter where name = 'processes';