Create an Oracle table and Related Parameters

1. Complete table creation syntax
Create table [schema.] table
(Column datatype [, column datatype]… )
[TABLESPACE tablespace]
[PCTFREE integer]
[PCTUSED integer]
[INITRANS integer]
[MAXTRANS integer]
[STORAGE storage-clause]
[LOGGING | NOLOGGING]
[CACHE | NOCACHE];
Note:
Schema: name of the solution where the table is located (user name)
Table: Table Name
Column: field name
Datatype: Data Type of the field
Tablespace: name of the Tablespace in which the table is located
Parameters used to control data space:
Pctfree: the amount of space reserved in each block to increase the length of the row (expressed as a percentage of the space occupied by the whole space minus the remaining space after the block header). When pctfree is insufficient, no new rows are added to the block.
Pctused: After the remaining space of the block is insufficient, the percentage of space used by the block must be smaller than pctused before a new row can be added to the block.
Control concurrency parameters:
INITRANS: Number of transaction items pre-allocated in the block. The default value of INITRANS for the data segment is 1, and the default value for the index segment is 2, to ensure the lowest degree of concurrency. When a transaction accesses a data block in the table, the transaction records a value in the header of the Oracle block to mark that the transaction is using the oracle block. When the transaction ends, the corresponding entry is deleted. For example, if INITRANS is set to 3, at least three transactions can be modified simultaneously. If necessary, you can also allocate other transaction locations from the free space of the block to allow more transactions to modify the rows in the block concurrently.
MAXTRANS: specifies the maximum number of transaction items that can be allocated to each block. The default value is 255. After setting, this value limits the space used by the transaction location to ensure that there is sufficient space in the block for the row or index data.
STORAGE: identifies the STORAGE clauses that determine how to distribute data to tables.
I. INITIAL: size of the INITIAL Zone
Ii. NEXT: size of the NEXT zone
Iii. PCTINCREASE: Percentage of space growth in each area in the future
Iv. MINEXTENTS: number of initial segments
V. MAXEXTENTS: Maximum number of extended zones
LOGGING: Create a specified table and record it to the redo log file. It also specifies that all subsequent operations on the table will be recorded. This is the default setting.
NOLOGGING: the creation of a specified table is not recorded in the redo log file.
CACHE: specify that even when a full table scan is performed, the blocks retrieved for the table will be placed at the last end of the cache lru list.
NOCACHE: specifies that when a full table scan is performed, the block retrieved for this table will be placed at the end of the LRU list that is not used recently in the buffer cache.
Case 1
You can set NOLOGGING for a table to produce fewer REDO logs.
ORACLE databases REDO the operation records that generate changes, such as DDL statements and DML statements. These operations are first stored in the redo buffer, and then written to the online log file by the LGER process according to the trigger conditions, if archiving is enabled for the database, archiving is required during log switching. Every step in such a complete chain may become a performance bottleneck, so it should be noticed by DBAs and database application personnel.
In the following case, when a table is set to the NOLOGGING mode, a certain number of insert operations can generate less REDO records for oracle.
SQL> conn/as sysdba
SQL> archive log list -- archive Mode
SQL> create table tj as select * from dba_objects where 1 = 2;
SQL> select count (*) from tj;
SQL> select table_name, logging from user_tables where table_name = 'tj ';
-- Observe the logging Attribute Value
SQL> set autotrace on stat
SQL> insert into tj select * from dba_objects; -- observe the statistical value of redo size
SQL> rollback;
SQL> insert/* + append */into tj select * from dba_objects; -- observe the statistical value of redo size
SQL> rollback;
SQL> alter table tj nologging;
SQL> select table_name, logging from user_tables where table_name = 'tj ';
-- Observe the logging Attribute Value
SQL> insert into tj select * from dba_objects; -- observe the statistical value of redo size
SQL> rollback;
SQL> insert/* + append */into tj select * from dba_objects; -- observe the statistical value of redo size

Note: Set the Autotrace command
Usage: set autot [RACE] {OFF | ON | TRACE [ONLY]} [EXP [LAIN] [STAT [ISTICS]
-- Disable the tracking execution plan and statistics function (disabled by default ).
SQL> set autotrace off;
-- The Execution Plan and statistics are displayed.
SQL> set autotrace on;
-- Only the execution plan and statistics are displayed, and the SQL Execution result is not displayed.
SQL> set autotrace traceonly;
-- Display only the execution plan
SQL> set autotrace on explain;
-- Only display statistics
SQL> set autotrace on statistics;
Note: commands for converting archive mode to non-archive Mode
-- 1) shut down the database
SQL> shutdown immediate
-- 2) Start the database to the mount mode.
SQL> startup mount
-- 3) change the database to non-archive/archive mode.
SQL> alter database noarchivelog;
Or
SQL> alter database archivelog;
-- 4) Open the database
SQL> Alter database open;
-- 5) view the status of the database archive Mode
SQL> archive log list
Note: If a ORA-38774 error occurs when you disable archiving logs, disable flash back database mode.
SQL> alter database flashback off
Case 2
Create a basic table
Create tablespace exampletb
Datafile 'e: \ examp01.dbf' reuse;
Create table scott. student
(Id NUMBER (5) CONSTRAINT st_id_pk primary key,
Name VARCHAR2 (10) CONSTRAINT st_name not null,
Phone VARCHAR2 (11 ),
School_time date default sysdate,
Sex CHAR (1 ),
CONSTRAINT st_sex_ck CHECK (sex IN ('F', 'M ')),
CONSTRAINT st_ph_uk UNIQUE (name ))
INITRANS 1 MAXTRANS 255
PCTFREE 20 PCTUSED 50
STORAGE (INITIAL 1024 k next 1024 k pctincrease 0 MINEXTENTS 1 MAXEXTENTS 5)
TABLESPACE exampletb
2. Modify the table structure
Alter table name add (column name type); -- add a new column
Alter table name modify (column name type); -- modify column Definition
Alter table Name drop column name; -- delete column
Rename TABLE name to new table name -- change table name (solution name cannot be added before table name)
Alter table name rename column current COLUMN name TO new COLUMN name; -- Modify COLUMN name
Case study of modifying table structure
SQL> Alter table scott. student add (QQ number (10 ));
-- Add a column to store the QQ number for the student table
SQL> Alter table scott. student modify (QQ number (12 ));
-- Modify the column named QQ in the student table
SQL> Alter table scott. student rename COLUMN QQ to QQ_num;
-- Rename the QQ column in the student table to QQ_num.
SQL> Alter table scott. student drop column QQ_num;
-- Delete the column named QQ_num in the student table
SQL> insert into scott. student (id, name) values (1, 'Lucy ');
-- Insert a record to the student table
SQL> Alter table scott. student modify (sex char (1) default 'M ');
-- Modify the definition of the sex column
SQL> insert into scott. student (id, name) values (2, 'Dell ');
-- Insert a record to the student table
SQL> Alter table scott. student modify (sex char (1) default null );
-- Modify the definition of the sex column
SQL> insert into scott. student (id, name) values (3, 'Mary ');
-- Insert a record to the student table
Thinking: Set and modify the default values of columns in oracle.
3. Table Constraints
Alter table name add constraint; -- add a constraint
Alter table Name drop constraint name; -- delete a constraint
Alter table name enable [validate/novalidate] constraint name;
-- Enable a constraint. validate/novalidate indicates whether to check the original data in the table when the constraint is enabled.
Alter table name disable constraint name; -- disable a constraint
Case study of modifying table Constraints
SQL> Alter table scott. student disable constraint st_sex_ck;
-- Disable the st_sex_ck Constraint
SQL> insert into scott. student (id, name, sex) values (4, 'lily', 'n ');
SQL> Alter table scott. student enable novalidate constraint st_sex_ck;
-- Enable the st_sex_ck constraint, but do not check existing data.
SQL> select * from scott. student;
SQL> insert into scott. student (id, name, sex) values (5, 'mark', 'n ');
SQL> @ $ ORACLE_HOME/rdbms/admin/utlexpt1. SQL -- create an exception data storage table
Or
@ G: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ RDBMS \ ADMIN \ utlexpt1. SQL
-- The specific path can be obtained by searching utlexpt1. SQL
SQL> alter table scott. student enable validate constraint st_sex_ck exceptions into exceptions; -- load abnormal data into the exception table
SQL> select * from scott. student where rowid in (select row_id from exceptions );
-- View abnormal data in the corresponding original table
SQL> Alter table scott. student drop constraint st_sex_ck; -- delete the constraint st_sex_ck

Common configuration parameters and environment variables for installing Oracle in Linux