Oracle database applications

01. Table Space

We know that the Oarcle database really holds the data file (data files), the oarcle tablespace (tablespaces) is actually a logical concept, and he does not physically exist, so a set of data files will be a table space to twist together.

Create a table space using the Create TABLESPACE statement, which is a spatial allocation in the database that can contain schema objects.

Table Space Properties:

A database can contain more than one table space, and a table space can belong to a single database

A table space contains multiple data files, and one data file can belong to only one table space

Table This space can be divided into finer logical storage units

Table Space Classification:

A persistent table space contains persisted schema objects. Objects in a permanent tablespace are stored in a data file.

A undo tablespace is a permanent table that manages the restoration of data by using an Oracle database if you are running a database in automatic revocation management mode. Oracle strongly recommends that you use the automatic undo management mode instead of using rollback segments for Undo.

A temporary tablespace contains a schema object that has only one session duration. Objects in a temporary tablespace are stored in a temporary file.

When you create a tablespace, it is initially a read/write tablespace. You can then use the ALTER TABLESPACE statement to take the tablespace offline or online, add a data file or temporary file to it, or make it read-only tablespace.

You can also use the Drop TABLESPACE statement to remove tablespace from the database.

Table Space Effect

Easy Storage Management

Improve I/O performance

Backup and Recovery

1 --View all table spaces for the current database note Administrator privileges required2 SelectTablespace_name fromdba_tablespaces;3 --authorize tablespace permissions with system logon4 GRANT CREATETablespace toLiutao5 6 --Specify table spaces while creating a table7 Create TableGrade8 (9Gid Number Primary Key  not NULL,TenGname Nvarchar2 ( +) One ) tablespace jbit A  - --Create a table space first and create at least one physical file specified - Createtablespace jbit theDataFile'E:\app\Administrator\oradata\orcl\jbit. DBF' -Size 10M--Initial Size -Autoextend on Next32M MaxSize Unlimited -  + --To remove permissions by using the System Logon authorization table space - GRANT DropTablespace toLiutao + --Delete a table space while deleting a physical file A DropTablespace jbit including contents andDatafiles

02. Sequence

sequence is the sequence of objects used to generate a unique, sequential ordinal can be ascending or descending

Create syntax:

Create sequence sequence name
[Start with initial volume]
[Increment by increment]
[MaxValue Maximum value | nomaxvalue]
[MinValue Minimum value | nominvalue]
[Cycle | nocycle]
[Cache Cache number | nocache];

Start with: The first sequence number is generated, and the default value for the ascending sequence is the minimum sequence value, and for descending sequence, the default value is the maximum value of the sequence.
Increment by: Used to specify the interval between serial numbers, the default value is 1, if integer is positive, the resulting sequence is in ascending order, and if integer is negative, the resulting sequence is sorted in descending order.
MaxValue: The maximum value that a sequence can generate.
Nomaxvalue:oracle sets the maximum value of the ascending sequence to 1027, and sets the maximum value of the descending sequence to-1. This is the default option.
The minvalue:minvalue must be less than or equal to the value of start with and must be less than the value of MaxValue.
Nominvalue:oracle sets the minimum value in ascending order to 1, or sets the minimum value of the descending sequence to-1026. This is the default value.
Cycle: After reaching the maximum or minimum value, the sequence continues to generate values from the beginning.
Nocycle: The sequence will no longer be able to generate a value after it reaches the maximum or minimum value. Do not write default to nocycle This is the default option.
Cache: Pre-allocating a set of serial numbers and keeping them in memory for faster access to serial numbers. When all the serial numbers in the cache are exhausted. Oracle generates another set of values and retains them in the cache.
NoCache: The serial number is not pre-allocated for faster access, and if the cache and Nocache,orcale are ignored when the sequence is created, 20 serial numbers will be cached by default.

To access the value of a sequence through a pseudo-column of a sequence

Nextval returns the next value of the sequence

Currval Returns the current value of the sequence

04.sys_guid function

1 --Create a sequence2 Createsequence Seq_first3 --querying the next sequence4 SelectSeq_first.nextval fromDual5 --querying the current sequence6 SelectSeq_first.currval fromDual7 --using sequences8 Insert  intoGrade (Gid,gname)VALUES(Seq_first.nextval,'SSH to develop an enterprise-class framework')

9--Delete sequence of SQL drop SEQUENCE seq_newsid;

03. Synonyms

Synonyms : Working with Objects (Table,View, synonym ) an alias that does not occupy any actual storage space, but Oracle In the data dictionary, and when using synonyms, the Oracle it will be translated to the name of the corresponding object.

function: The name of the masked object and its holder are simplified for the user SQL Statements

Classification

Private Synonyms

A private synonym can only be accessed within its schema and cannot have the same name as an object in the current schema

Public synonyms

public synonyms can be accessed by all database users

1 --Synonyms2 --Private Synonyms3     --A private synonym can only be accessed within its schema and cannot have the same name as an object in the current schema4 --Public synonyms5      --public synonyms can be accessed by all database users6 --Private Synonyms7 --permission to grant a private synonym using Ssytem login8 Grant CreateSynonym toLiutao9 --create a synonym for a student table with Liutso loginTen CREATESynonym Stu forLiutao. STUDENT One  A --create a common synonym permission - GRANT Create  PublicSynonym toLiutao -  the --log in with Liutao to create synonyms for class tables - CREATE  PublicSynonym GRA forLiutao.grade - Select *  fromGRA - --Liutao Login Mode Authorization + Grant Select  onGrade to  Public - --view synonyms for a table + SELECT *  fromSYS. All_synonymsWHEREtable_name='GRADE' A --Delete a private synonym at  Dropsynonym Gra - --Use System Login to grant Liutao permission to remove common synonyms - Grant Drop  PublicSynonym toLiutao - --Delete common synonyms drop public synonym public_emp; To delete a public synonym named Public_emp - Drop  PublicSynonym GRA

Small supplement: Reclaim Permissions

1 -- revoke permissions with system logon 2 Revoke Drop  from Liutao

Oracle database applications