Funda 1 > Partition - May 29th

sql> sql> insert into part values(&sno,'&name'); enter value for sno: 1 enter value for name: sa old 1: insert into part values(&sno,'&name') new 1: insert into part values(1,'sa') 1 row created. sql> / enter value for enter value for old 1: insert new 1: insert

sno: 2 name: dg into part values(&sno,'&name') into part values(2,'dg')

1 row created. sql> / enter value for sno: 4365 enter value for name: t old 1: insert into part values(&sno,'&name') new 1: insert into part values(4365,'t') insert into part values(4365,'t') * error at line 1: ora-14400: inserted partition key does not map to any partition sql> / enter value for enter value for old 1: insert new 1: insert

sno: 24 name: er into part values(&sno,'&name') into part values(24,'er')

1 row created. sql> / enter value for enter value for old 1: insert new 1: insert

sno: 29 name: ret into part values(&sno,'&name') into part values(29,'ret')

1 row created. sql> / enter value for enter value for old 1: insert new 1: insert

sno: 13 name: ret into part values(&sno,'&name') into part values(13,'ret')

1 row created. sql> / enter value for sno: 68 enter value for name: hjg old 1: insert into part values(&sno,'&name') new 1: insert into part values(68,'hjg') insert into part values(68,'hjg')

* error at line 1: ora-14400: inserted partition key does not map to any partition sql> -------- we give partition tat is less than 10 and 30 sql> sql> sql> select * from part; sno ---------1 2 24 29 13

name ---------sa dg er ret ret

sql> select * from p1; select * from p1 * error at line 1: ora-00942: table or view does not exist sql> ------ to access a partiton sql> sql> select * from part partition(p1); sno ---------1 2

name ---------sa dg

sql> select * from part partition(p2); sno ---------24 29 13

name ---------er ret ret

sql> --------- to alter the partition in the table sql> sql> alter table part add aprtition p3 values less than (100); alter table part add aprtition p3 values less than (100) * error at line 1: ora-01735: invalid alter table option sql> ed wrote file afiedt.buf 1* alter table part add partition p3 values less than (100) sql> / table altered.

sql> insert into part values(&sno,'&name') 2 ; enter value for sno: 100 enter value for name: sad old 1: insert into part values(&sno,'&name') new 1: insert into part values(100,'sad') insert into part values(100,'sad') * error at line 1: ora-14400: inserted partition key does not map to any partition sql> / enter value for sno: 789 enter value for name: fdhg old 1: insert into part values(&sno,'&name') new 1: insert into part values(789,'fdhg') insert into part values(789,'fdhg') * error at line 1: ora-14400: inserted partition key does not map to any partition sql> / enter value for enter value for old 1: insert new 1: insert

sno: 76 name: hgj into part values(&sno,'&name') into part values(76,'hgj')

1 row created. sql> select * from user_tab_partitions where table_name='part'; table_name com 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 pct_increase freelists freelist_groups logging compress num_rows ---------- ------------ ---------- --------------- ------- -------- ---------blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_anal ---------- ------------ ---------- ---------- ----------- ----------- --------buffer_ glo use ------- --- --part no p1 table_name com 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 pct_increase freelists freelist_groups logging compress num_rows ---------- ------------ ---------- --------------- ------- -------- ---------blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_anal ---------- ------------ ---------- ---------- ----------- ----------- --------buffer_ glo use ------- --- --0 table_name com 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 pct_increase freelists freelist_groups logging compress num_rows ---------- ------------ ---------- --------------- ------- -------- ---------blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_anal ---------- ------------ ---------- ---------- ----------- ----------- --------buffer_ glo use ------- --- --10 table_name com 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 pct_increase freelists freelist_groups logging compress num_rows ---------- ------------ ---------- --------------- ------- -------- ---------blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_anal ---------- ------------ ---------- ---------- ----------- ----------- --------buffer_ glo use ------- --- --2 1 rmantbs 10 table_name com 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 pct_increase freelists freelist_groups logging compress num_rows ---------- ------------ ---------- --------------- ------- -------- ---------blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_anal ---------- ------------ ---------- ---------- ----------- ----------- --------buffer_ glo use ------- --- --40 1 255 65536 1 table_name com 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 pct_increase freelists freelist_groups logging compress num_rows ---------- ------------ ---------- --------------- ------- -------- ---------blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_anal ---------- ------------ ---------- ---------- ----------- ----------- --------buffer_ glo use ------- --- --2147483645 1 1 yes disabled table_name com 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 pct_increase freelists freelist_groups logging compress num_rows ---------- ------------ ---------- --------------- ------- -------- ---------blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_anal ---------- ------------ ---------- ---------- ----------- ----------- --------buffer_ glo use ------- --- --default no

no

sql> desc user_tab_partition error: ora-04043: object user_tab_partition does not exist sql> sql> desc user_tab_partitions name null? ----------------------------------------- -------table_name composite

type ---------------------------varchar2(30) varchar2(3)

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 pct_increase freelists freelist_groups logging compression num_rows blocks empty_blocks avg_space chain_cnt avg_row_len sample_size last_analyzed buffer_pool global_stats user_stats

varchar2(30) number long number number varchar2(30) number number number number number number number number number number number varchar2(7) varchar2(8) number number number number number number number date varchar2(7) varchar2(3) varchar2(3)

sql> select partiton_name,high_value,partition_position,tablespace_name,avg_sapce from user_tab_partitions where table_name='part'; select partiton_name,high_value,partition_position,tablespace_name,avg_sapce from user_tab_partitions where table_name='part' * error at line 1: ora-00904: "avg_sapce": invalid identifier sql> ed wrote file afiedt.buf 1* select partiton_name,high_value,partition_position,tablespace_name,avg_space from user_tab_partitions where table_name='part' sql> / select partiton_name,high_value,partition_position,tablespace_name,avg_space from user_tab_partitions where table_name='part' * error at line 1: ora-00904: "partiton_name": invalid identifier sql> ed wrote file afiedt.buf 1* select partition_name,high_value,partition_position,tablespace_name,avg_space from user_tab_partitions where table_name='part'

sql> / partition_name -----------------------------high_value -------------------------------------------------------------------------------partition_position tablespace_name avg_space ------------------ ------------------------------ ---------p1 10 1 rmantbs p2 30

2 rmantbs

partition_name -----------------------------high_value -------------------------------------------------------------------------------partition_position tablespace_name avg_space ------------------ ------------------------------ ---------p3 100 3 rmantbs sql> sql> ---------- to drop the partition sql> sql> alter table part drop partition p2; table altered. sql> ------- to see the values acooring to partition sql> sql> select * from part partition p2; select * from part partition p2 * error at line 1: ora-00933: sql command not properly ended sql> select * from part partition(p2); select * from part partition(p2) * error at line 1: ora-02149: specified partition does not exist sql>

select * from part partition(p1);

sno ---------1 2

name ---------sa dg

sql> select * from part partition(p3); sno name ---------- ---------76 hgj sql> --------- if we drop the partition the values belongs to the partition also be deleted... sql> select partition_name,high_value from user_tab_partitions where table_name='part'; partition_name -----------------------------high_value -------------------------------------------------------------------------------p1 10 p3 100 sql> spool off