partition exchange

View All Public Scripts Login to Run Script

Script Name partition exchange
Visibility Unlisted - anyone with the share link can access
Description partition exchange script
Contributor k_a
Created Friday June 21, 2024

Statement 1
```
DROP TABLE STG_SRC
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

CREATE TABLE STG_SRC 
( 
    STG_SRC_ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
    NM VARCHAR2(4000), 
    BATCH_DT_ID NUMBER, 
    CONSTRAINT PK_STG_SRC PRIMARY KEY (STG_SRC_ID) 
)

Table created.

Statement 3

ALTER TABLE STG_SRC MODIFY PARTITION BY LIST (BATCH_DT_ID) AUTOMATIC (PARTITION INITIAL_PARTITION VALUES (20240101)) ONLINE UPDATE INDEXES

Table altered.

Statement 4

INSERT INTO STG_SRC (BATCH_DT_ID, NM) VALUES (20240201,'Robert')

1 row(s) inserted.

Statement 5

INSERT INTO STG_SRC (BATCH_DT_ID, NM) VALUES (20240202,'Keith')

1 row(s) inserted.

Statement 6

INSERT INTO STG_SRC (BATCH_DT_ID, NM) VALUES (20240203,'Mike')

1 row(s) inserted.

Statement 7

INSERT INTO STG_SRC (BATCH_DT_ID, NM) VALUES (20240204,'Sean')

1 row(s) inserted.

Statement 8

INSERT INTO STG_SRC (BATCH_DT_ID, NM) VALUES (20240205,'Alex')

1 row(s) inserted.

Statement 9
```
COMMIT
```
```
Statement processed.
```

Statement 10

SELECT * FROM STG_SRC


STG_SRC_ID NM BATCH_DT_ID
1 Robert 20240201
2 Keith 20240202
3 Mike 20240203
4 Sean 20240204
5 Alex 20240205

5 rows selected.

STG_SRC_ID	NM	BATCH_DT_ID
1	Robert	20240201
2	Keith	20240202
3	Mike	20240203
4	Sean	20240204
5	Alex	20240205

Statement 11

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'STG_SRC'


TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED INDEXING READ_ONLY INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE CELLMEMORY INMEMORY_SERVICE INMEMORY_SERVICE_NAME MEMOPTIMIZE_READ MEMOPTIMIZE_WRITE
STG_SRC NO INITIAL_PARTITION 0 20240101 8 1 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED
STG_SRC NO SYS_P614594 0 20240201 8 2 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED
STG_SRC NO SYS_P614595 0 20240202 8 3 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED
STG_SRC NO SYS_P614596 0 20240203 8 4 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED
STG_SRC NO SYS_P614637 0 20240204 8 5 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED
STG_SRC NO SYS_P614638 0 20240205 8 6 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED

6 rows selected.

Statement 12
```
DROP TABLE STG_TGT
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 13

CREATE TABLE STG_TGT 
( 
    STG_TGT_ID NUMBER GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
    NM VARCHAR2(4000), 
    BATCH_DT_ID NUMBER, 
    CONSTRAINT PK_STG_TGT PRIMARY KEY (STG_TGT_ID) 
)

Table created.

Statement 14

ALTER TABLE STG_TGT MODIFY PARTITION BY LIST (BATCH_DT_ID) AUTOMATIC (PARTITION INITIAL_PARTITION VALUES (20240101)) ONLINE UPDATE INDEXES

Table altered.

Statement 15
```
SELECT * FROM STG_TGT
```
```
no data found
```

Statement 16

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'STG_TGT'


TABLE_NAME COMPOSITE PARTITION_NAME SUBPARTITION_COUNT HIGH_VALUE HIGH_VALUE_LENGTH PARTITION_POSITION TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT MAX_SIZE PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING COMPRESSION COMPRESS_FOR NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN SAMPLE_SIZE LAST_ANALYZED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE GLOBAL_STATS USER_STATS IS_NESTED PARENT_TABLE_PARTITION INTERVAL SEGMENT_CREATED INDEXING READ_ONLY INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE CELLMEMORY INMEMORY_SERVICE INMEMORY_SERVICE_NAME MEMOPTIMIZE_READ MEMOPTIMIZE_WRITE
STG_TGT NO INITIAL_PARTITION 0 20240101 8 1 LIVESQL_USERS 10  - 1 255  -  -  -  -  -  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - NO NO ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED

Statement 17
```
DROP TABLE STG_SRC_TMP
```
```
ORA-00942: table or view does not exist 
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 18

CREATE TABLE STG_SRC_TMP FOR EXCHANGE WITH TABLE STG_SRC

Table created.

Statement 19

ALTER TABLE STG_SRC EXCHANGE PARTITION FOR (20240201) WITH TABLE STG_SRC_TMP INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES

Table altered.

Statement 20

SELECT * FROM STG_SRC WHERE BATCH_DT_ID = 20240201; -- 0 RECORDS as expected 
SELECT * FROM STG_SRC_TMP WHERE BATCH_DT_ID = 20240201; -- 1 RECORDS as expected 
 
alter table STG_TGT lock partition for (20240201) in share mode; -- Error ORA-01735: invalid ALTER TABLE option 
lock table STG_TGT partition for ( 20240201 ) in exclusive mode; -- Error Invalid statement 
 
 
ALTER TABLE STG_TGT EXCHANGE PARTITION FOR (20240201) WITH TABLE STG_SRC_TMP INCLUDING INDEXES WITHOUT VALIDATION UPDATE GLOBAL INDEXES

ORA-00933: SQL command not properly ended

More Details: https://docs.oracle.com/error-help/db/ora-00933

Statement 21

ORA-14702: The partition number is invalid or out-of-range   -- Error

Invalid statement

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database

TABLE_NAME	COMPOSITE	PARTITION_NAME	HIGH_VALUE	HIGH_VALUE_LENGTH	PARTITION_POSITION	TABLESPACE_NAME	PCT_FREE	PCT_USED	INI_TRANS	MAX_TRANS	INITIAL_EXTENT	NEXT_EXTENT	MIN_EXTENT	MAX_EXTENT	MAX_SIZE	PCT_INCREASE	FREELISTS	FREELIST_GROUPS	LOGGING	COMPRESSION	COMPRESS_FOR	NUM_ROWS	BLOCKS	EMPTY_BLOCKS	AVG_SPACE	CHAIN_CNT	AVG_ROW_LEN	SAMPLE_SIZE	LAST_ANALYZED	BUFFER_POOL	FLASH_CACHE	CELL_FLASH_CACHE	GLOBAL_STATS	USER_STATS	IS_NESTED	PARENT_TABLE_PARTITION	INTERVAL	SEGMENT_CREATED	INDEXING	READ_ONLY	INMEMORY	INMEMORY_PRIORITY	INMEMORY_DISTRIBUTE	INMEMORY_COMPRESSION	INMEMORY_DUPLICATE	CELLMEMORY	INMEMORY_SERVICE	INMEMORY_SERVICE_NAME	MEMOPTIMIZE_READ	MEMOPTIMIZE_WRITE
STG_SRC	NO	INITIAL_PARTITION	20240101	8	1	LIVESQL_USERS	10	-	1	255	-	-	-	-	-	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	NO	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED
STG_SRC	NO	SYS_P614594	20240201	8	2	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED
STG_SRC	NO	SYS_P614595	20240202	8	3	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED
STG_SRC	NO	SYS_P614596	20240203	8	4	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED
STG_SRC	NO	SYS_P614637	20240204	8	5	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED
STG_SRC	NO	SYS_P614638	20240205	8	6	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	NO	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED