Create an Oracle table and Related Parameters

1. 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: what is the solution name (user name) of the table )? Table: Table Name? Column: field name? Datatype: Data Type of the field? Tablespace: the name of the Tablespace in which the table is located controls the parameters used by the 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, do not add new rows 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 concurrently modify the rows in the block .? 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: The identifier determines how the STORAGE clause assigned to a table is classified. INITIAL: size of the INITIAL partition ii. NEXT: the size of the NEXT partition iii. PCTINCREASE: Percentage of space growth in each area in the Future iv. MINEXTENTS: number of initial segments v. MAXEXTENTS: What is the 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 will not be recorded in the redo log file .? CACHE: specify that even when a full table scan is performed, the blocks retrieved for this table will be placed at the last end of the cache lru list .? NOCACHE: 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? By setting the NOLOGGING of a table to generate fewer REDOORACLE databases, REDO will generate changed operation records, such as DDL statements and DML statements. These operations will first be placed in the redo buffer, the LGER process then writes online log files 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 sysdbaSQL> archive log list -- 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; -- observe the statistical value of redo size SQL> rollback; SQL> insert/* + append */into tj select * from dba_objects; -- Observe redo siz 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 redo size statistical value SQL> rollback; SQL> insert/* + append */into tj select * from dba_objects; -- observe the statistical value of redo size. Supplement: Set 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; -- SQL> set autotrace on is displayed for execution plans and statistics. -- only execution plans and statistics are displayed, and SQL Execution results are not displayed. SQL> set autotrace traceonly; -- display only execution plan SQL> set autotrace on explain; -- display only statistics SQL> set autotrace on statistics; additional instructions: command for converting archive mode to non-archive mode -- 1) Disable Database SQL> shutdown immediate -- 2) enable database to 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 database SQL> Alter database open; -- 5) view the status of database archive mode SQL> archive log list remarks: If a ORA-38774 error occurs when archiving logs are disabled, disable flash back to database Mode. SQL> alter database flashback off? Case 2? 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 N EXT 1024 k pctincrease 0 MINEXTENTS 1 MAXEXTENTS 5) TABLESPACE exampletb2, 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? Modify table Structure case SQL> Alter table scott. student add (QQ number (10); -- add a column for the student table to store the QQ number SQL> Alter table scott. student modify (QQ number (12); -- modify SQL> Alter table scott. student rename column qq to QQ_num; -- rename the qq column in the student table QQ_numSQL> Alter table scott. student drop column QQ_num; -- delete SQL> insert into scott in the student table column named QQ_num. student (id, name) values (1, 'Lucy '); -- insert a record SQL> Alter tab to the student table Le scott. student modify (sex char (1) default 'M'); -- modify the sex column definition SQL> insert into scott. student (id, name) values (2, 'Dell '); -- insert a record SQL> Alter table scott to the student table. student modify (sex char (1) default null); -- modify the definition SQL of the sex column> insert into scott. student (id, name) values (3, 'Mary '); -- insert a record into the student table. Think about setting and modifying the default values of columns in oracle. 3. table constraint 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? Modify table constraints case 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 the 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 abnormal data storage table or @ G: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ RDBMS \ ADMIN \ utlexpt1. SQL -- you can search utlexpt1. SQL to obtain SQL> alter table scott. student enable validate constraint st_sex_ck exceptions into exceptions; -- load the exception data into the exception table SQL> select * from scott. student where rowid in (select row_id from exceptions); -- view the exception data in the corresponding original table SQL> Alter table scott. student drop constraint st_sex_ck; -- delete the constraint st_sex_ck