Rename operations in partitions and subpartitions are as easy as renaming tables we just need to take care of syntax. Rename Partitions : We can see in above steps I have renamed Y_2013 partition of transaction table to Y_1_2013. Rename Subpartition : Same way we can rename a subpartition too. Let us see how. We […]
Monthly Archives: July 2019
Exchange Partition in Oracle
Exchanging Partitions is something new. And it provides a huge benefits. By exchanging partition you can actually get data quickly in or out of partitioned table. The data that resides in the partition is exchanged with given non partitioned or partitioned table without deletion and insertion operation. Things to be taken care of while performing […]
Difference Between Local Listener and Remote Listener
LOCAL_LISTENER is a listener that is running on the same machine as this instance. REMOTE_LISTENER is a listener that is not running on the same machine as this instance. Local Listener and Remote Listener concept comes with ORACLE RAC where a single database has multiple instances. Consider an example of 2 Node RAC Database which […]
Modify Non-Partitioned Table to Partitioned
After seeing many articles about types or partitions , benefits of partitions , different operation on partitions , the one question which will surely arise in DBA’s brain is what about my existing table. I am not going to drop my existing partition and create new partitioned table and insert records to those partitioned table. […]
Truncate Partition Operation
Just like we do truncate operation on table , we can truncate partition too. Here we will see 3 types of truncate operations. 1.Truncate Single Partition 2.Truncate Subpartition 3.Truncate Multiple Partitions together. This post will made your DBA maintenance activity easier as all truncate operations we will see. Let’s move to the first one topic. […]
Coalesce Partition and Subpartition
As we saw in previous posts by just defining number of partitions we can create partitions in HASH partition or *-hash subpartitions paritioning type. Now to remove some partition we can use coalesce partition feature of oracle. After we apply coalescing function one hash partition is removed and data are redistributed among the partitions which […]
See ROW Placement in Partitions
We have seen creation of partitions , its benefits , different methods etc. Today we will see method to check the records we are inserting in the partitioning table are placed properly in intended partitions. Let’s create one table using range partitioning method. Let us insert some random records in table. Now lets query the […]
Drop Table Partition with Index Maintenance
We can simply add and drop partitions from table as we are dropping and creating a new table. While dropping and adding table partition , we need to take care of the index too. In previous post we saw simply removing partitions makes index unusable. For Reference Drop Table Partition without Index Maintenance and SKIP_UNUSABLE_INDEXES […]
Drop Table Partition without Index Maintenance and SKIP_UNUSABLE_INDEXES parameter
In this article we will see how we can drop the PARTITION without any index maintenance at run time and doing it later. I have following partitions available for my table TRANSACTIONS_NEW. I have one index on it on date_of_transaction column : Let us drop partition Y_2014 Let us check index status : We can […]
Dropping Table and Index Partition
Dropping Table partition or Index partition is a part of routine maintenance job of DBA. When to drop or add partition ? Well, it depends on the requirement . For instance , in range partitioned tables whenever new range comes in the picture we need to add new partition for the table if we have […]