梦想有多远

数据文件和临时文件是数据库的数据在操作系统上的物理文件

每个数据文件都有两个文件号相关联，他们是FILE#或者FILE_ID，这些都可以通过查看V$DATAFILE、V$TEMPFILE或dba_data_files、dba_temp_files

例如：

SQL> select file#,name from v$datafile;

     FILE# NAME
———- ————————————————–
         1 F:\APP\BOSON.SUN\ORADATA\ORCL\SYSTEM01.DBF
         2 F:\APP\BOSON.SUN\ORADATA\ORCL\SYSAUX01.DBF
         3 F:\APP\BOSON.SUN\ORADATA\ORCL\UNDOTBS01.DBF
         4 F:\APP\BOSON.SUN\ORADATA\ORCL\USERS01.DBF
         5 F:\APP\BOSON.SUN\ORADATA\ORCL\EXAMPLE01.DBF
         6 F:\APP\BOSON.SUN\ORADATA\ORCL\P1.DBF
         7 F:\APP\BOSON.SUN\ORADATA\ORCL\P2.DBF
         8 F:\APP\BOSON.SUN\ORADATA\ORCL\P3.DBF
         9 F:\APP\BOSON.SUN\ORADATA\ORCL\P4
        10 F:\APP\BOSON.SUN\ORADATA\ORCL\DEMO.DBF
        11 F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\BOSON.DBF

     FILE# NAME
———- ————————————————–
        12 F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\DEMO01.DBF

已选择12行。

SQL> select file_name,file_id,tablespace_name from dba_data_files;

FILE_NAME                                             FILE_ID TABLESPACE
————————————————– ———- ———-
F:\APP\BOSON.SUN\ORADATA\ORCL\USERS01.DBF                   4 USERS
F:\APP\BOSON.SUN\ORADATA\ORCL\UNDOTBS01.DBF                 3 UNDOTBS1
F:\APP\BOSON.SUN\ORADATA\ORCL\SYSAUX01.DBF                  2 SYSAUX
F:\APP\BOSON.SUN\ORADATA\ORCL\SYSTEM01.DBF                  1 SYSTEM
F:\APP\BOSON.SUN\ORADATA\ORCL\EXAMPLE01.DBF                 5 EXAMPLE
F:\APP\BOSON.SUN\ORADATA\ORCL\P1.DBF                        6 P1
F:\APP\BOSON.SUN\ORADATA\ORCL\P2.DBF                        7 P2
F:\APP\BOSON.SUN\ORADATA\ORCL\P3.DBF                        8 P3
F:\APP\BOSON.SUN\ORADATA\ORCL\P4                            9 P4
F:\APP\BOSON.SUN\ORADATA\ORCL\DEMO.DBF                     10 DEMO
F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\BOSON.DBF             11 BOSON

FILE_NAME                                             FILE_ID TABLESPACE
————————————————– ———- ———-
F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\DEMO01.DBF            12 DEMOO

已选择12行。

数据库中只要包含SYSTEM SYSAUX两个数据文件（我所看的SG是10G），数据库中包含文件多少由参数DB_FILES决定，此参数为静态参数需要重起数据库生效。

在往表空间里添加数据文件时，需要考虑如下因素：

Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.

Operating systems impose limits on the number and size of datafiles.来自操作系统的限制）

The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.

When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files。如果你在添加数据文件时，超过MAXDATAIFLE限制，但是没有超过DB_FILES限制，那么控制文件将自动扩展。10.2以前的版本必须通过重建控制文件完成

以下时关于数据文件创建的一些语句：

数据文件大小调整：

有两种方式：手动和自动，首先介绍下自动

一般是在创建的时候指定是否允许自动扩展：

ALTER TABLESPACE users
    ADD DATAFILE ‘/u02/oracle/rbdb1/users03.dbf’ SIZE 10M
      AUTOEXTEND ON （这里就允许自动扩展
      NEXT 512K 每次增加512K
      MAXSIZE 250M; 文件最大为250M

查询自己创建的数据库文件是否允许自动扩展，可以用如下语句：

SQL> select file_name,autoextensible from dba_data_files;

FILE_NAME                                          AUT
————————————————– —
F:\APP\BOSON.SUN\ORADATA\ORCL\USERS01.DBF          YES
F:\APP\BOSON.SUN\ORADATA\ORCL\UNDOTBS01.DBF        YES
F:\APP\BOSON.SUN\ORADATA\ORCL\SYSAUX01.DBF         YES
F:\APP\BOSON.SUN\ORADATA\ORCL\SYSTEM01.DBF         YES
F:\APP\BOSON.SUN\ORADATA\ORCL\EXAMPLE01.DBF        YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P1.DBF               YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P2.DBF               YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P3.DBF               YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P4                   NO
F:\APP\BOSON.SUN\ORADATA\ORCL\DEMO.DBF             NO
F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\BOSON.DBF     NO

FILE_NAME                                          AUT
————————————————– —
F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\DEMO01.DBF    NO

已选择12行。

 通过查询可以看到BOSON.DBF这个文件没有启动自动扩展，可以通过如下语句启用：

SQL> alter database datafile ‘F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\BOSON.DB
 ’ autoextend on;

数据库已更改。

SQL> select file_name,autoextensible from dba_data_files;

FILE_NAME                                          AUT
————————————————– —
F:\APP\BOSON.SUN\ORADATA\ORCL\USERS01.DBF          YES
F:\APP\BOSON.SUN\ORADATA\ORCL\UNDOTBS01.DBF        YES
F:\APP\BOSON.SUN\ORADATA\ORCL\SYSAUX01.DBF         YES
F:\APP\BOSON.SUN\ORADATA\ORCL\SYSTEM01.DBF         YES
F:\APP\BOSON.SUN\ORADATA\ORCL\EXAMPLE01.DBF        YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P1.DBF               YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P2.DBF               YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P3.DBF               YES
F:\APP\BOSON.SUN\ORADATA\ORCL\P4                   NO
F:\APP\BOSON.SUN\ORADATA\ORCL\DEMO.DBF             NO
F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\BOSON.DBF     YES

FILE_NAME                                          AUT
————————————————– —
F:\APP\BOSON.SUN\PRODUCT\11.1.0\DB_1\DEMO01.DBF    NO

已选择12行。

看已经启用了，关闭也容易就是autoextend off

手动调整数据文件大小：

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'    RESIZE 100M;

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE FOR DROP;（FOR DROP 就是不需要回到ONLINE状态）

要想更改一个表空名字或位置，一般需要如下步骤：

首先在 数据库打开的状态下，把表空间OFFLINE

ALTER TABLESPACE users OFFLINE NORMAL;

然后在系统上重命名名字

然后用如下语句重命名：

ALTER TABLESPACE users     RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',                     '/u02/oracle/rbdb1/user2.dbf'                  TO '/u02/oracle/rbdb1/users01.dbf',                      '/u02/oracle/rbdb1/users02.dbf';  然后把表空间ONLINE，然后 最好备份下数据库
 
 
 

下面演示在一个单独的表空间中调整文件位置：在调整前确认以下事项：

Here is a sample procedure for relocating a datafile.

Assume the following conditions:

• An open database has a tablespace named users that is made up of datafiles all located on the same disk.
• The datafiles of the users tablespace are to be relocated to different and separate disk drives.
• You are currently connected with administrator privileges to the open database.
• You have a current backup of the database.

步骤：

Complete the following steps:

1. If you do not know the specific file names or sizes, you can obtain this information by issuing the following query of the data dictionary view DBA_DATA_FILES:

   SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES   2> WHERE TABLESPACE_NAME = 'USERS';  FILE_NAME                                  BYTES ------------------------------------------ ---------------- /u02/oracle/rbdb1/users01.dbf              102400000 /u02/oracle/rbdb1/users02.dbf              102400000

2. Take the tablespace containing the datafiles offline:

   ALTER TABLESPACE users OFFLINE NORMAL;

3. Copy the datafiles to their new locations and rename them using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in “Copying Files Using the Database Server”.

   Note:

   You can temporarily exit SQL*Plus to execute an operating system command to copy a file by using the SQL*Plus HOST command.

4. Rename the datafiles within the database.The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

   Use the ALTER TABLESPACE...RENAME DATAFILE statement.

   ALTER TABLESPACE users     RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',                     '/u02/oracle/rbdb1/users02.dbf'                  TO '/u03/oracle/rbdb1/users01.dbf',                      '/u04/oracle/rbdb1/users02.dbf';

5. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup

下面介绍重命名、重定位多个数据文件

步骤：

1 启动数据库到MOUNT状态

2 把新命名的文件移动到新的位置

3然后使用ALTER DATABASE命令：

 ALTER DATABASE
    RENAME FILE ‘/u02/oracle/rbdb1/sort01.dbf’,
                ‘/u02/oracle/rbdb1/user3.dbf’
             TO ‘/u02/oracle/rbdb1/temp01.dbf’,
                ‘/u02/oracle/rbdb1/users03.dbf;
4 备份数据库

下面介绍如何删除数据文件

The following example drops the datafile identified by the alias example_df3.f in the ASM disk group DGROUP1. The datafile belongs to the example tablespace.

ALTER TABLESPACE example DROP DATAFILE '+DGROUP1/example_df3.f';

The next example drops the tempfile lmtemp02.dbf, which belongs to the lmtemp tablespace.
ALTER TABLESPACE lmtemp DROP TEMPFILE '/u02/oracle/data/lmtemp02.dbf';
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP       INCLUDING DATAFILES;
 
以下 条件限制数据文件是否允许DROP
 
 
Restrictions for Dropping Datafiles
The following are restrictions for dropping datafiles and tempfiles:

The database must be open.
If a datafile is not empty, it cannot be dropped.

If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
You cannot drop the first or only datafile in a tablespace.

This means that DROP DATAFILE cannot be used with a bigfile tablespace.
You cannot drop datafiles in a read-only tablespace.
You cannot drop datafiles in the SYSTEM tablespace.
If a datafile in a locally managed tablespace is offline, it cannot be dropped.


 
 
利用ORACLE内置包复制文件
 
Copying a File on a Local File System
This section includes an example that uses the COPY_FILE procedure in the DBMS_FILE_TRANSFER package to copy a file on a local file system.
The following example copies a binary file named db1.dat from the /usr/admin/source directory to the /usr/admin/destination directory as db1_copy.dat on a local file system:


In SQL*Plus, connect as an administrative user who can grant privileges and create directory objects using SQL.
Use the SQL command CREATE DIRECTORY to create a directory object for the directory from which you want to copy the file.
 A directory object is similar to an alias for the directory.
For example, to create a directory object called SOURCE_DIR for the /usr/admin/source directory on your computer system, execute the following statement:
CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';

Use the SQL command CREATE DIRECTORY to create a directory object for the directory into which you want to copy the binary file. For example,
to create a directory object called DEST_DIR for the /usr/admin/destination directory on your computer system, execute the following statement:
CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';

Grant the required privileges to the user who will run the COPY_FILE procedure. In this example, the strmadmin user runs the procedure.
GRANT EXECUTE ON DBMS_FILE_TRANSFER TO strmadmin;  GRANT READ ON DIRECTORY source_dir TO strmadmin;  GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;

Connect as strmadmin user:
CONNECT strmadmin/strmadminpw

Run the COPY_FILE procedure to copy the file:
BEGIN   DBMS_FILE_TRANSFER.COPY_FILE(         source_directory_object       =>  'SOURCE_DIR',         source_file_name              =>  'db1.dat',         destination_directory_object  =>  'DEST_DIR',         destination_file_name         =>  'db1_copy.dat'); END; /


 
 
 
 

This entry was posted on 星期五, 十一月 30th, 2007 at 13:33 and is filed under oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.