【表空间查询】

查看表空間 登錄system賬戶下
select dbf.tablespace_name,
dbf.totalspace “总量(M)”,
dbf.totalblocks as 总块数,
dfs.freespace “剩余总量(M)”,
dfs.freeblocks “剩余块数”,
(dfs.freespace / dbf.totalspace) * 100 “空闲比例”
from (sum(t.bytes) / 1024 / 1024 totalspace,
sum(t.blocks) totalblocks
from dba_data_files t
group by t.tablespace_name) dbf,
(select tt.tablespace_name,
sum(tt.bytes) / 1024 / 1024 freespace,
sum(tt.blocks) freeblocks
from dba_free_space tt
group by tt.tablespace_name) dfs
where trim(dbf.tablespace_name) = trim(dfs.tablespace_name)

            SELECT Total.name "Tablespace Name",            Free_space, (total_space-Free_space) Used_space, total_space            FROM            (select tablespace_name, sum(bytes/1024/1024) Free_Space            from sys.dba_free_space            group by tablespace_name            ) Free,            (select b.name, sum(bytes/1024/1024) TOTAL_SPACE            from sys.v_$datafile a, sys.v_$tablespace B            where a.ts# = b.ts#            group by b.name            ) Total            WHERE Free.Tablespace_name = Total.name

修改表空间名称
1、 使用oracle用户登录执行
sqlplus/assysdba2、执行修改表空间命令如下SQL>altertablespaceTESTrenametoTEST1;注：可连续对多个表空间进行重命名4、确认表空间名已经修改SQL>selectnamefromvtablespace;
SQL>exit

Oracle数据库的表空间及数据库文件的迁移方法
一、移动表空间文件
1、修改表空间为离线状态
ALTER TABLESPACE TS_PARTITION_000 OFFLINE;
2、复制表空间对应文件到新的位置
cp /opt/oracle/11g/dbs/TS_PARTITION_000 /oradata/DTCDB/tbs/TS_PARTITION_000

3、修改表空间数据文件路径
ALTER TABLESPACE TS_PARTITION_000 RENAME DATAFILE ‘/opt/oracle/11g/dbs/TS_PARTITION_000’ TO ‘/oradata/DTCDB/tbs/TS_PARTITION_000’;
4、修改表空间为在线状态
ALTER TABLESPACE TS_PARTITION_000 ONLINE;
5、删除表空间对应的原来的数据库文件
rm -rf /opt/oracle/11g/dbs/TS_PARTITION_000 ##此为linux

二、移动数据库文件
1、关闭数据库
SHUTDOWN IMMEDIATE;
2、复制数据库文件
cp /opt/oracle/11g/dbs/dbfile.dbf /oradata/DTCDB/tbs/dbfile.dbf
3、挂接方式启动数据库
startup mount
4、修改数据库文件路径
ALTER DATABASE RENAME FILE ‘/opt/oracle/11g/dbs/dbfile.dbf’ TO ‘/oradata/DTCDB/tbs/dbfile.dbf’;
5、打开数据库
ALTER DATABASE OPEN;

创建表空间
create tablespace pdms
logging
datafile ‘D:\Software\DB\Oracle11\oradata\orcl\PDMS.DBF’
size 400m
autoextend on
next 10m maxsize 5120m
extent management local;

create temporary tablespace pdms_temp
tempfile ‘D:\Software\DB\Oracle11\oradata\orcl\PDMS_TEMP.DBF’
size 100m
autoextend on
next 10m maxsize 2048m
extent management local;

修改用户默认、临时表空间
alter user pdmstest default tablespace pdms_test;
alter user pdmstest temporary tablespace pdms_temp_test;

create user username identified by password
default tablespace user_data
temporary tablespace user_temp;

删除表空间以及数据文件
drop tablespace tablespacename including contents and datafiles cascade constraints ;

查看表空间
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;

修改表空间文件
先把第一个表空间文件自增长取消
SQL> ALTER DATABASE datafile ‘D:/oracle/oradata/orcl/PDMS’ autoextend off;
再创建表空间文件
SQL> ALTER tablespace pdms add datafile ‘D:/oracle/oradata/orcl/PDMS_20150504.DBF’ size 10240M autoextend on next 100M maxsize unlimited;
附：
—-查询表空间使用情况—
使用DBA权限登陆
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 4 DESC;

–修改表空间为自增长–
SQL>ALTER DATABASE
DATAFILE ‘/u01/oracle/oradata/orcl/ccen01.dbf’ AUTOEXTEND
ON NEXT 50M MAXSIZE UNLIMITED
–查看表空间是否具有自动扩展的能力–
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

查询表空间文件所在路径

select * from dba_data_files

增加已有分区表的分区
–查看 meas_data 表是不是分区表
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ‘MEAS_DATA’;
–查看当前用户的所有分区
SELECT * FROM USER_TAB_PARTITIONS;
–创建分区
create TABLESPACE PDMS_BEFORE_201508 logging datafile ‘D:\Software\DB\Oracle11\oradata\orcl\PDMS_BEFORE_201508.DBF’ size 5m autoextend on next 50m maxsize 10240m extent management local;
–给 meas_data 增加分区
alter table MEAS_DATA add partition PDMS_BEFORE_201508 values less than (‘2014’) tablespace PDMSDB;

1.创建个分区表
SQL> create table p_range_test
2 (id number,name varchar2(100))
3 partition by range(id)(
4 partition t_p1 values less than (10),
5 partition t_p2 values less than (20),
6 partition t_p3 values less than (30)
7 );
Table created.
2.查第一步创建好分区表的信息
SQL> col TABLE_NAME for a20
SQL> col partition_name for a20
SQL> col HIGH_VALUE for a10
SQL> col TABLESPACE_NAME for a15
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name=’P_RANGE_TEST’ order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

---

P_RANGE_TEST T_P1 10 TP1
P_RANGE_TEST T_P2 20 TP1
P_RANGE_TEST T_P3 30 TP1
从上面看出已有3个分区
3.添加一个分区t_p4
SQL> alter table p_range_test add partition t_p4 values less than(40);
Table altered.
顺序添加完成，当然这个不是LZ想要的吧？
4.再第三步添加好分区的信息
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name=’P_RANGE_TEST’ order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

---

P_RANGE_TEST T_P1 10 TP1
P_RANGE_TEST T_P2 20 TP1
P_RANGE_TEST T_P3 30 TP1
P_RANGE_TEST T_P4 40 TP1
从这个实验结果可以看到t_p4分区已经创建成功
5.对于LZ的问题：如果在创建RANGE分区表的时候指定了maxvalue，不可以添加分区（需要使用split方法来处理）
继续实验来验证是否可行 ！！！
6.创建带有maxvalue的分区表
SQL> drop table p_range_test purge;
create table p_range_test (id number,name varchar2(100))
partition by range(id)(
partition t_p1 values less than (10),
partition t_p2 values less than (20),
partition t_p3 values less than (30),
partition t_pmax values less than (maxvalue));
Table dropped.
SQL> 2 3 4 5 6
Table created.

7.此时添加分区时会报如下的错误
SQL> alter table p_range_test add partition t_p4 values less than(40);
alter table p_range_test add partition t_p4 values less than(40)
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
添加分区失败？那有没有办法搞定呢，对于牛B的oracle来说肯定有办法了，处理方法就是使用split来搞！
8.再查一下当前的分表的信息
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name=’P_RANGE_TEST’ order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

---

P_RANGE_TEST T_P1 10 TP1
P_RANGE_TEST T_P2 20 TP1
P_RANGE_TEST T_P3 30 TP1
P_RANGE_TEST T_PMAX MAXVALUE TP1

9.注意，这步就是关键的一步！！！！！使用split完成上面没有完成的分区任务
SQL> alter table p_range_test split partition t_pmax at (40) into (partition, partition t_pmax);
Table altered.
10.再查一下当前的分表的信息
SQL> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name=’P_RANGE_TEST’ order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME

---

P_RANGE_TEST T_P1 10 TP1
P_RANGE_TEST T_P2 20 TP1
P_RANGE_TEST T_P3 30 TP1
P_RANGE_TEST SYS_P41 40 TP1
P_RANGE_TEST T_PMAX MAXVALUE TP1