Session 01 - Partition Test

View All Public Scripts Login to Run Script

Script Name Session 01 - Partition Test
Visibility Unlisted - anyone with the share link can access
Description Session 01 - Partition Test
Area SQL General
Contributor mani.ramasamy@bofa.com
Created Friday August 28, 2020

Statement 1

create table part_test (ld_dt date, ld_src varchar2(10)) 
PARTITION BY RANGE (ld_dt) INTERVAL( NUMTODSINTERVAL (7, 'DAY')) 
(PARTITION P_FIRST VALUES LESS THAN (TIMESTAMP' 2020-01-01 00:00:00'))

Table created.

Statement 2

insert into part_test values ('09-SEP-2019', 'India')

1 row(s) inserted.

Statement 3

insert into part_test values ('02-JAN-2020', 'Singapore')

1 row(s) inserted.

Statement 4

insert into part_test values ('03-JAN-2020', 'Egypt')

1 row(s) inserted.

Statement 5

insert into part_test values ('05-JAN-2020', 'Singapore')

1 row(s) inserted.

Statement 6

insert into part_test values ('05-JAN-2020', 'Egypt')

1 row(s) inserted.

Statement 7

insert into part_test values ('08-JAN-2020', 'USA')

1 row(s) inserted.

Statement 8

insert into part_test values ('09-JAN-2020', 'USA')

1 row(s) inserted.

Statement 9

insert into part_test values ('09-JAN-2020', 'Mexico')

1 row(s) inserted.

Statement 10

insert into part_test values ('09-JAN-2020', 'USA')

1 row(s) inserted.

Statement 11

insert into part_test values ('09-JAN-2020', 'Mexico')

1 row(s) inserted.

Statement 12

insert into part_test values ('11-JAN-2020', 'Singapore')

1 row(s) inserted.

Statement 13

insert into part_test values ('12-JAN-2020', 'Egypt')

1 row(s) inserted.

Statement 14

insert into part_test values ('13-JAN-2020', 'USA')

1 row(s) inserted.

Statement 15

insert into part_test values ('14-JAN-2020', 'USA')

1 row(s) inserted.

Statement 16

insert into part_test values ('15-JAN-2020', 'Mexico')

1 row(s) inserted.

Statement 17

insert into part_test values ('15-JAN-2020', 'USA')

1 row(s) inserted.

Statement 18

insert into part_test values ('16-JAN-2020', 'Mexico')

1 row(s) inserted.

Statement 19
```
commit
```
```
Statement processed.
```

Statement 20

select * from part_test


LD_DT LD_SRC
09-SEP-19 India
02-JAN-20 Singapore
03-JAN-20 Egypt
05-JAN-20 Singapore
05-JAN-20 Egypt
08-JAN-20 USA
09-JAN-20 USA
09-JAN-20 Mexico
09-JAN-20 USA
09-JAN-20 Mexico
11-JAN-20 Singapore
12-JAN-20 Egypt
13-JAN-20 USA
14-JAN-20 USA
15-JAN-20 Mexico
15-JAN-20 USA
16-JAN-20 Mexico

17 rows selected.

LD_DT	LD_SRC
09-SEP-19	India
02-JAN-20	Singapore
03-JAN-20	Egypt
05-JAN-20	Singapore
05-JAN-20	Egypt
08-JAN-20	USA
09-JAN-20	USA
09-JAN-20	Mexico
09-JAN-20	USA
09-JAN-20	Mexico
11-JAN-20	Singapore
12-JAN-20	Egypt
13-JAN-20	USA
14-JAN-20	USA
15-JAN-20	Mexico
15-JAN-20	USA
16-JAN-20	Mexico

Statement 21

select * from all_tab_partitions where table_name = 'PART_TEST'


TABLE_OWNER 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
SQL_DVLVJEFWKRUKRLCIYBARDKDDW PART_TEST NO P_FIRST 0 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 83 1 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
SQL_DVLVJEFWKRUKRLCIYBARDKDDW PART_TEST NO SYS_P254976 0 TO_DATE(' 2020-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 83 2 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - YES YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED
SQL_DVLVJEFWKRUKRLCIYBARDKDDW PART_TEST NO SYS_P254977 0 TO_DATE(' 2020-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 83 3 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - YES YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED
SQL_DVLVJEFWKRUKRLCIYBARDKDDW PART_TEST NO SYS_P254978 0 TO_DATE(' 2020-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') 83 4 LIVESQL_USERS 10  - 1 255 65536 1048576 1 2147483645 2147483645  -  -  - YES DISABLED  -  -  -  -  -  -  -  -  - DEFAULT DEFAULT DEFAULT NO NO NO  - YES YES ON NO DISABLED  -  -  -  -  -  -  - DISABLED DISABLED

4 rows selected.

Statement 22

select * from part_test


LD_DT LD_SRC
09-SEP-19 India
02-JAN-20 Singapore
03-JAN-20 Egypt
05-JAN-20 Singapore
05-JAN-20 Egypt
08-JAN-20 USA
09-JAN-20 USA
09-JAN-20 Mexico
09-JAN-20 USA
09-JAN-20 Mexico
11-JAN-20 Singapore
12-JAN-20 Egypt
13-JAN-20 USA
14-JAN-20 USA
15-JAN-20 Mexico
15-JAN-20 USA
16-JAN-20 Mexico

17 rows selected.

LD_DT	LD_SRC
09-SEP-19	India
02-JAN-20	Singapore
03-JAN-20	Egypt
05-JAN-20	Singapore
05-JAN-20	Egypt
08-JAN-20	USA
09-JAN-20	USA
09-JAN-20	Mexico
09-JAN-20	USA
09-JAN-20	Mexico
11-JAN-20	Singapore
12-JAN-20	Egypt
13-JAN-20	USA
14-JAN-20	USA
15-JAN-20	Mexico
15-JAN-20	USA
16-JAN-20	Mexico

Statement 23

SELECT * FROM v$version


BANNER BANNER_FULL BANNER_LEGACY CON_ID
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0

BANNER	BANNER_FULL	BANNER_LEGACY	CON_ID
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.5.0.0.0	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	0

Additional Information

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

TABLE_OWNER	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
SQL_DVLVJEFWKRUKRLCIYBARDKDDW	PART_TEST	NO	P_FIRST	TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	83	1	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
SQL_DVLVJEFWKRUKRLCIYBARDKDDW	PART_TEST	NO	SYS_P254976	TO_DATE(' 2020-01-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	83	2	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	YES	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED
SQL_DVLVJEFWKRUKRLCIYBARDKDDW	PART_TEST	NO	SYS_P254977	TO_DATE(' 2020-01-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	83	3	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	YES	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED
SQL_DVLVJEFWKRUKRLCIYBARDKDDW	PART_TEST	NO	SYS_P254978	TO_DATE(' 2020-01-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')	83	4	LIVESQL_USERS	10	-	1	255	65536	1048576	1	2147483645	2147483645	-	-	-	YES	DISABLED	-	-	-	-	-	-	-	-	-	DEFAULT	DEFAULT	DEFAULT	NO	NO	NO	-	YES	YES	ON	NO	DISABLED	-	-	-	-	-	-	-	DISABLED	DISABLED