Java Foundation--oracle (iii)

I. ORACLE internal structure

1. Table Space

A tablespace is a logical division of a database, a table space belongs to only one database, each table space consists of one or more data files, and other logical structures in the table space are stored in these data files, the general Oracle system after installation, will automatically establish more than one table space,

oarcle11g Main Table space//attached (also the same)

1). Example: sample tablespace for storing schema object information and training materials for the sample database

2). System: is a systems tablespace for storing data from internal Oracle system tables and data dictionaries, such as table names, column names, user names, etc., and generally does not favor the storage of user-created tables, indexes, etc. in the system table space

3). Sysaux: is a secondary system table space that primarily stores objects from common sample users inside Oracle systems, such as tables and indexes that store CMR users. Thus reducing the system table space of the negative, it is generally not stored in the user's data, by the Oracle system internal automatic Maintenance

4). UNDOTBSI: is a redo table space that stores data about redo information in the database when the user modifies the data table (Insert,update,delete). The Oracle system automatically uses the redo table space to temporarily hold the pre-modified data, and when the modifications are completed and submitted, the system retains the amount of time before the data is modified to free up some space in the redo table space.

5). Users: is a user tablespace that holds data and private information for persistent user objects, and therefore is also called a data table space, and each database should have a user tablespace to assign to users when they are created.

In addition to the table spaces created by default by the Oracle system, users can create multiple tablespaces based on the size of the application system and the objects they want to store to differentiate between user data and system data.

/* below slightly

2. Table

Objects that hold user data

3. Constraint conditions

Constraints can be created for one column of a table (Constraint)

1). PRIMARY KEY constraint

2). Default Constraints

3). Check

4). Unique

5). Foreign key

4. Partitioning (Partition)

In a very large database, you can simplify the management of the database by dividing the data of a large table into smaller tables called partitions, which, in addition to partitioning the table, can partition the index, not only simplifying the management of the database, but also improving its application performance, in Oracle, the ability to subdivide partitions, Create sub-partitions, for example, you can split a table based on a set of values, and then split the partition according to another method.
5. Index

To be able to find the data, each row in the table is identified with a rowid table, rowID tells the database exactly where the line is located, the file where the package is expanded, the block in the file, and the line address in the block.

6. User

The user account is not a physical structure in the database, but it is important to the objects in the database because the user owns the data object, for example, the SYS user owns the data dictionary table, and the user system has a view to access the data dictionary table.

Creating objects for a database must (for example, a table) be performed under a user account, and each user account can be customized to make a specific tablespace as its default table space

7. The programme

A user account has an object set called a user's scenario (SCHMEA)

8. Synonyms

To provide a simple name that uniquely identifies a database object when using database objects for different users, you can create synonyms for database objects.

9. Permissions and Roles

10. Segments, extents and data blocks

Depending on the nature of the data processing, it may be necessary to separate the different regions within the table space to hold different data, called segments (Segment), for example, the area where the data is to be called the "Data section", the index is called the "index section", because the segment is a physical entity, So it has to be allocated to a table space in the database (in a data file in the tablespace), and the segment is actually made up of many extents (exctent), and when the space in the paragraph is exhausted, the segment is given additional extents. The data block is the smallest storage unit, and the Oracle database is a multiple of the operating system block.

Second, Oracle external structure

1. Data files

Each Oracle database has one or more data files (datafile), and a data file can only belong to one table space, and the data file is created to change the size, creating a new tablespace to create a new data file, once the data file is added to the tablespace, it cannot be removed from the table space. Nor can it be associated with other table spaces.

2. Redo log file (Redo log files)

Oracle holds logs for all database transactions, which are recorded in the online log file (Rodo log files), and the data is corrupted and can be recovered using these logs. A database requires at least two redo log files, Oracle writes to the log file in a circular form, the first log is filled, then to the second write, and so on, all log files are filled and then back to the first one, overwriting the data of the new transaction.

3. Control files

The external structure used to document and describe the database, which includes

1) Oracle database name and settling time

2) data file and reset log file name and location

3) log file record sequence code (log sequence Nmuber)

Whenever the database is activated, Oracle will read the control file contents immediately after the instance is activated, and the database will not start until the information is collected in all the external structure files of the database, so that the database system can be stopped because of the destruction of the control files of the rabbit, it is recommended that users configure at least two control files and store them on different disks.

Third, users in Oracle (here is the focus of learning)

---sys user

Superuser, most with the highest privileges, has the SYSDBA role, the owner of the data dictionary, the super-privilege owner (SYSDBA), which has CreateDatabase permissions (only it has!). )

The initial password is change_on_install and cannot be deleted by this user.

---system user

Manage operators with very large permissions, with Sysopert roles but no CreateDatabase permissions

Its default password is manager, and this user cannot be deleted.

---Scott users

Contains the sample list EMP and DEPT, can change the password, the user can be deleted, in the product environment, it is recommended to delete or lock, it is a normal user.

The default password is Tiget, which is locked by default and can be used after it is unlocked.

How to unlock:

Log in with a high-privileged user, alter user Scott account unlock;

And Scott's peers: Adams/wood, Jones/steel, Clark/cloth and Blake/paper.

---outln users

Optimize the planned storage outline user, password is outln

It is recommended that the password be modified immediately after creation and this user cannot be deleted.

---hr/hr users

(Human Resources), Oe/oe (Order Entry), sh/sh (Sales history).

Experiment, test user, sample table employees and departments

Can modify the password, the user can be deleted in the product environment is recommended to delete or lock.

---dbsnmp users

Password: DBSNMP

The user in the database used for the intelligent agent (Intelligent agent), to monitor and manage the database related performance of the user, if the user is stopped, you can not extract the relevant data information; Change the password, you need to put a new password to SNMP_ Rw.ora file, if you do not need intelligent Agents, you can delete it.

---sysman users

Is the user for EM management in the Oracle database and can be deleted if you do not use the user. (Em:enterprise manage Web-style graphical management, like SQL Server Enterprise Manager can manage, optimize, etc.)

Iv. creation of the initial user and table space

Open Sqlplus

Create a table space, create a user

1) First create the directory on disk F:/my_oracle_data2

2) Create a temporary tablespace

sql> Create temporary tablespace user_temp   // temporary tablespace name (user_temp) is self-fetching tempfile ' F:\\my_ ORACLE_DATA2\USER_TEMP.DBF ' size50m   //initial size autoextend on    // set self-increment next 50m maxsize 20480m  // set temporary table space  each time the self-increment 50 MB maximum is 20480 trillion extent management local;  // represents the table space using the local surface space management, which corresponds to the Data dictionary table space Management (many disadvantages, 9i later discarded)

3) CREATE TABLE space

Create  ' F:\\MY_ORACLE_DATA2\USER_DATA.DBF 'size 50mautoextend onnext 50m maxsize 20480mextent management local;

4) Create the user and specify the tablespace

default tablespace user_data2 temporary tablespace user_temp; // Create a user's password specifies which tablespace the user is the default temporary table empty

If you use this user login conn Sa/sa the following error will appear

Ora-01045:user SA lacks CREATE SESSION privilege; Logon denied reason is not authorized

Authorizing Grant Connect,resource to SA;

Finally, we will find the table space we created in the specified directory:

Note: Connect,resource is a role name, with which you can connect to the database and create a table in the table space

Attached: query all of the table space and data file correspondence

SELECT * FROM Dba_data_files

Java Foundation--oracle (iii)