Oracle table creation and related parameters

1. Creating table complete 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]]; Description:? Schema: the schema name in which the table is located (the user name)? Table: List name? Column: Field name? Datatype: What is the data type of the field? Tablespace: The table space name in which the table resides controls the parameters used by the data space:? Pctfree: The amount of space reserved in each block in order to increase the length of the line (expressed as a percentage of the space remaining after the block header is subtracted from the total space), and no new rows are added to the block when the remaining space is less than Pctfree. Pctused: After the block has less space left Pctfree, the block has used a space percentage that must be less than pctused to add new rows to the block. Control concurrency Parameters:? Initrans: The number of pre-allocated transaction items 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 guarantee a minimum of concurrency. When a transaction accesses a block of data in a table, the transaction records a value in the header of the Oracle block that marks the Oracle block that the transaction is using. At the end of the transaction, the corresponding entry is deleted. For example, if Initrans is set to 3, it is guaranteed that at least 3 transactions can make changes to the block at the same time. If necessary, you can also allocate other transaction locations from within the block's free space to allow more transactions to modify the rows within the block concurrently. Maxtrans: Limits the maximum number of transaction items that can be assigned to each block, with a default value of 255. When set, this value restricts the use of space by the transaction location, ensuring that there is enough space within the block for the row or index data to be used. STORAGE: Identifies the storage clause that determines how to differentiate the allocation table i.initial: The size of the initial zone Ii.next: The size of the next area iii.pctincrease: The percentage of space growth for each area in the future iv.minextents: the number of initial extents in the segment v.maxextents: most What is the number of extents that can be extended? LOGGING: The creation of the specified table is recorded in the Redo log file. It also specifies that all subsequent operations on the table will be logged. This is the default setting. Nologging: The creation of the specified table will not be recorded in the Redo log file。? CACHE: Specifies that the block retrieved for the table will be placed on the most recently used end of the LRU list of the buffer cache, even when performing a full table scan. NOCACHE: Specifies that when performing a full table scan, the blocks retrieved for the table will be placed on one end of the buffer cache's LRU list that was not recently used. Case 1? By setting the table's nologging to produce fewer redooracle the database is redo to the action that caused the change, For example, DDL statements, DML statements, which are placed first in redo buffer, and then written to the online log file by the Lger process according to the trigger conditions, if the database is open to archive, but also when the log switch to archive. Every link in such a complete chain can be a bottleneck for performance, so it needs to be noticed by DBAs and database applications. In the following case, when a table is set to nologging mode, a certain insert operation allows Oracle to generate less redo. sql> conn/as sysdbasql> Archive Log List-this time for 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 statsql> insert INTO TJ select * from Dba_objects; --observing the statistic value of redo size sql> rollback;  sql> insert/*+append*/into TJ select * from Dba_objects; --observing the statistic 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;     --observing the statistic value of redo size sql> rollback;  sql> insert/*+append*/into TJ select * from Dba_objects; --Observe the statistical value of the Redo size supplement: Set autotrace command usage: Set Autot[race] {OFF | On | Trace[only]} [Exp[lain]] [stat[istics]]--turn off the trace execution plan and Statistics feature (default off). The sql> set autotrace off;--execution plan and statistics all show sql> set autotrace on;--only the execution plan and statistics are displayed, and the results of the SQL execution are not displayed. Sql> set Autotrace traceonly;--only shows execution plan sql> set autotrace on explain;--only show statistics sql> set autotrace on statistics; supplemental Description: Conversion between archive mode and non-archive mode--1) shut down the database Sql>shutdown immediate--2) boot the database to mount mode sql>startup Mount--3) Change the database to non-archive mode/  Archive mode Sql>alter database Noarchivelog; or Sql>alter database archivelog; --4) Open Database sql>alter; --5) View the status of the database archive mode sql> archive Log list Note: If a ORA-38774 error occurs while closing the archive log, turn off Flash Flashback database mode. Sql> ALTER DATABASE flashback off? Case 2: Creating 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 (Ten) CONSTRAINT St_name not NU LL, pHone VARCHAR2 (one), 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 255PCTFREE pctused 50STORAGE (INITIAL 1024K NEXT 1024K pctincrease 0  Minextents 1 MAXEXTENTS 5) tablespace exampletb2, modify table structure alter tables name Add (column name type);  --Add 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 (cannot add scheme name before table name) ALTER table name RENAME column name to new column name; --Modify the column name? Modify table structure case sql> Alter table scott.student Add (QQ number (10)); --Add the student table to store QQ number sql> Alter table scott.student Modify (QQ No. (12)); --Modify the column named QQ in the student table sql> Alter table scott.student Rename column qq to Qq_num;  --Renaming the column named QQ in the student table qq_numsql> 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 into 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 into 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 into the student table think: Default value settings and modifications for columns in Oracle.         3, the constraint of the table ALTER TABLE name add constraint constraints;       --Add a constraint ALTER TABLE name DROP CONSTRAINT constraint name;      --Delete a constraint ALTER TABLE name ENABLE [validate/novalidate] constraint constraint name; --Enables a constraint that validate/novalidate whether the existing data in the table is checked when the constraint is enabled.      ALTER TABLE name disable constraint constraint name; --Disable a constraint? To modify a table constraint case sql> ALTER TABLE scott.student disable constraint st_sex_ck;--disable 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;--enables ST_SEX_CK constraints, but does not check for 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 Exception data save table or @ G:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ Admin\utlexpt1.sql--specific path can be obtained by searching utlexpt1.sql sql>alter table scott.student enable validate constraint St_sex_ck exceptions                                     into exceptions; --load exception data into exception table sql> select * from Scott.student where rowID in (select row_id from exceptions); --View exception data from the corresponding original table Sql>alter table scott.student drop constraint st_sex_ck; --Delete constraint St_sex_ck