Oracle Datafiles

原创 2004年08月12日 10:08:00

Oracle Datafiles

Version 9.2

General Information
Data Dictionary Objects Related To Data Files file$

dba_data_files
dba_free_space
Notes:
  • x
  • x
  • x
 
Create Data Files
   
 
   
 
   
 
   
 
Alter Data Files
Resize An Existing Tablespace Datafile ALTER DATABASE DATAFILE '<data_file_name>'
RESIZE <n> K|M;

Beware that you can only decrease the size of the datafile with the space that is free between highest used block of the datafile and the last block of the file. If the tablespace is fragmented, the free spaces between extents cannot be deallocated this way. Check dba_free_space for details.
ALTER DATABASE DATAFILE 'c:/oracle/oradata/orabase/tools02.tom' RESIZE 50M;

ALTER DATABASE DATAFILE 'c:/oracle/oradata/orabase/tools03.dan' RESIZE 50M;
Add A Datafile To An Existing Tablespace ALTER TABLESPACE <tablespace_name>
ADD DATAFILE '<path_and_file_name>' SIZE <n>K|M;
ALTER TABLESPACE tools
ADD DATAFILE 'c:/oracle/oradata/orabase/tools02.tom' SIZE 20M;
Move Tablespace Datafile SHUTDOWN

STARTUP MOUNT

Copy the datafile to it's new location

ALTER DATABASE RENAME FILE
'<old_full_path>' TO '<new_full_path>';

then

ALTER DATABASE OPEN;

After that, you can safely delete the old datafile.
SQL> CONN / AS SYSDBA

SQL> SHUTDOWN

SQL> STARTUP MOUNT

SQL> HOST

$ cp /u01/oradata/tools01.dbf /u06/oradata/tools01.dbf

$ EXIT

SQL> ALTER DATABASE RENAME FILE '/u01/oradata/tools01.dbf'
     TO '/u06/oradata/tools01.dbf';

SQL> ALTER DATABASE OPEN;

SQL> HOST

$ rm /u01/oradata/tools.01.dbf

$ EXIT
Move System Tablespace Datafile The data dictionary can never be offline, with the rest of the database operational. Moving the SYSTEM tablespace's datafiles requires a different technique because 'alter
database' generally means you are modifying the controlfile.
All 'RENAME datafile 'x' to 'y' command does is to reset the pointers to the datafiles stored in the Control File which SMON reads at startup)
conn / AS SYSDBA

SHUTDOWN

copy the datafile to its new location with operating system commands.

STARTUP NOMOUNT

ALTER DATABASE MOUNT;

ALTER DATABASE RENAME FILE 'c:/oracle/oradata/system01.dbf' TO 'd:/oracle/oradata/system01.dbf';

ALTER DATABASE OPEN;
 
Alter Temp Files
Resize alter database tempfile <temp file name>
resize <integer> M;
ALTER DATABASE TEMPFILE 'temp01.dbf'
RESIZE 100M;
Drop alter database tempfile <temp file name>
drop including datafiles;
ALTER DATABASE TEMPFILE 'temp01.dbf'
DROP INCLUDING DATAFILES;
 
Drop Data File
Drop A Datafile:
Works only on tablespaces consisting of a single datafile
ALTER DATABASE DATAFILE '<file_name>' [offline] DROP;
ALTER DATABASE DATAFILE   'C:/ORACLE/ORADATA/ORABASE/TOOLS02.TOM'
OFFLINE DROP;
 
Data File Related Queries
  SELECT file_name, tablespace_name,
       bytes/1024/1024 MB, blocks
FROM dba_data_files
UNION ALL
SELECT file_name, tablespace_name,
       bytes/1024/1024 MB, blocks
FROM dba_temp_files
ORDER BY tablespace_name, file_name;
Uneven Datafile Usage Within A Tablespace CREATE TABLESPACE bowie_data
DATAFILE 'c:/bowie/bowie_data01.dbf' size 10m,
'c:/bowie/bowie_data02.dbf' size 10m,
'c:/bowie/bowie_data03.dbf' size 10m
uniform size 64;

col segment_name format a30

SELECT file_id, file_name
FROM dba_data_files
WHERE tablespace_name = 'BOWIE_DATA';

CREATE TABLE one (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE two (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE three (x NUMBER) TABLESPACE bowie_data;
CREATE TABLE four (x NUMBER) TABLESPACE bowie_data;

Now we've create 4 tables in this tablespace. Let's see which data file they were placed in ...

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA';

Note that *all* tables have their first extent created in the *first* data file defined to the tablespace.

Now lets grow these tables and see what happens next.

ALTER TABLE one ALLOCATE EXTENT;
ALTER TABLE two ALLOCATE EXTENT;
ALTER TABLE three ALLOCATE EXTENT;
ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

... and the second extent of each table has been created in the second data file of the tablespace.

If a particular table were to keep growing ...

ALTER TABLE four ALLOCATE EXTENT;
ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

you can see how the extents get allocated to the data files in a round robin fashion. But the first extent is allocate to the first data file (providing it has sufficent space) ...

CREATE TABLE five (x NUMBER) TABLESPACE bowie_data;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

Let's add a new data file. What happens now ...

ALTER TABLESPACE bowie_data
ADD DATAFILE 'c:/bowie/bowie_data04.dbf' SIZE 10M;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

A new extent is added to table four. And uses the new datafile.

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

ALTER TABLE four ALLOCATE EXTENT;

SELECT segment_name, file_id
FROM dba_extents
WHERE tablespace_name = 'BOWIE_DATA'
ORDER BY segment_name;

... and now the new file is used. The files are still used in a round robin fashion with the new file slipping in.

Note how file 16 is the *most* used file and file 19 is the least. If I were to allocate several new tables that were only 1 or 2 extents in size, see how file 16 would be the one to be most "filled".

Related Topics
 

MySQL的非正式备份恢复方法--MySQL Datafiles

试验环境:     数据库:MySQL 5.5.8     主服务器:SERVERHOST     备用服务器:SERVERBAK       其中SERVERHOST为主服务器,每周备份数...
  • alzhuzhu
  • alzhuzhu
  • 2013年04月11日 11:04
  • 703

【Lucene学习笔记】索引的增删改查与查看工具

一、基本增删改查 二、索引查看工具Luke 三、关于Lucene4.10.2中的FieldType 而在最新的4.10.2版本中是由以下这些参数来与之对应的: TextField.TYPE_STORE...
  • u011526340
  • u011526340
  • 2014年12月18日 22:39
  • 872

Oracle数据库 导入导出 删除重建

以oracle11g为例,今天整理了一下数据库的导入导出、创建删除命令,包括数据泵的导入导出,,现将操作及语句记录下来。 1、用dba登陆oracle,创建一个名为test_space的表空间,首...
  • seeseait
  • seeseait
  • 2014年11月08日 23:38
  • 2624

oracle11g dataguard中standby库文件坏块的修复过程

问题描述: 机房断电了,所以primary和standby库都是直接断电,然后我都设置了开机自启动oracle,所以第二天我来看的时候,primary和standby都启动了,归档日志也传输到stan...
  • mchdba
  • mchdba
  • 2015年10月19日 21:01
  • 3214

Oracle OMF特性导致的DataGuard Duplicate构建备库的Datafiles命名问题

今天凌晨数据库系统做架构变更,构建DataGuard Max Availability主备模式。由于之前已经在自己新构建的测试环境下进行了多次的演练,本以为会在半个小时结束搭建Dg的战斗,支援其它战线...
  • ltc1183705927
  • ltc1183705927
  • 2017年01月22日 21:17
  • 213

oracle常用函数汇总——ORACLE日期时间函数大全

TO_DATE格式(以时间:2007-11-02   13:45:25为例)         Year:               yy two digits 两位年            ...
  • JR_Way
  • JR_Way
  • 2016年06月20日 20:21
  • 14827

Oracle字符集 彻底搞懂 字符集

以下是对Oracle中的字符集进行了详细的分析介绍,需要的朋友可以参考下   基本概念 字符集(Character set):是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称,包括各国家...
  • snowfoxmonitor
  • snowfoxmonitor
  • 2016年08月17日 08:35
  • 1032

mysql与oracle的对比

由于最近三年一直用mysql,好多oracle知识都有点生疏了,现在回顾下并记录下 1.表空间 mysql的表空间有共享表空间和独占表空间 独占表空间,其实就是一张表一个表空间,其实也就是一张表...
  • java_best
  • java_best
  • 2016年11月23日 09:51
  • 771

MySql与Oracle的几个主要区别

一、并发性   并发性是oltp数据库最重要的特性,但并发涉及到资源的获取、共享与锁定。   mysql: mysql以表级锁为主,对资源锁定的粒度很大,如果一个session对一个表加锁时间过长,会...
  • c332472988
  • c332472988
  • 2016年10月13日 09:40
  • 5800

Oracle 各种名词术语

1 高水位 定义 所有的oracle段(segments,在此,为了理解方便,建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限,我们把这个上限称为"high water ma...
  • ggxxkkll
  • ggxxkkll
  • 2014年01月20日 21:23
  • 1358
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle Datafiles
举报原因:
原因补充:

(最多只允许输入30个字)