注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

太极虫的博客

好好学习,天天向上

 
 
 

日志

 
 

Oracle Data & Temp Files  

2008-04-30 13:40:34|  分类: ORACLE技术 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

Oracle Data & Temp Files

Version 11.1
 

General Information
Dependencies

file$ ts$ x$ktfbhc
dba_data_files dba_temp_files  
dba_free_space gv$dbfile  

 
Alter Data Files

Resize An Existing Datafile

ALTER DATABASE DATAFILE '<data_file_name | data_file_number>'

RESIZE <n> K|M|G|T|P|E;

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|G|T|P|E;

ALTER TABLESPACE tools

ADD DATAFILE 'c:\oracle\oradata\orabase\tools02.tom' SIZE 20M;

ALTER DATABASE

CREATE DATAFILE '<path_and_file_name>' SIZE <n>K|M|G|T|P|E

AS '<tablespace_name>';

ALTER DATABASE

CREATE DATAFILE 'c:\oracle\oradata\orabase\uwdata03.dbf' SIZE 1G

AS 'UWDATA';

Move Tablespace Datafile

Can also be used to move SYSTEM, SYSAUX, and TEMP tablespace files

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;

-- then you can safely delete the old datafile.

conn / as sysdba

shutdown immediate;

startup mount

host

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

$ exit

alter database rename file '/u01/oradata/tools01.dbf'

to '/u06/oradata/tools01.dbf';

alter database open

host

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

$ exit

Autoextend

ALTER DATABASE DATAFILE <'data_file_name' | data_file_number> 

AUTOEXTEND <OFF | ON [NEXT SIZE <n>K|M|G|T|P|E

MAXSIZE <UNLIMITED | <n>K|M|G|T|P|E>;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' AUTOEXTEND ON MAXSIZE UNLIMITED;

Online / Offline

ALTER DATABASE DATAFILE <'data_file_name' | data_file_number> 

<ONLINE | OFFLINE [FOR DROP]>;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' OFFLINE;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' ONLINE;

ALTER DATABASE DATAFILE 'u06/oradata/tools01.dbf' OFFLINE FOR DROP;

 
Alter Temp Files
Resize alter database tempfile <temp file name>

resize <integer> K|M|G|T|P|E;

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 ALTER DATABASE DATAFILE '<file_name_or_file_number>' [offline] DROP;
set linesize 121

col file_name format a80

SELECT file_name, SUM(bytes)/1024/1024 DF_SIZE

FROM dba_data_files

GROUP BY file_name;

ALTER TABLESPACE users ADD datafile SIZE 50M;

SELECT file_name, SUM(bytes)/1024/1024 DF_SIZE

FROM dba_data_files

GROUP BY file_name;

ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\UKOUG\DATAFILE\O1_MF_USERS_35HCKNFO_.DBF'

OFFLINE DROP;

or

ALTER TABLESPACE users DROP DATAFILE 'C:\ORACLE\PRODUCT\ORADATA\UKOUG\DATAFILE\O1_MF_USERS_35HCKNFO_.DBF';

 
Data File Related Queries
Data File Information set linesize 121

col file_name format a45

col tablespace_name format a20

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;

Data File Block Sizing

-- as root created a file system with block size 1024

mkfs.ext3 -b 1024 /dev/sda3

-- mounted it

mount /dev/sda3 /mnt/test

-- and issued

iostat -d -t -x /dev/sda3

-- in another shell

dd if=/tmp/foo of=/mnt/test/foo2 bs=1024k

-- the results

Time: 08:47:05

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s

avgrq-sz

avgqu-sz await svctm %util

/dev/sda3 0.00 0.00 2.00 0.00 4.00 0.00 2.00 0.00

2.00

0.10 50.00 50.00 1.00

Time: 08:47:10

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s rkB/s wkB/s

avgrq-sz

avgqu-sz await svctm %util

/dev/sda3 0.00 10200.40 0.20 83.60 0.40 20568.00 0.20

10284.00 245.

45 67.92 810.50 31.03 26.00

-- do the math

wsec/s / wrqm/s = 20568.00 / 10200.40 = 2,017

-- Roughly two sectors of 512 bytes. So write were in blocks of 1K.

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
Tablespaces

 
Contact Us ? Legal Notices and Terms of Use ? Privacy Statement

  评论这张
 
阅读(46)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017