Storage and file management

Storage and file management
Storage and file management

Purpose

(1) master the creation and management of tablespaces.

(2) master the management operations such as viewing and modifying the tablespace under the OEM.

(3) master the creation, modification, viewing, and deletion of data files.

(4) master the creation, modification, viewing and deletion of control files and log files.

Requirements

(1) Basic operation interface during table space operations.

(2) record the interface for managing data files, log files, and control files using the Enterprise Manager.

(3) record the use of SQL * Plus statements to manage tablespaces and various files.

Steps:

(1) Add a tablespace.

① Use the Enterprise Manager to create the case tablespace "ygbx_tbs" and add a data file "ygbx001.dbf" to the tablespace "ygbx_tbs". The path is "f: \" and the size is 10 MB. If the data file already exists, it will be overwritten. The data file has the automatic extended attribute. Each increment is 64 KB and the maximum value is 50 MB.

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

Enter the user name and password:

2. Click "server ",

3. Click tablespaces in Storage.

4. Click "create" on the right of the page to create the tablespace ygbx_tbs

Add the data file "ygbx001.dbf" and the path "F: \ oracle \" is 10 MB. If the data file already exists, it will be overwritten. The data file has the automatic extension attribute, each increment is 64 KB and the maximum value is 50 MB.

5. Created successfully ,:

② Create a tablespace "ygbx1_tbs" using the command line method, and add 1st data files "ygbx003.dbf" to the tablespace "ygbx1_tbs". The path is "f: \ oracle \", the size is 10 MB. If the data file already exists, it will be overwritten. The data file has the auto-scaling attribute, with each increment of 128 KB and the maximum value of 100 MB. The 2nd data files are "ygbx004.dbf", the path is "f: \ oracle \", the size is 5 MB, and there is no auto-scaling attribute.

SQL> create tablespace ygbx1_tbs datafile 'f: \ oracle \ ygbx003.dbf'

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

③ Use the Enterprise Manager to create the case tablespace "ygbx2_tbs" and add a data file "ygbx005.dbf" to the tablespace "ygbx2_tbs". The path is "f: \" and the size is 10 MB. If the data file already exists, it will be overwritten. The data file has the automatic extended attribute. Each increment is 64 KB and the maximum value is 50 MB.

Note: The tablespace is unique throughout the database.

Steps are as follows: (1 ).

(2) modify the tablespace.

① Use the Enterprise Manager to modify the created tablespace "ygbx_tbs" and add the data file "ygbx002.dbf" to the tablespace "ygbx_tbs". The path is "f: \ oracle \" and the size is 10 MB. If the data file already exists, it will be overwritten. The data file has the automatic extended attribute. Each increment is 64 KB, the maximum value is 50 MB, and it is changed to offline status.

. Find the tablespace ygbx_tbs and select add datafiles in 'actions' and click 'Go' to add the data file.

2) Note that to add data files, the tablespace must be operated online.

For example,

Next, view the information corresponding to the tablespace, as shown in:

② Use the command line method to modify the tablespace "ygbx1_tbs" and add the data file "ygbx006.dbf" to the tablespace "ygbx1_tbs". The path is "f: \ oracle \", and change it to offline status.

SQL> alter tablespace ygbx1_tbs add datafile 'f: \ oracle \ ygbx006.dbf ';

SQL> alter database datafile 'f: \ oracle \ ygbx006.dbf' offline;

(3) Delete the tablespace.

① Use the command line method to delete the tablespace "ygbx1_tbs" and delete the contained data files.

First, take the tablespace offline, and then delete the tablespace and data files.

SQL> alter tablespace ygbx1_tbs offline;

SQL> drop tablespace ygbx1_tbs including contents and datafiles;

② Use the Enterprise Manager method to delete the tablespace "ygbx2_tbs" and also delete the contained data files.

Select 'ygbx2 _ tbs 'and click 'delete' to delete it.

(4) data file operations.

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

First go to The datafiles interface, and then modify the file size of the data file 'ygbx001. dbf' to 20 m. Click the data file to be modified, and then edit it.

To change the data file size to 20 mb, click 'apply 'to take effect,

② Use SQL * Plus to modify the maximum value of the ygbx003.dbf data file to be infinitely large.

SQL> alter tablespace datafile 'f: \ oracle \ ygbx003.dbf' maxsize unlimited;

SQL> alter tablespace ygbx_tbs drop datafile 'f: \ e \ ygbx002.dbf'

③ Use SQL * Plus to delete the ygbx002.dbf data file.

(5) view log files and control files.

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

After logging in as a system user, click server and then click controlfile under storage to go to the following interface:

Back up the control file at the same time ,:

Click 'OK' to back up.

Experiment summary:

1. The tablespace size is the sum of the data files in the tablespace. A tablespace contains at least one data file. A data file can only belong to one tablespace. Tablespace logical storage objects: Permanent segments --> such as tables and indexes. Temporary section --> for example, temporary table data and sorting section. Rollback segment --> used for transaction rollback or flash back memory revocation data.

2. DATABASE: a DATABASE can be divided into multiple logical storage units called tablespaces. TABLESPACE: Only one database, including one or more files. SEGMENT: it exists in a tablespace and contains one or more partitions. EXTENT: composed of adjacent data blocks, which means that each partition can only exist in one data file. BLOCK: The smallest I/O unit in the database, db_block_size

3. Advantages of local tablespace Management: a. Recursive space management is not required. B. do not record the free space in the data dictionary table, which can reduce contention. C. It can automatically track adjacent idle space without merging idle space. D. the system automatically determines the size of the local management area. E. do not generate Restoration Information for changes to the location map. For such changes, tables in the data dictionary are not updated (except for special situations such as the tablespace quota information)

4. Delete tablespaces without objects: drop tablespace name;

Delete tablespaces with objects, but do not delete physical files: drop tablespace name including contents;

Delete tablespaces with objects and delete physical files: drop tablespace name including contents and datafiles cascadeconstraints;

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

6. Logically, data is stored in segments (usually tables); physically, data is stored in datafiles. A table space entity is an image of the two. A table space may contain multiple segments and consists of multiple data files.