Statements you should know about ORACLE

The statements you should know in ORACLE are frequently used. We should remember that, however, the human brain is not a computer, and we may regret them when we forget to use them, so we will record them one by one, there will also be traces for future use. 1. modify the TABLE name: [SQL] ALTER TABLE TableName RENAME TO New_TableName; note that after the TABLE name is modified, the original primary key name is not modified, so you need TO recreate the primary key, of course, you can tell me any good way. 2. modify the primary key: [SQL] alter table ocs_instanceconfig_cfg drop constraint partition; alter table ocs_instanceconfig_cfg add constraint primary key (CONFIGTYPE, TENANTID, MODULEID, CATALOGITEM, PARAMNAME); note that, when importing a database using the backup dmp, the association between constraints and indexes may be lost. If you delete an index without adding cascade drop index, the primary key constraint may be deleted, but the index is not deleted. If your data has been backed up and restored like this: [SQL] alter table ocs_instanceconfig_cfg drop constraint pk_ocs_instanceconfig; when you execute this statement, the indexes that come with the primary key may not be deleted, when you create a primary key with the same name, an error is reported for the index. You need to write it like this: [SQL] alter table ocs_instanceconfig_cfg drop constraint pk_ocs_instanceconfig cascade drop index 3. delete A table: [SQL] drop table tableName; 4. delete sequence: [SQL] drop sequence sequence_name; 5. delete An index: [SQL] drop index index_name; 6. TABLE creation: [SQL] CREATE TABLE ocs_instanceconfig_def (CONFIGTYPE NUMBER (10) NOT NULL, TENANTID NUMBER (10) DEFAULT 0 NOT NULL, MODULEID VARCHAR2 (64) NOT NULL, CATALOGITEM VARCHAR2 (128) not null, PARAMNAME VARCHAR2 (128) not null, PARAMVALUE VARCHAR2 (512), REMARK VARCHAR2 (2048), credate date, lastupddate date, CONSTRAINT PK_ocs_instanceconfig_def primary key (CONFIGTYPE, TENANTID, MODULEID, CATALOGITEM, PARAMNAME); 7. sequence creation: [SQL] create sequence sequence_name INCREMENT BY 1 START WITH 1 NOCYCLE CACHE 1000 ORDER; 8. INDEX creation: [SQL] CREATE INDEX index_name on table_name (ApplyTime, OriFileName, CBPID); 9. VIEW creation: [SQL] create view view_name (CONFIGTYPE, TENANTID, MODULEID, CATALOGITEM, PARAMNAME, PARAMVALUE, REMARK, CREDATE, LASTUPDDATE) as select B. CONFIGTYPE, B. TENANTID, B. MODULEID, B. CATALOGITEM, B. PARAMNAME, B. PARAMVALUE, B. REMARK, B. CREDATE, B. lastupddate from table_name B UNION SELECT. CONFIGTYPE,. TENANTID,. MODULEID,. CATALOGITEM,. PARAMNAME,. PARAMVALUE,. REMARK,. CREDATE,. lastupddate from table_name A where (. MODULEID,. CATALOGITEM,. PARAMNAME) not in (select B. MODULEID, B. CATALOGITEM, B. PARAMNAME from table_name B); 10. delete view: [SQL] drop view view_name; 11. modify table fields: [SQL] alter table table_name modify columun_name VARCHAR2 (30); 12. delete table fields: [SQL] alter table table_name drop RESERVE6; 13. add a table field: [SQL] alter table table_name add RESERVE6 VARCHAR2 (64 );