Oracle fourth job

 

 

Fourth
Purpose:
Master the two different methods for moving data files and understand the application scenarios of these two methods. Master how to create a tablespace and understand how to use the OMF mechanism to facilitate DBA.
Measure the test taker's knowledge about how to create non-standard block-size tablespaces and understand the application scenarios of different block-size tablespaces.
Master how to create restored and temporary tablespaces, and understand how to use these two types of tablespaces.
Lab content:

1. Create a data folder under the "target folder" (later called a data folder ).

2. Move all data files (including data files contained in temporary tablespace) to the data folder ".
Conn scott/tiger @ shared

Startup mount
Alter database noarchivelog;
Shutdown immediate
Manually load the data files (c: \ oracle \ product \ 10.2.0 \ oradata \ DB947026 \ SYSAUX01.DBF and
C: \ oracle \ product \ 10.2.0 \ oradata \ DB947026 \ UNDOTBS01.DBF and
C: \ oracle \ product \ 10.2.0 \ oradata \ DB947026 \ SYSTEM01.DBF)
Cut to the target file (c: \ DB947026 \ data), and then execute
STARTUP MOUNT
Alter database rename file 'C: \ oracle \ product \ 10.2.0 \ oradata \ DB947026 \ sysaux01.dbf' TO 'C: \ DB947026 \ data \ sysaux01.dbf ';
Alter database rename file 'C: \ oracle \ product \ 10.2.0 \ oradata \ DB947026 \ system01.dbf' to 'C: \ DB947026 \ data \ system01.dbf ';
Alter database rename file 'C: \ oracle \ product \ 10.2.0 \ oradata \ DB947026 \ temp01.dbf' to 'C: \ DB947026 \ data \ temp01.dbf ';
Alter database rename file 'C: \ oracle \ product \ 10.2.0 \ oradata \ DB947026 \ undo01.dbf' to 'C: \ DB947026 \ data \ undo01.dbf ';
Alter database open;
Run
Select * from dba_tablespace;
Select * from dba_data_files;
View

3. Adjust the size of the data file contained in the SYSTEM tablespace to 600 mb, which is automatically extended. Each time 50 MB is used, the maximum size is 2048 MB.
Alter database datafile 'C: \ DB947026 \ data \ system01.dbf' resize 600 m;
Alter database datafile 'C: \ DB947026 \ data \ system01.dbf' autoextend on next 50 M maxsize 2048 M;

4. Adjust the size of the data file contained in the SYSAUX tablespace to 300 MB, which is automatically extended. The maximum size is 1024 MB at each time.
Alter database datafile 'C: \ DB947026 \ data \ sysaux01.dbf' resize 300 m;
Alter database datafile 'C: \ DB947026 \ data \ sysaux01.dbf' autoextend on next 50 M maxsize 1024 M;

5. Create Table space TEST2, including the data file t947026.dbf (?????? Is the last 6 digits of the student ID ),
The size is 80 Mb, which is automatically scalable. Each time it is 8 MB and the maximum size is 800 mb, it is stored in the "data folder ".

Create tablespace TEST2 datafile 'C: \ DB947026 \ data \ t947026.dbf' size 80 M autoextend on next 8 M maxsize 800 M;

6. Use the OMF mechanism to add data files to TEST2. The data files are stored in the "data folder" and the size is 80 Mb, which is not automatically extended.

AlTer system set db_create_file_dest = 'C: \ DB947026 \ data ';
Alter tablespace TEST2 add datafile size 80 M autoextend off;

7. Use the OMF mechanism to create the tablespace example2, and store the data files in the "data folder". The size is 80 Mb, which is automatically extended. Each time it is 8 Mb, the maximum size is 800 mb.

Alter system set db_create_file_dest = 'C: \ DB947026 \ data ';
Create tablespace example2 datafile size 80 M autoextend on next 8 M maxsize 800 M;
8. Use the OMF mechanism to create the tablespace users2. The data files are stored in the "data folder" and the size is 80 Mb, which is not automatically extended.

Create tablespace USERS2 datafile size 80 M autoextend off;
9. Create a tablespace tb2_8k with a block size of 8 K, including the data file TB8 ????. Dbf (???? For the last four student IDs ),
This data file is initially 40 MB and is automatically extended. It can be 4 MB at a time and a maximum of 400 mb is stored in the "data folder ".

Show parameter db_block_size;
(4 K) // view the current database size. In this system, the database size is 4 K
Show parameter db_8K_cache_size;
View the default system size (0 is not enabled)
Alter system set db_8k_cache_size = 8 M scope = both;
Create tablespace tb2_8K datafile 'C: \ DB947026 \ DATA \ tb87011.dbf' size 40 M autoextend on next 4 M maxsize 400 M blocksize 8 K;

10. Create a tablespace tb2_2k with a block size of 2 K. It contains the data file TB7040.dbf (???? For the last four student IDs ),
This data file is initially 10 MB and automatically expanded. Each time it is 1 MB, the maximum is 100 MB, and is stored in the "data folder ".

Show parameter db_2K_cache_size;
Alter system set db_2k_cache_size = 2 M scope = both;
Shutdown immediate
Startup
Create tablespace tb2_2K datafile 'C: \ DB947026 \ DATA \ tb27011.dbf' size 10 M autoextend oN Next 1 M maxsize 100 M blocksize 2 K;

11. Create the UNDO tablespace tb2_undo and use the OMF mechanism. This data file is initially 80 mb and automatically expanded. Each time it is 8 Mb, it is not limited at most and is stored in the "data folder ".

Alter system set db_create_file_dest = 'C: \ DB947026 \ data ';
Create undo tablespace tb2_undo datafile size 80 M autoextend on next 8 M maxsize unlimited;
12. Create a temporary tablespace tb2_temp and use the OMF mechanism. This data file is initially 80 mb and automatically expanded. Each time it is 8 Mb, it is not limited at most and is stored in the "data folder ".

Create temporary tablespace tb2_temp tempfile size 80 M autoextend on next 8 M maxsize unlimited;
13. Set the current UNDO tablespace to tb2_undo and the default temporary tablespace to tb2_temp.

Alter system set undo_tablespace = tb2_undo scope = both;
Alter database default temporary tablespace tb2_temp;
14. Configure the database to support shared server connections. Set the number of currently dispatched processes to 3 and the maximum number of dispatched processes to 9,
The number of shared server processes is 2, and the maximum number of shared server processes is 9.

Alter system set dispatchers = "(PRO = TCP) (DIS = 3 )";
// Alter system set dispatchers = '(protocol = tcp) (dispatchers = 3)' scope = spfile;

Alter system set max_dispatchers = 9;

Alter system set shared_servers = 2;

Alter system set max_shared_servers = 9;

Http://www.cnblogs.com/tracy/archive/2011/08/04/2127738.html

Http://blog.csdn.net/robinson_0612/article/details/6030190

(SERVER = SHARED)
(SERVER = DEDICATED)