Storage and file Management

Storage and file Management

Objective

(1) Master the establishment and management of the table space.

(2) Master The table space of the OEM to view, modify and other management operations.

(3) Master the establishment, modification, viewing and deletion of data files.

(4) Master control files, log file creation, modification, view, delete operations.

Requirements

(1) Record table space operation during the basic operating interface.

(2) Record the interface that uses Enterprise Manager to manage data files, log files and control files.

(3) record the operation of using Sql*plus to manage tablespaces and various types of files.

Steps:

(1) Add table space.

① using Enterprise Manager to create a case tablespace "Ygbx_tbs" toAdd a data file "ygbx001.dbf"to the Tablespace "ygbx_tbs" , the path is "f:\" and the size is ten MB. If the data file already exists, it is overwritten, and the data file has an auto-extended attribute, with each increment of up to KBand a maximum of .

1. Enter the address of the login OEM in the address bar of the browser :https://localhost:1158/em

Enter your user name and password:

2. Click "Server",

3. Click on the Storage in the tablespaces

4. Click "Create" on the right side of the pageto create a tablespace Ygbx_tbs

and add the data file "ygbx001.dbf", the path is "F:\oracle\" size is ten MB, if the data file already exists, it is overwritten , the data file has an auto-extended attribute, with each increment of up to KBand a maximum value of .

5. Successful creation:

② Create a table space using the command line method "Ygbx1_tbs", for table space"Ygbx1_tbsthe Add section1a data file "ygbx003.dbf", Path is"f:\oracle\", the size isTen MB. If the data file already exists, it is overwritten, the data file has an auto-extended attribute, and each increment isKB, the maximum value isMB. Section2data file is "ygbx004.dbf", Path is"f:\oracle\", the size is5 MB, there are no auto-extended properties.

Sql>create tablespace ygbx1_tbs datafile ' F:\oracle\ygbx003.dbf '

Size 10m reuse autoextend on next 128k maxsize 100m;

③ use Enterprise Manager to create a case tablespace "Ygbx2_tbs" toAdd a data fileto the tablespace "ygbx2_tbs"ygbx005.dbf ", Path is" f:\ ", the size is Ten MB . If the data file already exists, it is overwritten, and the data file has an auto-extended attribute, with each increment of up to KBand a maximum of .

Note: Tablespace is unique throughout the database.

Step as (1), for the table space built

(2) modify the table space.

① Use Enterprise Manager to modify the tablespace "ygbx_tbs" You just created,adding data Filesto the tablespace "ygbx_tbs"ygbx002.dbf ", the path is"f:\oracle\"and the size is ten MB. If the data file already exists, it is overwritten, the data file has an auto-extended attribute, each increment is a maximum of KB, andit is modified to take offline status.

. Find to table space Ygbx_tbs then in ' Actions ' select add datafiles click 'go' button to add data file

2) Note that if you want to increase the data file, the tablespace must be operating in an online state

To increase the success of the data file page,

Next, look at the table space that corresponds to the information shown in:

② use the command line to modify the Tablespace "Ygbx1_tbs" toincrease the data file "ygbx006.dbf" for thetablespace "ygbx1_tbs", The path is "f:\oracle\" and modifies it to an offline state.

Sql>alter tablespace ygbx1_tbs Add datafile ' F:\oracle\ygbx006.dbf ';

Sql>alter database datafile ' F:\oracle\ygbx006.dbf ' offline;

(3) Delete table space.

① uses the command line to delete the tablespace "Ygbx1_tbs" while deleting the contained data file.

Take the table space offline first, and then delete the tablespace and data files

Sql>alter tablespace Ygbx1_tbs offline;

Sql>drop tablespace Ygbx1_tbs including contents and datafiles;

② uses Enterprise Manager to delete the tablespace "Ygbx2_tbs" while deleting the included data files.

Select ' Ygbx2_tbs ' and click 'delete' to delete

(4) Data file operation.

① Use Enterprise Manager to modify the size of the data file ygbx001.dbf to 20M.

First enter the datafiles interface , and then modify the data file ' ygbx001.dbf ' of the size 20m, Point in the data file that you want to modify, and then edit

To modify the size of the data file to 20m, then click ' Apply ' to take effect ,

② uses Sql*plus to modify the maximum value of the YGBX003.DBF data file to infinity .

Sql>alter tablespace datafile ' F:\oracle\ygbx003.dbf ' maxsize unlimited;

Sql>alter tablespace ygbx_tbs drop datafile ' F:\orace\ygbx002.dbf '

③ uses Sql*plus to delete ygbx002.dbf data files.

(5) View log files, control files.

① uses Enterprise Manager to view and manage the control files and log files of the database.

After logging in to the system User, click on Server and click Storage under controlfile Enter the following interface

Backup control files at the same time:

Click the 'OK' button to backup successfully.

Experimental Summary:

1.  --> --> -->

2. Database: A data base can be divided into multiple logical storage units called table spaces. tablespace: can belong to only one database , including one or more files. SEGMENT: exists in a table space and contains one or more extents. EXTENT: consists of contiguous blocks of data , which means that each zone can only exist in one data file. BLOCK: is the smallest I/O unit in the database , db_block_size

3.Benefits of managing table spaces locally:A. no need to perform recursive space management operations . B. do not log free space in the data dictionary table to reduce contention . c. automatic tracking of adjacent free space without the need to merge free space . D. The system automatically determines the size of the local admin area . E. changes to the location map do not generate restore information , and the country does not update the tables in the data dictionary ( except for special cases such as tablespace quota information )

4. Delete table spaces without objects : drop tablespace table space name ;

Delete a table space with objects, but do not delete the physical files : Drop tablespace table space name including contents;

Delete the table space with objects and delete the physical files : Drop tablespace table space name including contents and datafiles cascadeconstraints;

5. You can use the following PL/SQL program to convert any dictionary-managed tablespace into local administration:
Executedbms_space_admin.tablespace_migrage_to_local ('tablespacename');

6. Logically, data is stored in segments (usually tables), and physically, data is stored in data files (datafile). A tablespace (tablespace) entity is an image of both, a table space may contain multiple segments, and consists of multiple data files.

Storage and file Management