- CREATE TABLE (Create a partition table)
CREATE TABLE BILL_MONTHFEE_ZERO
(
Serv_id Number (20) Not NULL,
BILLING_CYCLE_MONTH NUMBER (6) Not null,
Date_Type Number (1),
ACC_NBR VARCHAR2 (80)
)
Partition by Range (Billing_CYCLE_MOTH)
(Partition P_200407 VALUES LESS THAN (200408)
TableSpace TS_ZIKEN
Storage (Initial 100K Next 100K Mineltnce 1 maxextents unlimited pctincrease 0),
Partition P_200408 Values Less Than (200409)
TableSpace TS_ZIKEN
Storage (Initial 100K Next 100k MineXtents 1 maxextents unlimited pctincrease 0))
;
CREATE INDEX IDX_BILL_MONTHFEE_ZERO_IDX01 On BILL_MONTHFEE_ZERO (BILLING_CYCLE_MONTH)
TABLESPACE TS_ZIKEN_IDX
Storage (Initial 100k Next 100k Mineltnce 1 maxextents unlimited pctincrease 0) NOLOGGING LOCAL
Grant All on Bill_Monthfee_Zero to DXSQ_DEV;
- Increase partition table
ALTER TABLE BILL_MONTHFEE_ZERO ADD Partition P_200409
Values Less Than (200410) TABLESPACE TS_ZIKEN;
- Delete a partition
ALTER TABLE Part_TBL DROP Partition Part_TBL_08;
- Divide a partition into two partitions
ALTER TABLE BILL_MONTHFEE_ZERO SPLIT Partition P_200409 At (200409)
INTO (Partition P_200409_1 TableSpace Ts_ziken,
Partition P_200409_2 TableSpace Ts_ziken_idx);
- Merged partition
ALTER
TABLE
BILL_MONTHFEE_ZERO
Merge
Partitions P_200408
,
P_200409
INTO
Partition
P_all
- Rennamed partition
ALTER TABLE BILL_MONTHFEE_ZERO RENAME Partition P_200408 To P_Fee_200408
- Reform the partition space
ALTER TABLE BILL_MONTHFEE_ZERO MOVE Partition P_200409TABLESPACE TS_ZIKEN_01 NOLOGGING
- Query specific partitions
SELECT Count (*) from Bill_Monthfee_Zero Partition (p_200407);
--adding data
INSERT INTO BILL_MONTHFEE_ZERO SELECT * FROM BILL_MONTHFEE_ZERO Partition (p_200407)
- Export of partition tables
Userid = scott / tiger @ aaa
Buffer = 102400
Tables = BILL_MONTHFEE: P_200401,
File = E: /EXP_PARA /EXP_DXSQ_TABLES.DMPLOG=E: /EXP_PARA /EXP_DXSQ_TABLES.LOG
A table is added to a partition table as a sub-table example
- Clear data in November.
Alter Table Bill_Monthfee Truncate Partition P_200311;
/
- Prevent operation errors, first back up the original table.
CREATE TABLE TMP_BILL_MONTHFEE_2311 AS SELECT * from TMP_BILL_MONTHFE_200311;
- Add the backup table to the original table, then the record of the backup table is 0
ALTER TABLE BILL_MONTHFEE Exchange Partition P_200311 with Table Tmp_Bill_Monthfee_200311
/
- Rebuilding the index of November.
Alter index idx_bill_monthfee_01 rebuild partition p_200311 nologging
/
Alter index idx_bill_monthfee_acc_id rebuild partition p_200311 NOLogging
/
Alter index IDX_BILL_MONTHFEE_SERV_ID REBUILD Partition P_200311 NOLOGGING
/
Note: Add the table to the partition table, the structure of this table is consistent with the partition table, and the records of this table are gone.
skill:
Delete a field in the table:
ALTER TABLE BILL_MONTHFEE_ZERO SET Unused Column Date_Type;
Add a field: ALTER TABLE BILL_MONTHFEE_ZERO Add Date_Type Number (1);
