oracle-Data Table Objects

A table generally refers to a relational table, and it can also generate object tables and temporary tables. Total, the object table is generated from a user-defined data type, and temporary tables are used to store temporary data that is dedicated to a transaction or session

Character type :

Fixed length: char (1-2000 bytes)

Variable length: varchar2
Numeric Type: number (Precision,scale)

Date Time Type :

The default date and time format is specified by the initialization parameter Nls_date_format, which is typically dd-mm-yy

LOB Type :

Used for large, unstructured data, such as binaries, pictures, and other types of external files. Data for LOB types can be stored directly in the database or stored in an external file, and pointers to data are stored in the database. LOB is divided into blob,clob,bfile

BLOB: Used to store binary objects. A typical blob can include objects, audio files, video files, and so on. Ability to store binary objects up to 128MB in blob type

CLOB: A large object that stores character formatting, and fields of type CLOB can store up to 128MB objects. Oracle first converts the data to Unicode and then stores it in the database

BFILE: For storing files in binary format, BFILE can store binary files up to 128MB as operating system files outside the database, the file size cannot exceed the limits of the operating system; The BFILE field only holds a pointer to the binary file, and the BFILE field is read-only. Data cannot be modified from the database

rowid type :

Pseudo-column type, used for the physical address of each record in the Oracle internal save table. Use ROWID to locate the required records within Oracle.

Because the physical address of the data record is actually saved by ROWID, access to the data record through ROWID can get the fastest access speed. For ease of use, Oracle automatically establishes a name for each table called the rowID field, which can be queried, updated, and deleted, and set to use ROWID to access records in the table for the fastest possible operation.

Create a data table

If the user is in their own mode, create must have the CREATE TABLE system permission, and if the table is created in another user mode, then you must have the Create any table system permission

For example: Create a Student profile information sheet students, which includes information such as school number, name, gender, age, department number, class number, and date of filing:

CREATE TABLE Students (

Stuno Number (TEN) is not NULL,

Stuname VARCHAR2 (8),

Sext char (2),

Age int,

Departno VARCHAR2 (2) NOT NULL,

Classno VARCHAR2 (4) NOT NULL,

RegDate date default sysdate);

Note: If the user creates a table in another mode, precede the table name with the name of a schema

You can also create a new table based on a table or view that already exists

For example, use the CREATE TABLE ... as SELECT statement to make a copy of the students table:

CREATE TABLE Students_2 as

SELECT * from students;

After creating the table, Oracle will create the table by copying columns (including data) from the students

Characteristics of a data table

When a table is created in Oracle, the attributes of the table determine how the table is created, how the table is stored on disk, and how the table is ultimately executed when it is created

Next, the 4 parameters commonly used in the detailed table are:

a--storage parameters (storage)

When a user creates a table in Oracle, Oracle allows the user to specify how the table uses storage space on disk. If no storage parameters are specified, the table takes the default storage parameters for the table space to which it belongs.

To set, for a localized managed tablespace, if the specified extents are managed in autoallocate (the other is uniform), you can specify Initial,next (the size of the second extents of the data allocation, which does not work in local, Dictionary), minextents (the minimum number of extents assigned to the data in the table is not available in local, dictionary)

Initial: The first area size, in kilobytes or MB

For example, create a Students_3 data table for storing student profile information (the structure of the table is the same as the structure of the students table above) and allocate the first extents of the table by the initial storage parameter in the storage clause to 256kB:

CREATE TABLE Students_3 (
Stuno Number (TEN) is not NULL,
Stuname VARCHAR2 (8),
Sex char (2),
Age int,
Departno VARCHAR2 (2) NOT NULL,
Classno VARCHAR2 (4) NOT NULL,
RegDate Date Default sysdate)
Tablespace Play Storage (initial 256k)
/

If you want to query the data table storage parameters, you can use Dd--user_tables (initial_extent)

b--Data Block Management parameters

For data tables that do not typically have LOB types, a data block can hold multiple rows of the table, and the data block management parameters that the user can set are:

(For segment Space Manage=auto, you do not need to set the following parameters; for manual)

PCTFREE and pctused

The pctfree is used to specify the minimum amount of space the database must retain, and when the block reaches the limit of the Pctfree parameter, the block is marked as unavailable with a default value of 10

Pctused: The bounds used to set whether the data block is available. That is, in order for the data block to be reused, the storage space already occupied must be less than the pctused setting

Note: generally the sum of these two parameters must be equal to or less than 100, two parameters and 100 difference, the higher the storage efficiency

Generally in the actual application

When using the update operation more, you can set pctfree larger, and pctused smaller, recommended Pctfree20 PCTUSED40

When using more insert operation, you can set pctfree smaller, and pctused larger, recommended Pctfree5 pctused60

For example: Create a Students_4 data table and set its pctfree and pctused to 20 and 40:

CREATE TABLE Studnets_4 (
Stuno Number (TEN) is not NULL,
Stuname VARCHAR2 (8),
Sex char (2),
Age int,
Departno VARCHAR2 (2) NOT NULL,
Classno VARCHAR2 (4) NOT NULL,
RegDate Date Default sysdate)
Tablespace Play Storage (initial 256k)
Pctfree pctused 40
/

C--initrans parameter (default is 1)

This parameter is used to specify the number of concurrent transactions allowed for a block of data. When a transaction accesses a block of data in a table, the transaction saves an entry at the head of the block to identify that the transaction is using the data block. When the transaction ends, its corresponding entry is deleted. For a single block of data, the total number of transactions that access a block of data is determined by the Maxtrans parameter, while the Oracle 11g default supports a maximum of 255 concurrent transactions. But in fact, Maxtrans has been abandoned.

For example: Create a students_5 table and specify 10 transaction entries at the head of the data block:

CREATE TABLE Students_5 (

Stuno Number (TEN) is not NULL,

Stname VARCHAR2 (8),

Sext char (2),

Age int,

Departno VARCHAR2 (2) NOT NULL,

Classno VARCHAR2 (4) NOT NULL,

RegDate Date Default sysdate)

Tablespace Play Storage (initial 256k)

Pctfree pctused 10 Initrans;

can use Dd--user_tables (Ini_trans,max_trans)

Due to the different application characteristics of each table, different Initrans should be set separately for each table, but not too large

d--Redo Log Parameters

By default, when you create a table, you use the logging clause. When considering whether to adopt the nologging clause, the user must consider the gains and risks obtained. When you use the nologging clause, you save storage space for redo log files and reduce the time it takes to create the table. However, if you do not record operations on the table in the Redo log file, you may not be able to recover the lost data with a database recovery operation.

For example, create a students_6, and use the nologging clause when creating the table, so that the user's creation, deletion, modification (DDL) operations on the table are not logged to the redo log file:

CREATE TABLE Students_6 (
Stuno Number (TEN) is not NULL,
Stuname VARCHAR2 (8),
Sex char (2),
Age int,
Departno VARCHAR2 (2) NOT NULL,
Classno VARCHAR2 (4) NOT NULL,
RegDate Date Default sysdate)
Tablespace Play Storage (initial 256k)
Pctfree pctused Initrans nologging
/

e--Cache Parameters

When performing a full table search in Oracle, chunks of data read into the cache are stored in the least recently used end of the LRU list. That is, if a query operation is made and data must be stored in the cache, the data block just read in is swapped out of the cache

When you create a table, you can use the cache clause to change this behavior so that when you perform a full table search in a table that is established with the cache clause, the read-in data block is placed into the most recently used end of the LRU. In this way, the data block belonging to the table will not be swapped out immediately when the LRU algorithm is used to swap the cache block in the database cache, thus improving the query efficiency of the table.

Default is NoCache

For smaller, frequently queried tables, the user specifies the cache clause when creating the table to use the system cache to improve query efficiency on the table

such as: Querying the Students_6 table in Dd--user_tables whether the cache feature is enabled

Select Table_name,cache from User_tables where table_name= ' students_6 ';

Maintain Data Sheets

Ordinary users can only modify the table in their own mode, if you want to modify the table in any mode, the user must have ALTER any table system permissions

Adding and Removing fields

For example: Add a province new field to the Students_6 table

ALTER TABLE Students_6 Add (province varchar2 (10));

Delete the province field in Students_6

ALTER TABLE students_6 drop column province;

If you want to delete multiple columns, you cannot use column, (,) in this way to represent

Description: When you delete a field, the field values for each record in the table are deleted, and the storage space used is freed, and the data for the other columns in the table is not affected. If you want to delete a field from a large table, the delete operation may take a long time because each record needs to be processed.

modifying fields

When changing, be aware that, in the case of data, you can only change the length of the data from low to high, and no data is arbitrary (even change the data type)

For example, change the length of the Departno field in the Students_6 table from 2 to 4:

ALTER TABLE students_6 Modify Departno varchar2 (4);

Note that this modification only works for data that is inserted in the future.

Rename Field

ALTER TABLE students_6 Rename column departno to Deptno;

Renaming a table

Renaming a table is easy, but the impact is very large, so be careful when modifying the name of the table. Although Oracle can automatically update foreign keys, constraint definitions, and table relationships in a data dictionary, it cannot update the database's stored procedures, customer applications, and other objects that depend on the object

such as: Rename Students_6 to Students_7

ALTER TABLE students_6 Rename to students_7;

changing tablespace and Storage parameters

1. Modifying table spaces

For example: Move the Students_6 table from table space play to the tbsp_2 table space

ALTER TABLE students_6 move tablespace tbsp_2;

Moving a table space moves data from one data file on a physical structure to another data file

2. Modifying storage parameters

Mainly refers to modify Pctfree pctused If you change these two parameters, all the data blocks in the table will be affected, regardless of whether the data block is already used

Example: Use ALTER TABLE to reset the pctfree of the Students_6 table pctused

ALTER TABLE students_6 Pctfree pctused 45;

Delete a table

After the data table is created, the user can delete it according to the actual needs. However, it is important to note that in general, users can only delete tables in their own mode, and if you want to delete tables with other schemas, you must have drop any table system permissions

When you delete a table's structure, Oracle typically performs the following actions:

Delete all data in a table

Delete all indexes and triggers associated with the table

If there are views or PL/SQL procedures that depend on the table, these views or the PL/SQL process will be put in an unusable state

Delete the definition of the table from DD

Reclaim the storage space allocated for the table

--there is an optional cascade constraints, when using this parameter, the drop table not only deletes the table, but all views, constraints, or triggers that reference the table are also deleted

For example: Delete table students_5 and all views, constraints, or triggers referencing this table

DROP TABLE students_5 cascade constraints;

In general, it is not completely deleted (only in DD), but instead it is placed in the Recycle Bin and can be restored using flashback flash back

You can query whether the table is in the Recycle Bin by Dd--recyclebin (original_name--table name, object_name)

such as: Recovery of deleted students_5 with Falshback

Flashback table Students_5 to before drop;

Of course, if you want to free up space as soon as you delete it, you can use purge so that the table is completely removed

Modify the state of a table

You cannot set a table to read only until you oracle11g, and you can only place a table space or database as read only

such as: students_5 the table as read-only

ALTER TABLE Students_5 Read only (read write);

Table in read-only state, not DML, but can move table space

Data integrity and constraints

In the definition of the constraint, it can be in the CREATE table, or in the ALTER TABLE, usually the field of the table is defined first, and then by ALTER TABLE to add a constraint to the table as needed

a--non-null constraint

is to restrict the need to provide a value for a column. A null value (NULL) is a nonexistent value, and by default, the column is allowed to be null

such as: Create Books table, require Bookno (book number), ISBN and Publishno (publisher number) cannot be null value

CREATE TABLE Books (
Bookno Number (4) is not NULL,
BookName varchar2 (20),
Author Varchar2 (10),
Saleprice number (9,2),
Publisherno VARCHAR2 (4) NOT NULL,
Publishdate date,
ISBN VARCHAR2 (+) NOT NULL)
/

At this point, you can use

ALTER TABLE books modify bookname not null;

If, when not NULL is modified, the column data already exists, the modification fails.

b--PRIMARY KEY constraints

Used to uniquely identify each row of records in a table. In a table, there is a maximum of one primary KEY constraint, and a PRIMARY key constraint can consist of either a single column or a two or more two columns (a federated primary key)

A PRIMARY KEY constraint has a non-null constraint at the same time.

If a primary key constraint is made up of a column , the primary key constraint becomes a row-level constraint . If a PRIMARY key constraint consists of two or more than two columns , the primary key is called a table-level constraint

You can use the keyword constraints in the CREATE TABLE ... primary key

For example, CREATE table books_1 and define row-level primary key constraints for the table BOOK_PK (primary key column Bookno):

CREATE TABLE Books_1 (
Bookno Number (4) is not NULL,
BookName varchar2 (20),
Author Varchar2 (10),
Saleprice number (9,2),
Publisherno VARCHAR2 (4) NOT NULL,
Publishdate date,
ISBN VARCHAR2 (+) NOT NULL,
Constraints BOOK_PK primary KEY (Bookno))
/

If the user does not specify a primary key when creating the table, you can use the ALTER TABLE ADD constraint ... primary key

such as: use ALTER TABLE. Add statement adds a PRIMARY KEY constraint for books

ALTER TABLE Books

Add constraints BOOKS_PK primary KEY (Bookno);

Here, if you specify a constraint name, you must add constraints, and if you use the system auto-assigned name, you do not need the keyword

For example: In the CREATE TABLE, Bookno number (4) Primary key

Same on ALTER TABLE add primary KEY (Bookno)

Can add the system auto-named constraint name

Note: If a PRIMARY KEY constraint already exists in the table, then when the view adds a primary key constraint to the table, the system generates an error message (even if the different columns are incremented). Similarly, adding a PRIMARY KEY constraint will fail if you add a primary key to the table, the table already has data, and the primary key column has the same value or null.

To delete a PRIMARY KEY constraint:

ALTER TABLE books_1 drop constraints book_pk;

Also, increase the column of the primary key according to the actual situation

c--Uniqueness Constraints

Uniqueness, emphasizing that the column in which it is located does not allow the same value. Is the type of column that resolves a value that can be empty and cannot be duplicated

Such as: Create a membership table members, and ask for the table's QQ column to define a Uniqueness constraint:

CREATE TABLE members (
Memno Number (4) is not NULL,
MemName varchar2 () NOT NULL,
Phone varchar2 (20),
Email VARCHAR2 (30),
QQ varchar2 (constraints) Qq_uk unique,
Provcode VARCHAR2 (2) NOT NULL,
Occucode VARCHAR2 (2) NOT NULL,
Indate Date Default Sysdate,
constraint MEM_PK primary KEY (Memno))
/

To this, I found a magical phenomenon, constraint and constraints the same use, Baidu said that PL/SQL automatically generated by the file is not with S

Adding a unique column has a value, it is not allowed to repeat, but you can insert multiple null values, that is, the null value of the column can be duplicated, so to prevent this, you can add a NOT NULL constraint on the column. So the constraint on this column is basically the equivalent of primary key.

d--FOREIGN KEY constraint

　　A generic foreign KEY constraint is associated with two tables (there is also a case where the same table is self-connected). The reference column of the current table (foreign key table) must be the same as the value of the referenced table (primary KEY constraint or UNIQUE constraint), and if the column data of the referenced table is deleted, the data cannot appear in the reference table, and if the referenced table stores data that will be deleted in the referenced table, the delete operation will fail

Example: establishing foreign KEY constraints associated with EMOLOYEES_TEMP and departments tables

ALTER TABLE Employees_temp
Add constraint TEMP_DEPARTID_FK
Foreign KEY (DEPARTMENT_ID)
References Departments (DEPARTMENT_ID)
/

Of course, if the foreign key column in the Foreign key table is the same as the referenced column column name of the referenced table, you can omit the column name after references

When you define a FOREIGN key constraint, you can use the keyword on to determine the reference behavior:

No action: When you delete data from referenced columns in the referenced table, the FOREIGN KEY constraint will be violated, the operation will be disabled, the default

Set NULL: When the data for the referenced column of the referenced table is deleted, foreign key columns are set to NULL, and if this keyword works, the foreign key column must support null values

Cascade: When the data of the referenced column in the referenced table is deleted, the corresponding data in the foreign key table is also deleted, which is often referred to as "cascade delete"

For example: In HR mode, create a new table departments_temp, and then the table establishes a FOREIGN key constraint with employees_temp and specifies that the FOREIGN KEY constraint reference type is on DELETE cascade

ALTER TABLE Employees_temp

Add Constraint Temp_departid_fk2

Foreign KEY (DEPARTMENT_ID)

References departments_temp on delete Cascade;

Disabling and activating constraints

The existence of constraints reduces the efficiency of inserting and changing data, and the system must confirm that the data meets the defined constraints. Therefore, in some cases, such as importing large amounts of data from an external data source to a table from SQL * Loader, and knowing beforehand that the data meets the constraints, you can disable these constraints in order to improve execution efficiency.

One: Constraint disable--disable when defined

Such as: Create Student Information table student, and define a check constraint for the Age column (age) for the Disable status (requires 0-120):

CREATE TABLE Student (
Stucode VARCHAR2 (4) NOT NULL,
Stuname VARCHAR2 (Ten) NOT NULL,
Age int constraint age_ck check (age>0 and age<120) disable,
Province Varchar2 (20),
Schoolname VARCHAR2 (50))
/

Second: Disable existing constraints--alter table disable constraint

For example, disable constraints in the Employees_temp table TEMP_DEPARTID_FK

ALTER TABLE employees_temp disable constraint TEMP_DEPARTID_FK;

Note: When you disable a PRIMARY KEY constraint, Oracle removes the unique index of the constraint by default, and when you reactivate the constraint, Oracle will re-establish the unique index, and if you want to preserve the unique index when you delete the constraint, you can use the keyword Keep index when you disable the constraint ( Usually placed behind the constraint name)

If you disable a unique constraint or PRIMARY KEY constraint, you cannot disable a unique or PRIMARY KEY constraint if a foreign key is referencing the column. In this case, you can disable the foreign KEY constraint and then disable the unique or PRIMARY KEY constraint, or use cascade when you disable the unique, PRIMARY KEY constraint, which allows you to cascade disable foreign keys for these columns

Disable is also only temporary, you can use ALTER TABLE to enable constraint ...

--novalidate|validate:novalidate indicates that when the constraint is activated, it does not verify that the data already exists in the table satisfies the constraint, by default it is validate, which checks

When a duplicate data cannot be lifted, change these repeating fields before lifting

　　

oracle-Data Table Objects