Oracle stores new records to other disks through Table Partitioning

Oracle needs to store new records to other disks through Table Partitioning: the original oracle database data files are stored on the d disk, but there is not much space left on the d disk. We recommend that you switch to the E disk as the boss. When I last created an oracle data file for the tablespace, I found that the large table cannot be created, so it has not been processed yet. Solution: I recently read some oracle data on the Internet and thought of an idea. I verified it on the database at home. Change the log table to a partition table, and save the newly added log data to the new partition. The new partition can be stored on another disk. Theoretical Basis 1. different tablespaces can be easily stored on different Disks without the problem of large tables. data of different partitions in a partitioned table can be stored in different tablespaces. you can use table redefinition to convert an existing table to a partitioned table. 4. for a user who queries a partitioned table, no additional operations (such as partitions) are required. For details, refer to the previous two articles. General steps 1. through online redefinition, the log table is converted into a partition table 2. when creating a tablespace to a new disk, the user still belongs to the original tablespace (which can be queried at that time) 3. add a partition to the log table, and the data file of the new partition is no longer in the new tablespace. Perform the following steps in SQLPLUS: 1. add a primary key to the Mutual table (consistent with the following LOGSMSHALL_MUTUAL_NEW definition) (because the table must have a primary key) (this step is not required and may be required under 9i, however, I first executed the ALTER TABLE LOGSMSHALL_MUTUAL ADD constraint PK_MUTUAL primary key (id) during database verification on Database 136. enable the table to allow redefinition of EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE (USER, 'logsmshall _ mutual', DBMS_REDEFINITION.CONS_USE_PK); 3. create table LOGSMSHALL_MUTUAL_NEW (id number (20) primary key not null, "SESSION ID "VARCHAR2 (28)," REQUESTID "VARCHAR2 (32)," USERTELNO "VARCHAR2 (16)," USERCITYNAME "VARCHAR2 (8)," USERBRANDNAME "VARCHAR2 (16 ), "USERCONTENT" VARCHAR2 (512), "RECEIVETIME" timestamp default sysdate, "PROCESSTYPE" VARCHAR2 (16), "PROCESSNODENAME" VARCHAR2 (32), "RECNODENAME" VARCHAR2 (32 ), "RECTIME" TIMESTAMP www.2cto.com, "RECTYPE" VARCHAR2 (16) DEFAULT 'notrec', "RECRESULT" CHAR (1) DEFAULT '1', "RECRESULTCODE" VARCHAR2 (32), "RECRESULTDESC" VARCHAR2 (256), "PLATFORMHANDLENODENAME" VARCHAR2 (32), "PLATFORMHANDLETIME" timestamp default sysdate, "PLATFORMHANDLERESULT" CHAR (1) DEFAULT '2', "PLATFORMHANDLERESULTCODE" VARCHAR2 (32), "PLATFORMHANDLERESULTDESC" VARCHAR2 (1024), "REPLYCONTENT" VARCHAR2 (1024 ), "REPLYINDEXID" INTEGER, "SENDSMSNODENAME" VARCHAR2 (32), "SENDSMSTIME" TIMEST Amp default sysdate, "SENDSMSRESULT" CHAR (1) DEFAULT '1', "SENDSMSRESULTCODE" VARCHAR2 (32), "SENDSMSRESULTDESC" VARCHAR2 (256), "COSTSECONDS" INTEGER, "NLIBIZNAME" VARCHAR2 (32), "BIZNAME" VARCHAR2 (128), "OPERATIONNAME" VARCHAR2 (16), "PARMSKEYANDVALUE" VARCHAR2 (128), "CHECKFLAG" CHAR (1) DEFAULT '0', "CHECKTIME" timestamp default sysdate) partition by range (RECEIVETIME) (PARTITION P1 VALUE S less than (TO_DATE ('1970-4-10', 'yyyy-MM-DD '); 4. start table redefinition EXEC DBMS_REDEFINITION.START_REDEF_TABLE (USER, 'logsmshall _ mutual', 'logsmshall _ MUTUAL_NEW ', 'id', DBMS_REDEFINITION.cons_use_rowid); 5. the end table's redefinition EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE (USER, 'logsmshall _ mutual', 'logsmshall _ MUTUAL_NEW '); www.2cto.com the process is automatically completed. apply the DML In the snapshot log to the intermediate table. swaps the names of the original and intermediate tables, including all possible data dictionaries. however, it should be noted that there are no changing constraints, indexes, and trigger names. Some need to be manually modified 7. drop table LOGSMSHALL_MUTUAL_NEW; 6. modify trigger create or replace trigger "TIB_LOGSMSHALL_MUTUAL" before insert on "LOGSMSHALL_MUTUAL" for each row declare INTEGRITY_ERROR EXCEPTION; errno integer; errmsg char (200); dummy integer; FOUND BOOLEAN; BEGIN www.2cto.com -- COLUMN "ID" uses sequence S_LOGSMSHALL_MUTUAL SELECT S_LOGSMSHALL_MUTUAL.NEXTVAL INTO: NEW. id from dual; -- ERRORS HANDLIN G exception when INTEGRITY_ERROR THEN RAISE_APPLICATION_ERROR (ERRNO, ERRMSG); END;/7. create tablespace ECSS_LOG_NEW DATAFILE 'd: \ oracle \ product \ 10.2.0 \ oradata \ ECSS_LOG_NEW_data 'size 1024 m autoextend on next 256 m maxsize unlimited; 8. ADD the partition alter table LOGSMSHALL_MUTUAL add partition P_NEW values less than (TO_DATE ('2017-12-31 ', 'yyyy-MM-DD') to the original TABLE ')); because the data contained in the original partition is smaller than that of January 10, data larger than January 10, will be stored. In the new partition P_NEW, www.2cto.com, verify the LOGSMSHALL_MUTUAL partition SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'logsmshall _ mutual'. Two 9 s are displayed. verify that a piece of data with an insertion date greater than 2012 enters the LOGSMSHALL_MUTUAL table insert into LOGSMSHALL_MUTUAL (ReceiveTime) VALUES (to_date ('2017-4-20 ', 'yyyy-MM-DD'); commit; run three more statements to verify whether the record is inserted into the new partition: select count (*) cn from logsmshall_mutual partition (P1); select count (*) cn from logsmshall_mutual partition (P_NEW); select count (*) cn from logsmshall_mutual; a more detailed document will be prepared later. Author Ajita