Oracle442 Application Instances ---------- overview file, Overview Design Manual instance

Oracle442 Application Instances ---------- overview file, Overview Design Manual instance

------------------------ Brief Description :---------------------------------
In order to rationally allocate and use system resources, the Oracle system puts forward the concept of profiles. A summary file is a configuration file that describes how to use system resources (mainly CPU resources. Assign the profile to a database user. When the user connects to and accesses the database server, the system allocates resources to the user according to the profile. In some books, it is translated as a configuration file. Its functions include:
1. Manage database system resources.
Use Profile to allocate resource quotas. You must set the initialization parameter resource_limit to true.
Alter system set resource_limit = true scope = BOTH;
2. Manage database passwords and verification methods.
By DEFAULT, the user is assigned the DEFAULT profile, which is assigned to each created user. However, this file has no restrictions on resources. Therefore, administrators often need to create profiles based on the environment of their own database systems.

1. PROFILE management content:
1. CPU time
2. I/O usage
3. idle time (idle time)
4. CONNECT TIME)
5. Number of concurrent sessions
6. Password mechanism:

Ii. default profile:
1. All users will be specified with this PROFILE when they are created.
2. The content of the default profile is empty and unlimited.
Bytes ------------------------------------------------------------------------------------

Scenario 58: View profile information in Enterprise Manager

Application Scenario 59: Use the DBA_PROFILES view to view the profile information
SELECT * FROM DBA_PROFILES where profiles = 'default ';

Application Scenario 60: Use the profile to take effect
View the parameter value of RESOURCE_LIMIT:
Show parameter RESOURCE_LIMIT
Set RESOURCE_LIMIT to true;
Alter system set RESOURCE_LIMIT = TRUE;

Application Scenario 61: create a profile in Enterprise Manager

Application Scenario 62: Use the create profile statement to CREATE a PROFILE

Create profile <profile name> LIMIT <configuration item> <value>
ED:
CREATE PROFILE ADMINPROFILE LIMIT
SESSION_PER_USER 2
CPU _ per_session 10000
CPU_PER_CALL DEFAULT
CONNNET_TIME 500
IDLE_TIME 90
PASSWORD_LIFE_TIME 90
PASSWROD_REUSE_TIME 90
PASSWORD_REUSER_MAX 100
FILED_LOGIN_ATTEMPTS 5;

Application Scenario 63: grant the profile to the user

Create user <username> PROFILE <PROFILE>
Eg: create user newuser profile adminprofile identified by pwd;

View the NEWUSER Profile
Select username, profile from DBA_USERS where username = 'newuser ';

Use the alter user statement to modify the USER's profile
Alter user <USERNAME> PROFILE <PROFILENAME>
EG: alter user newuser profile default;
Select username, profile from DBA_USERS where username = 'newuser ';

Application Scenario 64: query the user's resource restrictions and password settings
SELECT p. PROFILE, p. RESOURCE_NAME, p. limit from DBA_USERS DBA_PROFILES p where u. PROFILE = p. profile and u. USERNAME = 'newuser ';

---------------------- Data dictionary ---------------------

A data dictionary is a place where Oracle stores database information. It is used to describe data.

For example, the Creator information, creation time information, tablespace information, and user access permission information of a table.

A database data dictionary is a group of tables and view structures. They are stored in the SYSTEM tablespace.

You can access the data dictionary to view detailed information when you encounter difficulties in performing operations on the data in the database.

You can use SQL statements to access the database data dictionary.

The data dictionary includes:

1. Information about all schema objects in the database, such as tables, views, clusters, and indexes.

2. How much space is allocated and how much space is currently used.

3. Column default value.

4. Constraints on information integrity.

5. Oracle user name.

6. permissions granted to users and roles.

7. Audit information accessed or used by the user.

8. Other generated database information.

Data dictionaries in Oracle are static and dynamic.

1. static data dictionary --> it does not change when users access the data dictionary,

-- For example, a table created by a user

2. The dynamic data dictionary is dependent on the performance of the database and reflects some internal information about the database operation. Therefore, it is not always the same when accessing such data dictionaries.

-- The currently locked object

Static Data Dictionary: This type of data dictionary is mainly composed of tables and views.

The tables in the data dictionary cannot be directly accessed, but the views in the data dictionary can be accessed.

The views in the static data dictionary are divided into three types, which have three prefixes: user _ *, all _ *, and dba _*.

User _*

This view stores information about the objects owned by the current user. (All objects in this user mode)

All _*

This attempt stores the information of objects accessible to the current user. (Compared with user _ *, all _ * does not need to own this object. You only need to have the permission to access this object)

Dba _*

This view stores information about all objects in the database. (The premise is that the current user has the permission to access these databases. Generally, the user must have the Administrator permission)
Bytes -------------------------------------------------------------------------------------
Application Scenario 65: Modify the profile in Enterprise Manager

Scenario 66: Use the alter profile statement to modify the PROFILE
Alter profile <PROFILENAME> LIMT <PAREMETERNAME> <PAREMETERVALUES> ......
Eg:
Alter profile adminprofile limit PASSWORD_LIFE_TIME 60;

Application Scenario 67: delete a Summary File
Drop profile <Summary File Name> [CASCADE];
EG: drop profile adminprofile cascade;

-------------- Schema -----------------
Definition:
A schema is a collection of database objects (used by a user .).
Schema objects are the logical structures that directly refer to the database's data.
A user is a name defined in the database that can connect to and access objects.
Schemas and users help database administrators manage database security.

From the definition, we can see that schema is a set of database objects. To distinguish each set, we need to name this set, these names are the nodes similar to user names that we see in the Enterprise Manager Solution. These nodes similar to user names are actually a schema, which contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.

A user generally corresponds to a schema. The schema name of the user is the same as the user name and serves as the user's default schema. This is why the schema names are database usernames in the Enterprise Manager Solution. You cannot create a new schema in an Oracle database. To create a schema, you can only create one user, but it is not used to create a schema). When creating a user, create a schem with the same name as the user name and use it as the default shcema for the user. That is to say, the number of schemas is the same as the number of users, and the schema names are one-to-one and the same as the names of users. All aliases that can be called schema as users are inaccurate, but it is easier to understand.

A user has a default schema, and Its schema name is the same as the user name. Of course, a user can also use other schemas. If we access a table without specifying which schema the table belongs to, the system will automatically add the default sheman name to the table. For example, when we access the database, we can access the emp table under the scott user through select * from emp; in fact, the complete Syntax of this SQL statement is select * from scott. emp. The full name of an object in the database is schema. object, not user. object. Similarly, if we do not specify the schema of the object when creating the object, the schema of the object is the default schema of the user. This is like a user has a default tablespace, but this user can also use other tablespaces. If we do not specify a tablespace when creating an object, the object is stored in the default tablespace, to store objects in other tablespaces, We need to specify the tablespace of the object when creating the object.

 

 

---------------- Tablespace ------------------------

Tablespace attributes:

A database can contain multiple tablespaces. One tablespace can belong to only one database.

A tablespace contains multiple data files. A single data file can only belong to one tablespace.

Table space can be divided into finer logical storage units

We will introduce the four structures in a step-by-step manner from small to large, and compare them with the differences between Schemas and data files.

Data Block)

A data Block is the minimum unit for storing data in Oracle. Note: This is the minimum unit in the Oracle environment. Oracle uses data blocks to block the differences in the storage structures of different operating systems. In both Windows and Unix/Linux environments, their operating system storage structures and methods, and even character arrangement methods are different. Oracle uses data blocks to block these differences. All data operations are performed on Oracle blocks, which is equivalent to a hierarchical abstraction.

All Oracle operations on data and space allocation are actually operations on data Block. We searched a row from the data table. In fact, Oracle will read the data block of the row from the memory buffer (or hard disk), and then return the specified data row on the data block. Whether in the buffer zone or on the hard disk, Oracle operates data in small units, that is, data blocks.

Data blocks are of a size. When a database is created, you can set them by parameters. Note: in Oracle Database parameters, only the data block size parameters cannot be modified after the database is created. The size of data blocks can be supported by multiple data blocks in a database. However, this generally does not make much sense and may cause a certain burden on management and debugging.

The data block size is specified by the number of kb bytes. The default value is 8 kb. The related parameters are db_block_size. The following statements are used to view the block size.

SQL> show parameter db_block_size;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Db_block_size integer 8192 // 1024 × 8

 
The size of data blocks is set based on different types of systems. If the data block settings are relatively large, there are more data rows to be read at a time, and the memory consumption for the SGA is relatively large, so there may be more swap-in and swap-out caused by specific queries. If the setting is too small, frequent IO logic physical reads may also cause performance problems.

Another parameter related to data blocks is db_file_multiblock_read_count, which indicates the number of data blocks read from physical storage at a time. For some data mining systems, you can consider adjusting this parameter slightly larger.

Zone exten

A partition extent is a storage structure that is larger than a data block. It represents a series of continuous data block sets. We know that physical storage is usually a random read/write process. Even in the same file, we cannot guarantee that the same information is stored in an absolutely continuous physical storage space. The same is true for Oracle data storage.

When storing data information, Oracle allocates data blocks for storage, but it cannot ensure that all allocated data blocks are in a continuous structure. Therefore, the concept of partition extent appears, indicating a series of continuous data block sets.

View dba_extents (or all_extents, user_extents) is an important means for us to study the partition structure and storage structure.

From the view, we can clearly see the characteristics of the partition.

First, partitions are specific to segments. The Data segment is the upper-layer organization unit of the partition. A database object corresponds to a segement, and the database object belongs to different schema (owner. Therefore, you can locate the extent information description in the data area by using different data segment names, different owners, and even different tablespace information.

Another part of the information is about the extent allocation information of the region, such as the ID of the file, the number of the starting data block and the number of data blocks.

Data segment

A Data Segment corresponds to a database object. Generally, a database object corresponds to a data segment. Multiple extents correspond to one data segment. Each data segment is actually represented by a database object. From the dba_segments view, you can clearly see the structure of the data segment.

From the comment information in the segment_type column, we can see that the types of data segments are diverse. Database objects of any type are essentially data segments. Data Tables, indexes, rollback, and clustering are all forms of data segments. At the same time, the data segment is created when the data object is created. As the object volume increases, multiple extents are allocated for management.

 

Another part of the information can be read from dba_segments, that is, the space allocated by the data object, the data block, and the number of partitions. With this view, you can easily obtain the size of all objects in the specified schema.

 

SQL> select owner, sum (bytes)/1024/1024 as vol, sum (blocks) as totalblocks, sum (extents) as totalextents from dba_segments group by owner having wner = 'sys ';

 

OWNER VOL TOTALBLOCKS TOTALEXTENTS

---------------------------------------------------------------

SYS 585.5 74944 3248

In the preceding query, the schema of SYS occupies 585.5 MB, including 74944 data blocks and 3248 partitions.

After an object is created, an extent partition and eight data block are allocated at the segment level.

Note that the data segment is usually related to the data object. A Data Object corresponds to a segment. However, in a partitioned table, a partition corresponds to a segment object. In addition, the segment object can be specified and stored in that tablespace. This is the basis for storage division. Different types of segments are stored in different tablespaces, which are stored in different files and finally distributed in different physical storage.

Partitions are actually stored separately. Generally, an object is not stored across physical storage. A partition table corresponds to multiple segments. Therefore, it is possible to separate buckets from partitioned tables.

Tablespace

TableSpace is the highest level structure in the storage structure. When creating a tablespace, you must specify the files to be stored. One tablespace can specify multiple data files, and multiple files can be stored in different physical storage. That is to say, the tablespace can be stored across physical storage. However, the storage of the lower-level object data segment in a tablespace cannot be specified in that file. Therefore, to allow data objects to access IO load balancing, you must specify different data objects in different tablespaces. This is why data tables and indexes are created in different tablespaces.

Table space is accessed through v $ tablespace.

SQL> desc v $ tablespace;
 

Compared with the preceding Structure View, the structure of the table space view is much simpler, but it only contains some description information. Pay attention to the two parameters.

One is bigfile, and the other is a flag. It indicates whether the tablespace is a big file tablespace. Large file tablespace is a new feature launched in 10 Gb. In terms of performance, you can set a large file tablespace to store more than TB of data, but only one data file is required. The other is flashback_on, which indicates whether the flash back feature of the tablespace is enabled.

Note that the concept set of data table segment blocks can be easily confused with the schema structure. Schema is an organizational concept derived from the theoretical category of classical databases. In oracle, Schema is an organizational concept, and a user corresponds to a schema. Schema is a collection and organization of logical objects. The same table space and other concepts are not hierarchical.

In a schema, objects can be created in any data table space. Only one default tablespace concept is default tablespace. The default tablespace is specified when the user is created.

By distinguishing several core concepts and their respective relationships, you can better understand the various operating mechanisms of Oracle.
----------------------------------------------------------------------------------

Application Scenario 68: View tablespace information in Enterprise Manager

Application Scenario 69: Use the V $ TABLESPACE view to view table space information

SELECT * from v $ TABLESPACE;

Application Scenario 70: View table space attributes

SELECT * FROM DBA_TABLESPACES;

Application Scenario 71: view the tablespace and its information

SELECT * FROM DBA_TABLESPACE_GROUPS;

Application Scenario 72: view the segment information contained in the tablespace

SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM DBA_SEGMENTS;

Application Scenario 73: view the idle interval information in a tablespace

SELECT TABLESPACE_NAME, BYTES, blocks from DBA_FREE_SPACE;

Scenario 74: Create a tablespace in Enterprise Manager

Application Scenario 75: Use the create tablespace statement to CREATE a TABLESPACE

Create a local tablespace
Create tablespace OrclTBS01
DATAFILE 'd: \ app \ Administrator \ oradata \ orcl \ orcltbs01.dbf' SIZE 50 M
Extent management local autoallocate;

Large file tablespace:
Create tablespace OrclTBS02
DATAFILE 'd: \ app \ Administrator \ oradata \ orcl \ OrclTBS02.dbf 'size 30 M
Extent management local uniform size 128 K;

Create bigfile tablespace bigtbs
DATAFILE 'd: \ app \ Administrator \ oradata \ orcl \ bigtbs. dbf' SIZE 10 Gb;

Temporary tablespace:
Create temporary tablespace tmptbs
TEMPFILE 'd: \ app \ Administrator \ oradata \ orcl \ tmptbs. dbf'
SIZE 20 M REUSE
Extent management local uniform size 16 M;

Application Scenario 76: Segment management in a tablespace

Create a tablespace for automatic segment management;
Create tablespace OrclTBS01
DATAFILE 'f: \ app \ Administrator \ oradata \ orcl \ orcltbs01.dbf' SIZE 30 M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
Segment space management auto;

Table space managed by creation means:
Create tablespace OrclTBS02
DATAFILE 'f: \ app \ Administrator \ oradata \ orcl \ orcltbs02.dbf' SIZE 30 M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
Segment space management manual;

Scenario 77: Manage data blocks in a tablespace:

When creating a tablespace, the size of the data block used by the tablespace is 8 KB.

Create tablespace OrclTBS01
DATAFILE 'd: \ app \ Administrator \ oradata \ orcl \ orcltbs01.dbf' SIZE 30 M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8 K;

Scenario 78: Create an undo tablespace
Function:
When the ROOLBACK command is executed, the regression operation is completed.
Restore database
Use flashback to query and analyze data from previous time points.
Use Flashback Technology to restore data from logical damages.

Create undo tablespace undotbs01
DATAFILE 'd: \ app \ Administrator \ oradata \ orcl \ undotbs02.dbf 'size 2 m reuse;

Application Scenario 79: Modify the tablespace attributes in Enterprise Manager.

Application Scenario 80: Use the alter tablespace statement to rename a TABLESPACE

Alter tablespace OrclTBS02 rename to OrclTBS03;

SELECT * from v $ TABLESPACE;

Application Scenario 81: add data files to local tablespace Management

Alter tablespace OrclTBS01
Add datafile 'd: \ app \ Administrator \ oradata \ orcl \ orcltbs11.dbf' SIZE 10 M;

Application Scenario 82: Add a temporary file to a temporary tablespace:
Alter tablespace tmptbs
Add tempfile 'f: \ app \ Administrator \ oradata \ orcl \ tmptbs01.dbf' SIZE 20 M;

Application Scenario 83: Modify the attributes of a large file tablespace:

Alter tablespace bigtbs RESIZE 4G;

Scenario 84: Set the tablespace status:

Alter tablespace OrclTBS01 OFFLINE;

SELECT TABLESPACE_NAME, CONTENTS, status from DBA_TABLESPACES;

Alter tablespace OrclTBS01 ONLINE;

Application Scenario 85: Set the read-only tablespace

Alter tablespace OrclTBS01 OFFLINE;

SELECT TABLESPACE_NAME, CONTENTS, status from DBA_TABLESPACES;

Alter tablespace OrclTBS01 ONLINE;

Scenario 86: delete a tablespace

Drop tablespace OrclTBS03;

Drop tablespace OrclTBS01 including contents;

Drop tablespace OrclTBS01 including contents and datafiles;

Application Scenario 87: Statistical table space usage

SELECT c. tablespace_name "tablespace ",
ROUND (a. bytes/1024/1024, 2) "tablespace size ",
ROUND (a. bytes-b.bytes)/1048576, 2) "space used ",
ROUND (B. bytes/1048576, 2) "available space ",
ROUND (B. bytes/a. bytes *, 2) | '%' "percentage remaining"
FROM
(SELECT tablespace_name, SUM (bytes) bytes FROM DBA_DATA_FILES
Group by tablespace_name),
(SELECT a. tablespace_name, NVL (SUM (B. bytes), 0) bytes FROM DBA_DATA_FILES a, DBA_FREE_SPACE B
WHERE a. tablespace_name = B. tablespace_name (+) AND a. file_id = B. file_id (+)
Group by a. tablespace_name) B,
DBA_TABLESPACES c
WHERE a. tablespace_name = B. tablespace_name (+) AND a. tablespace_name = c. tablespace_name
Order by round (B. bytes/1024/1024, 2 );

Application Scenario 88: CREATE a temporary tablespace group in the create tablespace statement

Create temporary tablespace temp_spc
TEMPFILE 'd: \ app \ Administrator \ oradata \ orcl \ temp03.dbf'
SIZE 20 M
Tablespace group temp_grp;

Application Scenario 89: Use the alter tablespace statement to create a temporary TABLESPACE Group

Alter tablespace temp_spc tablespace group new_temp_group

Application Scenario 90: View and add temporary tablespace groups in Enterprise Manager

Application Scenario 91: View DBA_TABLESPACE_GROUPS to view temporary tablespace Information

SELECT * FROM DBA_TABLESPACE_GROUPS;

Application Scenario 92: Remove a tablespace from a temporary tablespace Group

Alter tablespace temp_spc tablespace group '';

Application Scenario 93: Table space group allocation to specified users

Create user lee identified by leepass
Default tablespace Users
Temporary tablespace NEW_TEMP_GROUP;

Alter user lee temporary tablespace NEW_TEMP_GROUP;

Select username, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
FROM DBA_USERS where username = 'lil ';
 

Alter database orcl default temporary tablespace NEW_TEMP_GROUP;

Application Scenario 94: View rollback segment information:

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, NEXT_EXTENT
FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'rollback ';

Col name for A12
SELECT s. USN, n. NAME, s. EXTENTS, s. OPTSIZE, s. HWMSIZE, s. STATUS
From v $ ROLLSTAT s, V $ ROLLNAME n
WHERE s. USN = n. USN;

Application Scenario 95: Set the rollback segment Management Mode

Show parameter UNDO_MANAGEMENT

Alter system set UNDO_MANAGEMENT = MANUAL
SCOPE = SPFILE;

Application Scenario 96: reasonably plan and create rollback segments

Create rollback segment OrclRs01
TABLESPACE UndoTBS1
STORAGE (INITIAL 5 M
NEXT 2 M
Maxextents unlimited );

Application Scenario 97: Modify the attributes of a rollback segment:

Alter rollback segment OrclRs01 ONLINE;

Select name, status from v $ ROLLNAME, V $ ROLLSTAT
Where v $ ROLLSTAT. USN = V $ ROLLNAME. USN;

Alter rollback segment OrclRs01 shrink to 1 M;

Application Scenario 98: delete rollback segments

Alter rollback segment OrclRs01 OFFLINE;
Drop rollback segment OrclRs01;