OCP factory training notes (the next day)

Segment segment, partition extent, block logical concept
Table. A-> heap, heap table, relatively slow (records in the heap table are not ordered)

Each block has an SCN header and some data is written at the end. (If the block breaks down, the entire block will become unidentifiable)
If there is a 12 K table, it will be allocated to two blocks.

The role of tablespace: Are a repository for schema object data;
Datafile: Oracle automatically stores the table here (table cannot be specified to datafile, but can only be specified to tablespace)

OMF
Db_create_file_dest
If db_create_file_dest_n is set, specify the image to multiple locations.
Db_recovery_file_dest: Flash back recovery Region

Extent Management
Local management: manage through bitmap
Dictionary management; insert data through the FET $ and vet $ tables. (Sys recursive SQL may be generated, affecting system functions)
If local management is performed, the preceding sys recursive SQL statement is not generated.

When a user's permanent tablespace is not specified, users is assigned as the default permanent tablespace.

V $ tablespace has several forms:
Large table space: One tablespace can only have one datafile
Extent allocation: 1> automatic Automation
2> uniform size
Select Automatic extent allocation. The first 16 disk areas are 64 KB, 8*1 m ,? * 8 MB

Segment management method:
1> automatic: Manages bitmap blocks.
2> Manual: freelist
Generally, the first management method is selected, and the automatic management method has better concurrency. This management method is called automatic segment space management (assm)

When a table space is in the logging mode
1> Update and delete (redolog is still generated)
2> Create Table T1 as select * From **; Redo is not generated
You can set table-level logging and unlogging. If you do not specify this table-level logging, logging is automatically inherited from the tablespace level.

Alter tablespace offline = alter tablespace offline normal)
Alter tablespace immediate; No checkpoint occurs. Media recovery must be performed at the next startup;
Alter tablespace temporary; (if normal down can be performed, complete the checkpoint first and then normal down (similar to normal );
If the file breaks down, forced offline (similar to immediate) occurs)

 

Note the following when creating a tablespace:
When creating a tablespace name, we recommend that you do not use "" to specify the file name, otherwise it will be case sensitive in the future.
If the reuse parameter is specified, if the datafile name is the same, the original tablespace will be overwritten.

The tablespace managed by the dictionary in the database can be changed to a local management mode by calling a PLSQL package and using the stored procedure in it.

Dba_tablespace: it is stored in the system tablespace and can be viewed only when the database is in the open state.
V $ tablespace: dynamic view. You can view different content in different states, but the content of dba_tablespace is not detailed.

Disk Area ing diagram:
Storage-> tablespace-> View tablespace content-> extent map

In theory, a database has a maximum of 65000 datafiles.
ASM; file system. Volume Manager (I/O State balance)
(Can the data be retrieved after the ASM instance is deleted)
Yes, Oracle Support has this software. Read the header of the raw disk of the ASM disk and restore it. Start the ASM instance.

Insert a block,
User Data is inserted from bottom up, and system data is inserted from top down.

What is the size of a large table in a database? No restrictions.

Grant and revoke permissions:
Grant, revoke

Two Roles of profile: password management and resource management

Sys user system user comparison:
Sys attributes: DBA account, admin option, permission for data dictionary management, and AWR report
System: You will have some additional tool views.
User: SYS has DBA permission and sysdba permission.
System: You have the DBA permission (system permission). system does not have the permission to close the database because it only has the DBA permission.

Differences between dBA and sysdba:
Permission: DBA role permissions are compound.
Sysdba: permissions are atomic and cannot be divided (system permissions)

Passwd authentication method
1> password (in data dictionary)
2> external (OS authentication)
3> global (SSO single-point authentication ,)
Common User and administrator user

If the operating system account tsmith exists in the database as ops $ tsmith, this user can directly log on to the database using the operating system login method.
Operating system authentication takes precedence over Password File authentication.

Operating system authentication content:
Because Oracle is a member of oinstall, (sysdba) Oracle can log on through the operating system command.
Run the following command to view all users with sysdba permissions:
SQL> select * from V $ pwfile_users;
The default system does not have sysdba system permissions.

However, you can use the grant command to grant system permissions.

User Permissions are divided into 1> system permissions (Open shutdown)
2> Object object permissions (Update, delete)

Relationship between system permissions: a B c is not connected at a level. A grants B permissions D and B grants C permissions D (Open shutdown)
A can take the permission from B, but does not take the permission from C.

Object permission: a B C-level connection
If a takes the permission D from B, take the permission from C (Update, delete)

The final permission of a role is a set of all its permissions.

Different Versions:
Compared with 9i and 10g, 9i connect has more connect permissions.
Resource has an unlimited space quota.

 

Default role; default role
Set role vocationdba;
Assign roles to users
Create user U1 identified by U1 quota 10 m on users;
Create role R1;
Grant create table to R1;
Grant R1 to U1;

Non-default role (open afterwards)
SQL> alter user U1 default role none; (U1 default role is not activated)
However, you can activate this role on your own.

SQL> conn u1/u1
SQL> set role R1;

Two Functions of the user's profile: 1> Control File
2> restrict resources (prevent hackers)
CPU time, connection time, server control time

If the database is in the shared connection mode, the SGA occupied by the session is private.

The prerequisite for setting the profile is,
You must first set the system parameter resource_limit, alter system set resource_limit = true;
In this way, the profile resource limit will take effect.

It is not recommended to use reserved words and spaces as the object name.
The maximum length of db_link is 128 bytes.

"" Double quotation marks are used for objects, which are case sensitive.
'''Date, single character quotes
Varchar2: more space than varchar
Nunmber: numeric type
Timestamp: expands the time interval by one thousandth of a second (more precise than date)
Clob: big string type
BLOB: A photo is used as a data file to store data. A photo file is directly stored in a data file.
Bfile: File Name Reference
External table:

Constraint: 1> primary key constraint: The primary key column cannot have null values or duplicate values (primary key constraint = special check constraint)
2> unique constraint. A column can have only one
3> check constraints, such as limiting the number of employees between the ages of 14 and 18
4> primary key and foreign key constraints
Department killing cannot be killed (the foreign key indexed to him must be removed first)

Page 201,
Disable constraint (disable novalidate does not check constraints) after data is inserted, check again.
If you have set disable validate for a table, you cannot perform DML operations on the table.

The default constraint is non-latency.

 

 

 

 

 

Drop table HR. Employees purge (if 'purge' is not added, return to the recycle bin. If it is added, it is completely deleted)

Only primary key columns and unique columns can be used as foreign keys of other tables.

Truncate (DDL. Each statement is implicitly committed as an independent transaction. The where condition cannot be added. Only the table data is deleted and the structure is retained)
Delete (rollback is supported, which belongs to DML)
Drop (Delete the entire table, including the table structure, which is a DDL Statement)

Drop and truncate consume almost the same amount of resources as they are all DDL operations.

The index contains two contents: [key and row pointer].
Two types of indexes: bitmap index and B-TREE Index
The rowid of the B-tree index includes the file name, Object Name, block number, and row offset,
If you use rowid to search for data, you only need one Io to operate it.

Bitmap index: We recommend that you use Bitmap indexes when there are many duplicate values. (For example, if an index is created for a column value, which indicates the color, a color can be represented by a row. In this way, if you want to find all columns in red and blue, you can perform one or more operations on the corresponding rows)

The disadvantage of Bitmap indexes is that the entire index table will be locked with a new column. Therefore, bitmap indexes are only suitable for Query Systems (Query Systems with many repeated values ).
By default, Oracle does not create an index for external keys. Only primary keys and unique keys can be used to create an index.

Anti-Key Index:
The command is similar to alter index reverse.
If data is inserted to, blocks, the hotspot block will be generated in the indexed file block, and the query may be delayed,
After the key value is turned over, it becomes,. In this way, the respective blocks in the index file are placed in different locations.
This avoids the appearance of hotspot blocks in indexes.
The disadvantage of the anti-key index is that it does not support the range query well (because the corresponding data in the anti-key index is not stored in sequence). It is compared when used in the equivalent query.
If range query is performed at this time, it will become a full table scan.

Syntax for creating an index: Create index my_index on table ()
An index is created. The default type is B-tree index.

Function Index:
The reason for creating a function index is that all indexes are placed in the function and cannot be used.
For example:
Create index indx1 on HR. Employees (upper (last_name ));
Indexes cannot be used,
To solve this problem, we can use function indexes.

 

View concept:
To hide some sensitive information, you can open (retain sensitive information) some options of some base tables to create a view.
If we modify this view, the contents of the base table below will be directly modified.

Materialized View combines data on the base table to generate an actual table. Modifications to the materialized view do not affect the base table.

224
Sequence object: sequence is used only for the current object.
Sequence Current: The current sequence value.
Sequnnce. nextval: Specifies the global Sequence Value.

For example:
Create sequence S1 start with 1; (if there is no write increment, each sequence increments with one)
Select s1.currval from dual; view the current sequence of the session
Insert into T1 values (s1.nextval, 'limin ');

After a sequence object is generated, it must be unique but not continuous.
(A session generates a sequence. If rollbak or power is down, the sequence serial number will disappear)

Temporary table:
Temporary tables can generate undo, but do not generate redo.
In the temp table, after a commit occurs, only the records in the table are deleted and the definitions are saved.

For example:
Create global temporary table t_dept as select * from orders;
This will create a table, t_dept, but there will be no data in it. Because the above is a complete session.
Create global temporary table t_dept as select * from orders on commit reserve (Session-based, released only when the session is disconnected)

View:
DBA _ Only Administrators can view
All _ Everyone can view
User _ users can view

Questions: view all tables under the HR account as an administrator
Select table_name from dba_tables where owner = 'hr ';

For example
Su-Oracle
Conn HR/hr
Create Table dept as select * from orders;
Create Table EMP as select * from employees;
Create Table ** as select ** from **
When creating these tables, only the non-null constraints of the original table will be copied. Other constraints will not be copied.

Exercise:
Use em to create constraints:
Tables-> edit-> table-> Constraints
Create a composite index (no keywords)
Create a function index.
Create index indx1 on EMP (upper (last_name ));
Investigate the indexes and types of indexes in the EMP table.
Select index_name, index_type from user_indexes;

DML language merge = Update + insert (conditional update) Merge

Oracle transaction start and end:
Starting from the first DML statement implicit
Transaction to commit, rollbak ends

For example:
1. Select
2. Insert
3. Update
4. Create Table T1 (DDL)
When you execute 4th statements, it checks the previous statement by default. If no commit is made, it generates an implicit commit.

To use ISQLPLUS, follow these steps:
ISQLPLUS start
% ORACLE_HOME/install/port. lst
/5560/isqlplus

How to browse from a browser:
Edsir2p0.us.oracle.com: 5560/isqlplus