Chapter II Application of Oracle database

Chapter II Application of Oracle database
2.1 Table space and user Rights management
2.1.1 Table Space
2.1.1.1 Classification:
Permanent Table Space
Temporary table space
Undo Table Space
Purpose of the 2.1.1.2 table space
Assign different spaces to different users
Create a different data file to a different disk
2.1.1.3 Creating table Spaces
2.1.1.3.1 Syntax:
CREATE tablespace Tablespacename
datafile ' filename ' [SIZE integet [k| H]]
[Autoextend [OFF | On]];
2.1.1.4 Deleting a table space
2.1.1.4.1 Syntax:
DROP tablespace tablespacename;
2.1.2 Customizing user permissions
2.1.2.1 Sys: Super User
2.1.2.2 System: Systems Administrator
2.1.2.3 Scott: Demo Users
2.1.3 Database Rights Management
2.1.3.1 System permissions:
2.1.3.1.1 definition: Perform some system-level permission operation in the database
2.1.3.1.2 Common system permissions:
CREATE SESSION: Connecting to a Database
CREATE TABLE: Creating tables
Create VIEW: Creating Entities
Create SEQUENCE: Creating a sequence
2.1.3.2 Object Permissions
2.1.3.2.1 Definition: User-owned permissions on specific objects in the database
2.1.3.2.2 Access to access:
Connect: A user connected to a database, especially a user who does not need to create a table, is typically granted this permission;
RESOURCE: A more reliable and formal database user can grant this role;
DBA: The database administrator, the highest privilege for managing the database;
2.1.3.2.3 the syntax for granting permissions:
GRANT Permissions | role to User;
2.1.3.2.4 The syntax for revoking permissions:
REVOKE Permissions | role from user;
2.1.3.2.5 Database user Security Design principle
1, according to the minimum allocation principle
2, the user divided into management, application, maintenance, backup four categories
3, not allowed to use SYS and system users to establish database application objects
4. Prohibit grant DBA to user;
2.2 Sequence
2.2.1 Creating a sequence
Grammar:
CREATE SEQUENCE Sequence_name
[START with integer]----> Specify the first sequence number to generate
[INCREMENT by integer]----> Specify the interval between serial numbers
[MAXVALUE Integer | Nomaxvalue]----> Specifies the maximum value that can be generated for a sequence, if Nomaxvalue is specified, the maximum is 10*27
[MINVALUE Integer | Nominvalue]----> Specifies the minimum value that can be generated for a sequence, if Nominvalue is specified, the minimum is 10*26
[CYCLE | Nocycle]----> Pre-assign a set of serial numbers
[CACHE Integer |    NOCACHE]; ----> does not assign a set of serial numbers, and if two are ignored, the system caches 20 serial numbers by default
2.2.2 Access sequence
Nextval: Returns the initial value of the sequence when used for the first time
Currval: Returns the current value of the sequence
2.2.3 Changing a sequence
Grammar:
ALTER SEQUENCE [schema.] Sequence_name
[INCREMENT by integer]
[MAXVALUE Integer | Nomaxvalue]
[MINVALUE Integer | Nominvalue]
[CYCLE | Nocycle]
[CACHE Integer | NOCACHE];
Note: You cannot modify the start with parameter of a sequence, and when modifying a sequence, be aware that the minimum value of the ascending sequence should be less than the maximum value;
2.2.3 Deleting a sequence
Grammar:
DROP SEQUENCE [schema.] Sequence_name
Example: Drop SEQUENCE seql;---> Delete seql sequence from the database
2.2.3 using sequences
2.3 Synonyms
2.3.1 Use of synonyms
1. Simplifying SQL statements
2. Hide the name and owner of an object
3. Remote objects with a bit of distributed database provide location transparency
4. Providing public access to objects
2.3.2 Synonyms Classification
2.3.2.1 Private synonyms
1. Create syntax:
CREATE [OR REPLACE] synonym [schema.] Synonym_name---> Synonym names
for [schema.] object_name---> Replace this synonym in the presence of a synonym
2.3.2.2 Public synonyms
1. Create syntax:
CREATE [OR REPLACE] public synonym [schema.] Synonym_name
for [schema.] object_name
2.3.2.3 the difference between a private synonym and a public synonym
1. Private synonyms can only be accessed in the current mode, and cannot have the same name as the current schema object
2. Public synonym can be accessed by all database users
3, create synonyms need to have a certain permission
2.3.2 Deleting synonyms
Grammar:
DROP [public] synonym [schema.] Synonym_name
2.4 Sequence
2.4.1 What is a sequence?
Definition: A sequence is an optional structure associated with a table, a way to quickly access a database to improve database performance
Classification of 2.4.2 Sequences
2.4.2.1, partitioned, or non-partitioned indexes for single-column or composite indexes
2.4.2.2, B-Tree index (identity index) unique or non-unique index
2.4.2.3, normal, or reverse key index based on function index
2.4.2.4, bitmap index
2.4.2.5 B-Tree Index
Grammar:
CREATE [UNIQUE] INDEX index_name on table_name (column_list)
[Tablespace Tablespace_name]
Unique: Specify a unique index
Index_name: Create the name of the index
Column_list: List of column names on which indexes are created, based on multiple columns
2.4.2.6 unique or non-unique index
Unique index definition: No two rows in the column that defines the index have duplicate values
Non-unique index definition: A single keyword can have multiple rows associated with it
2.4.2.7 Reverse Key index
Definition: Reverse the byte of an indexed column while saving sequence order
2.4.2.8-bit Graph index
Advantages: 1, for a large number of real-time queries
2. Reduced footprint compared to other indexing technologies
3, the configuration of low-end hardware, the performance is also very good
2.4.2.9 Other Indexes
Composite index: Created on multiple columns within a table, the index column does not have to be the same as the column order in the table
Function-based index: one or more columns that use a function or expression time to identify the index that is being established, then create a function-based index
2.4.3 Creating a sequence principle
1~8 Textbook 58~59 Page
2.4.4 Deleting an index
2.4.4.1 DROP Index statement
Example: DROP index name;
2.4.4.2 when to delete an index
1. The application no longer needs an index
2. Execute Bulk Load Money Delete Index
3. The index is corrupted
2.4.5 Rebuilding an index
2.4.5.1 ALTER INDEX ... Rebuild statements
Example: ALTER index index name REBUILD noreverse;
2.4.5.2 when to rebuild an index
1. After the user table is moved to a new table space
2. The index already contains many deleted items
3. You need to convert the existing normal index into a reverse key index
2.5 Partition Table
2.5.1 What partition table
2.5.1.1 definition: Divides all rows in a table into sections and stores them in different locations, partitioned tables are called partitioned tables
2.5.1.2 Advantages:
1, improve the table query
2, the table easier to manage
3. Easy Backup and Recovery
4. Improve data security
2.5.2 Classification of partitioned tables
2.5.2.1, Range partitioning
2.5.2.2, List partition
2.5.2.3, hash partition
2.5.2.4, Composite partitioning
2.5.2.5, Interval partition
2.5.2.6, Virtual partitions

Chapter II Application of Oracle database