Common Oracle DBA statements 6

########## Managing Tables ############/* char type maxlen = 2000; varchar2 type maxlen = 4000 bytesrowid is an 18-bit 64-digit string (10 bytes 80 bits) consisting of rowids: Object # (object number) -- 32 bits, 6-bit rfile # (relative file number) -- 10 bits, 3-bit block # (block number) -- 22 Bits, 6-bit row # (row number) -- 16 bits, 3-bit 64-hexadecimal: A-Z, A-Z, 0-9,/, + A total of 64 symbols in the dbms_rowid package can provide the interpretation of rowid */select rowid, dbms_rowid.rowid_block_number (rowid), dbms_rowid.rowid_row_number (rowid) from table_name; Create Table Test2 (ID int, lname varchar2 (20) not null, fname varchar2 (20) constraint ck_1 check (fname like 'K % '), empdate date default sysdate) tablespace tablespace_name;

/* Manually allocate partitions. The allocated data file must be the data file in the tablespace where the table is located */alter table user. table_name allocate extent (size 500 k datafile '... ');/* release space not used in the table */alter table table_name deallocate unused; alter table table_name deallocate unused keep 8 K;/* move the tablespace of the non-partition table to the new tablespace, after the tablespace is moved, the index object in the original table will be unavailable and must be rebuilt */alter table user. table_name move tablespace new_tablespace_name; Create index index_name on user. table_name (column_name) tablespace users; Alter index index_name rebuild; drop table table_name [cascade constraints]; alter table user. table_name drop column col_name [cascade constraints checkpoint 1000]; --- drop column/* mark unused columns in the table */alter table user. table_name set unused column comments cascade constraints;/* mark columns not used in the drop table */alter table user. table_name drop unused columns checkpoint 1000;/* When an exception occurs in drop Col, use continue to prevent deduplication of the column */alter table user. table_name drop columns continue checkpoints 1000; select * From dba_tables/dba_objects; ######## managing indexes ##########/* Create Index */example: /* create a general index */create index index_name on table_name (column_name) tablespace tablespace_name;/* Create a bitmap Index */create bitmap index index_name on table_name (column_name1, column_name2) tablespace tablespace_name; /* The index cannot use pctused */create [bitmap] index index_name on table_name (column_name) tablespace tablespace_name pctfree 20 storage (inner 100 K next 100 k ); /* it is best not to perform logs for indexes with large data volumes */create [bitmap] index index_name table_name (column_name1, column_name2) tablespace_name pctfree 20 storage (in1_100 K next 100 k) nologging; /* Create a reverse Index */create index index_name on table_name (column_name) reverse;/* Create a Function Index */create index index_name on table_name (function_name (column_name) tablespace tablespace_name; /* create constraints when creating a table */create table user. table_name (column_name number (7) Constraint constraint_name primary key deferrable using index storage (initial 100 K next 100 k) tablespace tablespace_name, column_name2 varchar2 (25) Constraint constraint_name not null, column_name3 number (7) tablespace tablespace_name;/* the memory space parameters allocated to the creation of Bitmap indexes to accelerate index creation */show parameter create_bit; /* change the index storage parameter */alter index index_name pctfree 30 storage (initial 200 K next 200 k ); /* manually allocate a partition to the Index */alter index index_name allocate extent (size 200 K datafile '$ Oracle/oradata /.. ');/* release space useless in the Index */alter index index_name deallocate unused;/* re-create the Index */alter index index_name rebuild tablespace tablespace_name; /* interchange between common indexes and reverse indexes */alter index index_name rebuild tablespace tablespace_name reverse;/* do not lock the table */alter index index_name rebuild online when re-indexing; /* fragment the Index */alter index index_name coalesce;/* analyze the index, which is actually the process of updating statistics */analyze index index_name validate structure; DESC index_state; drop index index_name; alter index index_name monitoring usage; ----- monitor whether the index is used by alter index index_name nomonitoring usage; ---- cancel monitoring/* view of index information */select * From dba_indexes/dba_ind_columns/dbs_ind_expressions/V $ object_usage; ######### maintaining data integrity ########### alter table table_name drop constraint constraint_name; ---- drop constraint alter table table_name add constraint constraint_name primary key (column_name1, column_name2); ----- create the primary key alter table table_name add constraint constraint_name unique (column_name1, column_name2 ); --- create a unique constraint/* Create a foreign key constraint */alter table table_name add constraint constraint_name foreign key (column_name1) References table_name (column_name1);/* Do not validate old data, only restrict new data [enable/disable: Restrict/not restrict new data; novalidate/validate: old data is not/verified] */alter table table_name add constraint constraint_name check (column_name like 'B %') enable/disable novalidate/validate;/* modify constraints, latency verification. When commit is used, verify */alter table table_name modify constraint constraint_name initially deferred;/* modify constraints and immediately verify */alter table table_name modify constraint constraint_name initially immediate; alter session set constraints = deferred/immediate;/* Drop a primary key table with foreign keys, cascade deletion with cascade constraints parameters */drop table table_name cascade constraints; /* When the truncate foreign key table is set to invalid, then truncate; */truncate table table_name; /* Set invalid constraints */alter table table_name disable constraint constraint_name; alter table table_name enable novalidate constraint constraint_name;/* put data rows with invalid constraints into the exception table, this table records the row numbers of rows that violate the data constraints. Before that, you must create the exceptions table */alter table table_name add constraint constraint_name check (column_name> 15) Enable validate exceptions into exceptions; /* run the script for creating the exceptions table */start $ ORACLE_HOME/rdbms/admin/utlexcpt. SQL;/* Tables or views for obtaining constraints */select * From user_constraints/dba_constraints/dba_cons_columns;