# OCA: Oracle Database 11g Administrator Certified Associate- P15

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
51
lượt xem
6

## OCA: Oracle Database 11g Administrator Certified Associate- P15

Mô tả tài liệu

Tham khảo tài liệu 'oca: oracle database 11g administrator certified associate- p15', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0)

Lưu

## Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate- P15

1. Managing Tablespaces 531 MANAGEMENT AUTO clause. Oracle strongly recommends AUTOMATIC segment space manage- ment for permanent locally managed tablespaces, and the default behavior of Oracle 11g is AUTO. Here is a statement that creates a tablespace with manual segment space management: CREATE TABLESPACE hr_index DATAFILE ‘/u02/oradata/11GR11/hr_index01.dbf’ SIZE 2G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL; When automatic segment space management is specified, bitmaps are used instead of free lists to identify which data blocks are available for inserts. The parameters PCTFREE and PCTUSED are ignored for segments in tablespaces with automatic segment space management. Automatic segment space management is available only on tablespaces configured for local extent management; it is not available for temporary or system tablespaces. Automatic seg- ment space management performs better and reduces your maintenance tasks, making it the preferred technique. To specify automatic segment space management, use the SEGMENT SPACE MANAGEMENT AUTO clause of the CREATE TABLESPACE statement like this or do not include the SEGMENT SPACE MANAGEMENT clause (it is the default): CREATE TABLESPACE hr_index DATAFILE ‘/u02/oradata/11GR11/hr_index01.dbf’ SIZE 2G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; When automatic segment space management is used, Oracle ignores the storage param- eters PCTUSED, FREELISTS, and FREELIST GROUPS when creating objects. Although the name segment space management sounds similar to extent management, it is quite different and can be more accurately regarded as block space management. Choosing Other Tablespace Options Several options are available to use when creating a tablespace. You learned to create BIGFILE or SMALLFILE tablespaces and use the EXTENT MANAGEMENT and SEGMENT SPACE MANAGEMENT options in the previous sections. In this section, you will learn the other options available while creating a tablespace: NÛ Specifying nondefault block size NÛ Specifying default storage characteristics NÛ Specifying logging and flashback clauses NÛ Creating offline tablespaces
2. 532 Chapter 10 N Allocating Database Storage and Creating Schema Objects The following example shows the optional clauses you can use while creating a dictionary- managed tablespace: CREATE TABLESPACE APPL_DATA DATAFILE ‘/disk3/oradata/DB01/appl_data01.dbf’ SIZE 100M DEFAULT STORAGE ( INITIAL 256K NEXT 256K MINEXTENTS 2 PCTINCREASE 0 MAXEXTENTS 4096) BLOCKSIZE 16K MINIMUM EXTENT 256K LOGGING ONLINE FORCE LOGGING FLASHBACK ON EXTENT MANAGEMENT DICTIONARY SEGMENT SPACE MANAGEMENT MANUAL; The following example shows the optional clauses you can use while creating a locally managed tablespace: CREATE TABLESPACE APPL_DATA DATAFILE ‘/disk3/oradata/DB01/appl_data01.dbf’ SIZE 100M DEFAULT STORAGE COMPRESS BLOCKSIZE 16K LOGGING ONLINE FORCE LOGGING FLASHBACK ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; Though Oracle manages the tablespace characteristics very efficiently with its default values, you can specify several clauses to a finer level of control. The clauses in the CREATE TABLESPACE command can specify the following: DEFAULT STORAGE clause The DEFAULT STORAGE clause specifies the default storage parameters for new objects that are created in the tablespace. If an explicit storage clause is specified when creating an object, the tablespace defaults are not used for the specified storage parameters. The storage parameters are specified within parentheses; no parameter is mandatory, but if
3. Managing Tablespaces 533 you specify the DEFAULT STORAGE clause, you must specify at least one parameter inside the parentheses. The storage parameters are valid only for dictionary-managed tablespaces; for locally managed tablespaces, you can specify only the COMPRESS option. I will discuss the storage parameters later in the chapter in the section “Creating a Table.” BLOCKSIZE clause Oracle allows a tablespace to have a different block size than the default standard database block size. The database block size is specified when you create the data- base using the initialization parameter DB_BLOCK_SIZE. This is the block size used for the SYSTEM tablespace and is known as the standard block size. The valid sizes of the nonstan- dard block size are 2KB, 4KB, 8KB, 16KB, and 32KB. If you do not specify a block size for the tablespace, the database block size is assumed. Multiple block sizes in the database are beneficial for large databases with OLTP and Decision Support System (DSS) data stored together and for storing large tables. The restrictions on specifying nonstandard block sizes along with the tablespace creation are discussed in the section “Using Nonstandard Block Sizes.” MINIMUM EXTENT clause The MINIMUM EXTENT clause specifies that the extent sizes should be a multiple of the size specified. You can use this clause to control fragmentation in the tablespace by allocating extents of at least the size specified; this clause is always a mul- tiple of the size specified. In the CREATE TABLESPACE example, all the extents allocated in the tablespace would be a multiple of 256KB. The INITIAL and NEXT extent sizes specified should be a multiple of MINIMUM EXTENT. This clause is valid only for dictionary-managed tablespaces. LOGGING/NOLOGGING clause The LOGGING/NOLOGGING clause specifies that the DDL opera- tions and direct-load INSERT should be recorded in the redo log files. This is the default, and the clause can be omitted. When you specify NOLOGGING, data is modified with minimal logging, and hence the commands complete faster. Since the changes are not recorded in the redo log files, you need to apply the commands again in the case of a media recovery. You can specify LOGGING or NOLOGGING in the individual object creation statement, and it over- rides the tablespace default. FORCE LOGGING clause You must specify this clause to log all changes irrespective of the LOGGING mode for individual objects in the tablespace. You can specify the NOLOGGING clause and FORCE LOGGING clause together when creating a tablespace. If you do so, the objects will be created in NOLOGGING mode and will be overridden by the FORCE LOGGING mode. When you take the tablespace out of the FORCE LOGGING mode, the NOLOGGING attri- bute for objects goes into effect. ONLINE/OFFLINE clause This clause specifies that the tablespace should be made online or available as soon as it is created. This is the default, and hence the clause can be omitted. If you do not want the tablespace to be available, you can specify OFFLINE. FLASHBACK ON/OFF clause FLASHBACK ON puts the tablespace in the flashback mode and is the default. The OFF option turns flashback off, and hence Oracle will not save any flash- back data. I will discuss flashback operations in Chapter 15, “Implementing Database Backups.”
4. 534 Chapter 10 N Allocating Database Storage and Creating Schema Objects The clauses related to encrypting the tablespace are not discussed here because they are beyond the scope for this book. Using Nonstandard Block Sizes The block size used while creating the database is specified in the initialization parameter using the DB_BLOCK_SIZE parameter. This is known as the standard block size for the data- base. You must choose a block size that suits most of your tables as the standard block size. In most databases, this is the only block size you will ever need. Oracle gives you the option of having multiple block sizes, which is especially useful when you’re transporting tablespaces from another database with a different block size. The DB_CACHE_SIZE parameter defines the buffer cache size that is associated with the standard block size. To create tablespaces with nonstandard block size, you must set the appropriate initialization parameter to define a buffer cache size for the block size. The initialization parameter is DB_nK_CACHE_SIZE, where n is the nonstandard block size. n can have values 2, 4, 8, 16, or 32 but cannot have the size of the standard block size. For example, if your standard block size is 8KB, you cannot set the parameter DB_8K_CACHE_SIZE. If you need to create a tablespace that uses a different block size, say 16KB, you must set the DB_16K_CACHE_SIZE parameter. By default, the value for DB_nK_CACHE_SIZE parameters is 0MB. The temporary tablespaces created should have the standard block size. The DB_nK_CACHE_SIZE parameter is dynamic; you can alter its value using the ALTER SYSTEM statement. Creating Temporary Tablespaces Oracle can manage space for sort operations more efficiently by using temporary tablespaces. By exclusively designating a tablespace for temporary segments, Oracle eliminates the allo- cation and deallocation of temporary segments in a permanent tablespace. A temporary tablespace can be used only for sort segments. A temporary tablespace is used for temporary segments, which are created, managed, and dropped by the database as needed. These tem- porary segments are most commonly generated during sorting operations such as ORDER BY, GROUP BY, and CREATE INDEX. They are also generated during other operations such as hash joins or inserts into temporary tables. You create a temporary tablespace at database creation time with the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement or after the database is created with the CREATE TEMPORARY TABLESPACE statement, like this: CREATE TEMPORARY TABLESPACE temp TEMPFILE ‘/u01/oradata/11GR1/temp01.dbf’ SIZE 1G;
5. Managing Tablespaces 535 Notice that the keyword TEMPFILE is used instead of DATAFILE. Temp files are available only with temporary tablespaces, they never need to be backed up, and they do not log data changes in the redo logs. The EXTENT MANAGEMENT LOCAL clause is optional and can be omitted; you can provide it to improve readability. If you do not specify the extent size by using the UNIFORM SIZE clause, the default size used will be 1MB. Although it is always good practice to create a separate temporary tablespace, it is required when the SYSTEM tablespace is locally managed. Temporary tablespaces are created using temp files instead of data files. Temp files are allocated slightly differently than data files. Although data files are completely allocated and initialized at creation time, temp files are not always guaranteed to allocate the disk space specified. This means that on some Unix systems a temp file will not actually allocate disk space until a sorting operation requires it. Although this delayed allocation approach allows rapid file creation, it can cause problems down the road if you have not reserved the space that may be needed at runtime. Each user is assigned a temporary tablespace when the user is created. By default, the default tablespace (where the user creates objects) and the temporary tablespace (where the user’s sort operations are performed) are both the SYSTEM tablespace. No user should have SYSTEM as their default or temporary tablespace. This will unnecessarily increase fragmen- tation in the SYSTEM tablespace. When creating a database, you can also create a temporary tablespace using the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement. If the default temporary tablespace is defined in the database, all new users will have that tablespace assigned as the temporary tablespace by default if you do not specify another tablespace for the users’ tem- porary tablespace. You can also designate a data tablespace for application tables during database creation using the DEFAULT TABLESPACE clause. If there are multiple temporary tablespaces in a database and if you want to utilize the space in multiple temporary tablespaces to a user’s sort operation, you can use the tempo- rary tablespace groups. When creating the temporary tablespace, use the TABLESPACE GROUP clause as in the following example: CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE ‘/u01/oradata/11GR1/temp01a.dbf’ size 200M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M TABLESPACE GROUP ALL_TEMPS; In this example, the tablespace is made part of the ALL_TEMPS temporary tablespace group. Tablespace groups are applicable only to temporary tablespaces. If the group does not exist, Oracle creates the group and adds the tablespace to the group. When creating a temporary tablespace, you can use only the EXTENT MANAGEMENT and TABLESPACE GROUP clauses along with TEMPFILE clause. All other options are invalid for temporary tablespaces.
11. Managing Tablespaces 541 If you forget to take a tablespace out of backup mode, the next time you bounce your database, it will see that the checkpoint number in the control file is later than the one in the data file headers and report that media recovery is required. Obtaining Tablespace Information DBAs often need to find the space used and available in a tablespace as well as query the tablespace characteristics. The data dictionary is the place to go for obtaining tablespace information. You can use the command-line utility SQL*Plus to query the information from data dictionary tables, or you can use Enterprise Manager Grid Control. We will review both in this section. Obtaining Tablespace Information Using SQL*Plus Many data dictionary views can provide information about tablespaces in a database, such as the following: NÛ DBA_TABLESPACES NÛ DBA_DATA_FILES NÛ DBA_TEMP_FILES NÛ V$TABLESPACE The DBA_TABLESPACES view has one row for each tablespace in the database and provides the following information: NÛ The tablespace block size NÛ The tablespace status: online, offline, or read-only NÛ The contents of the tablespace: undo, temporary, or permanent NÛ Whether it uses dictionary-managed or locally managed extents NÛ Whether the segment space management is automatic or manual NÛ Whether it is a bigfile or smallfile tablespace To get a listing of all the tablespaces in the database, their status, contents, extent man- agement policy, and segment management policy, run the following query: SELECT tablespace_name, status,contents ,extent_management extents ,segment_space_management free_space FROM dba_tablespaces TABLESPACE_NAME STATUS CONTENTS EXTENTS FREE_SPACE ------------------ --------- --------- --------- ---------- SYSTEM ONLINE PERMANENT LOCAL MANUAL UNDOTBS1 ONLINE UNDO LOCAL MANUAL 12. 542 Chapter 10 N Allocating Database Storage and Creating Schema Objects SYSAUX ONLINE PERMANENT LOCAL AUTO TEMP ONLINE TEMPORARY LOCAL MANUAL USERS ONLINE PERMANENT LOCAL AUTO EXAMPLE ONLINE PERMANENT LOCAL AUTO DATA ONLINE PERMANENT LOCAL AUTO INDX ONLINE PERMANENT LOCAL AUTO The V$TABLESPACE view also has one row per tablespace, but it includes some informa- tion other than DBA_TABLESPACES, such as whether the tablespace participates in database flashback operations: SELECT name, bigfile, flashback_on FROM v$tablespace; NAME BIGFILE FLASHBACK_ON ---------- -------- ------------ SYSTEM NO YES UNDOTBS1 NO YES SYSAUX NO YES USERS NO YES TEMP NO YES EXAMPLE NO YES DATA NO YES INDX NO YES See Chapter 15 for more information on flashback operations. The DBA_DATA_FILES and DBA_TEMP_FILES views contain information on data files and temp files, respectively. This information includes the tablespace name, filename, file size, and autoextend settings. SELECT tablespace_name, file_name, bytes/1024 kbytes FROM dba_data_files UNION ALL SELECT tablespace_name, file_name, bytes/1024 kbytes FROM dba_temp_files; TABLESPACE FILE_NAME KBYTES ---------- ---------------------------------------- ------- USERS C:\ORACLE\ORADATA\ORA11\USERS01.DBF 102400 SYSAUX C:\ORACLE\ORADATA\ORA11\SYSAUX01.DBF 256000 13. Managing Tablespaces 543 UNDOTBS1 C:\ORACLE\ORADATA\ORA11\UNDOTBS01.DBF 51200 SYSTEM C:\ORACLE\ORADATA\ORA11\SYSTEM01.DBF 460800 EXAMPLE C:\ORACLE\ORADATA\ORA11\EXAMPLE01.DBF 153600 INDX C:\ORACLE\ORADATA\ORA11\INDX01.DBF 102400 TEMP C:\ORACLE\ORADATA\ORA11\TEMP01.DBF 51200 In addition to in the data dictionary, you can obtain tablespace information from several sources. Some of these sources are the DDL and the Enterprise Manager. generating DDl for a tablespace Another way to quickly identify the attributes of a tablespace is to ask the database to generate DDL to re-create the tablespace. The CREATE TABLESPACE statement that results contains the attributes for the tablespace. Use the PL/SQL package DBMS_METADATA to generate DDL for your database objects. For example, to generate the DDL for the USERS tablespace, execute this: SELECT DBMS_METADATA.GET_DDL(‘TABLESPACE’,’USERS’) FROM dual; The output from this statement is a CREATE TABLESPACE statement that contains all the attributes for the USERS tablespace: CREATE TABLESPACE “USERS” DATAFILE ‘/u01/app/oracle/oradata/11GR11/users01.dbf’ SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; Obtaining Tablespace Information Using the EM Database Control Instead of querying the data dictionary views with a command-line tool such as SQL*Plus, you can use the interactive GUI tool EM Database Control to monitor and manage data- base structures, including tablespaces. The EM Database Control is an alternative to a command-line interface. To use the Database Control, follow these steps: 1. Point your browser to the Enterprise Manager URL for your database (similar to https://hostname:5500/em/console). 2. Log in to EM, and navigate to the Server tab of the main screen, which is shown in Figure 10.2. 3. Click the Tablespaces link under the heading Storage to display a list of tablespaces like that shown in Figure 10.3. 14. 544 Chapter 10 N Allocating Database Storage and Creating Schema Objects F i g u r e 10 . 2 The Enterprise Manager Server tab F i g u r e 10 . 3 The Enterprise Manager Tablespaces screen 4. Click the radio button next to the tablespace you want to work with, and then click the Edit button. You can navigate to the tablespace General, Storage, and Thresholds edit screens, as shown in Figure 10.4. 15. Managing Tablespaces 545 F i g u r e 10 . 4 The Enterprise Manager tablespace editor You use the screens and options in the EM Database Control to manipulate and change your tablespaces with many of the same options that the command-line interface supports. For example, to increase the size of the data file in the HR_DATA tablespace, click the Edit button next to the data file. The EM Database Control displays the tablespace edit screen, as shown in Figure 10.5. F i g u r e 10 . 5 Editing the data file size 16. 546 Chapter 10 N Allocating Database Storage and Creating Schema Objects Edit the File Size field, increasing it to 100MB. The change will be applied when you click Continue. Managing Data Files Data files (or temporary files) are made when you create a tablespace or when you alter a tablespace to add files. If you are not using the OMF feature, you will need to manage data files yourself. The database will create or reuse one or more data files in the sizes and locations that you specify whenever you create a tablespace. A data file belongs to only one tablespace and only one database at a time. Temp files are a special variety of data file that are used in temporary tablespaces. When the database creates or reuses a data file, the operating-system file is allocated and initialized—filled with a regular pattern of mostly binary zeros. This initialization will not occur with temp files. Performing Operations on Data Files Operations that you may need to perform on data files include the following: NÛ Resizing them NÛ Taking them offline or online NÛ Moving (renaming) them Sizing Files You can specify that the data file (or temporary file) will grow automatically whenever space is needed in the tablespace. This is accomplished by specifying the AUTOEXTEND clause for the file. This functionality enables you to have fewer data files per tablespace and can simplify the administration of data files. The AUTOEXTEND clause can be ON or OFF; you can also spec- ify file size increments. You can set a maximum limit for the file size; by default, the file size limit is UNLIMITED. You can specify the AUTOEXTEND clause for files when you run the CREATE DATABASE, CREATE TABLESPACE, ALTER TABLESPACE, and ALTER DATAFILE commands. For example: CREATE TABLESPACE APPL_DATA DATAFILE ‘/disk2/oradata/DB01/appl_data01.dbf‘ SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M; The AUTOEXTEND ON clause specifies that the automatic file-resize feature should be enabled for the specified file; NEXT specifies the size by which the file should be incremented, and MAXSIZE specifies the maximum size for the file. When Oracle tries to allocate an extent in the tablespace, it looks for a free extent. If a large enough free extent cannot be located 17. Managing Data Files 547 (even after coalescing), Oracle increases the data file size by 100MB and tries to allocate the new extent. The following statement disables the automatic file-extension feature: ALTER DATABASE DATAFILE ‘/disk2/oradata/DB01/appl_data01.dbf‘ AUTOEXTEND OFF; If the file already exists in the database, and you want to enable the autoextension fea- ture, use the ALTER DATABASE command. For example, you can use the following statement: ALTER DATABASE DATAFILE ‘/disk2/oradata/DB01/appl_data01.dbf‘ AUTOEXTEND ON NEXT 100M MAXSIZE 2000M; You can increase or decrease the size of a data file or temporary file (thus increasing or decreasing the size of the tablespace) by using the RESIZE clause of the ALTER DATABASE DATAFILE command. For example, to redefine the size of a file, use the following statement: ALTER DATABASE DATAFILE ‘/disk2/oradata/DB01/appl_data01.dbf‘ RESIZE 1500M; When decreasing the file size, Oracle returns an error if it finds data beyond the new file size. You cannot reduce the file size below the high-water mark in the file. Reducing the file size helps reclaim unused space. Making Files Online and Offline Sometimes you may have to make data files unavailable to the database if there is a file cor- ruption. You can use the ONLINE and OFFLINE clauses of the ALTER DATABASE statement to take a data file online or offline. You can specify the filename or specify the unique identi- fier number that represents the data file. This identifier can be found in the FILE# column of V$DATAFILE or the FILE_ID column of the DBA_DATA_FILES view. To take a data file offline, use the OFFLINE clause. If the database is in NOARCHIVELOG mode, then you must specify the FOR DROP clause along with the OFFLINE clause. The data file will be taken offline and marked with status OFFLINE. You can remove the data file using OS commands, if you want to get rid of the data file. If the database is in ARCHIVELOG mode, you don’t need to specify the FOR DROP clause when taking a data file offline. When you’re ready to bring the data file online, Oracle performs media recovery on the data file to make it consistent with the database. Also, the FOR DROP clause is ignored if the database is in ARCHIVELOG mode. Here is an example of taking a data file offline: ALTER DATABASE DATAFILE ‘/u01/oradata/11gr1/tools02.dbf’ OFFLINE; The following statement brings the data file online: ALTER DATABASE DATAFILE ‘/u01/oradata/11gr1/tools02.dbf’ ONLINE;