SQL & PL/SQL :: How To Remove Partition In A Table Without Data Loss
Jul 27, 2012
We would like to remove the partitions from a particular table. The table in question has 12 partitions. Based on some initial investigation, I've come up with the following options. because the table we going to remove partition will have millions of records so on considering the db downtime we are looking for a alternative way. Is there a better way?
Copy data into another table, drop all partitions, then copy the data back into the original table
Copy data into another table, drop the original table, then rename the new table and rebuild the indexes.
I have a database using character set AL32UTF8. The database contains character strings (VARCHAR2 colums) that may contain both Western European and Eastern European characters (and may be even other kinds of characters such as Cyrillic or Asian).
Suppose a client application has set NLS_LANG character set to WE8ISO8859P1. By this Western European characters will be shown correctly, while Eastern European characters which do not compare with WE8ISO8859P1 will be converted and shown as '?' (question marks) in the client application. If a user of this application fetches a record with Eastern European characters, modifies the record and then rewrites it to the database, the Eastern European characters with be rewritten to the database as question marks, i.e. Eastern European data have been corrupted.
I would like to prevent this by detecting that data were not converted properly during the fetch and then show the record to the user in read-only mode in order to avoid data loss, but I have not been able to detect the conversion error.
The application fetches data through the OCI interface using the "ofetch" function. The error code set by ofetch is the same (i.e. no error) regardless of whether the record contains Eastern European characters or not.
I thought I could manage this by setting the database parameter NLS_NCHAR_CONV_EXCP to TRUE, but this has no effect. Apparently this only deals with operations directly in the database.
I have 2 tables with same no of column and range partition based on date.At the end of month i want to copy the data of one table to another table.Instead of copy the data i want to copy the data of one partition completely to another table partition ..
I have a staging table with 70 million rows and nearly 90 columns. We just want to verify and perform some validations on the data and then move this data to final tables. There staging table has a primary key in it.
We are planning to create the final table in following ways.
1. Create n number of final tables. 2. Create n number of final tables with partitions based on the primary key value in staging table. 3. Create single final table with partition based on the primary key value staging table.
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
I have a table that partitioned into six partitions. each partitions placed in different table space and every two table space placed it on a different hardisk
when I will do query select with the non-partition keys condition, how the search process ? whether the sequence (scan sequentially from partition 1 to partition 6) or partition in a hardisk is accessed at the same time with other partition in other hardisk. ( in the image, partition 1,4 accessed at the same time with partition 2,5 and 3,6)
As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .
Can i alter the table to create partition on non partition table, i have tried and could not create it. Do we have some other means to do it as this is the live table and cannot drop them else will lose the data.
Can I add range sub partition to a hash partition table. Example like this.
CREATE TABLE test ( test_id VARCHAR2(10 ) , test_TYPE VARCHAR2(5) , CREATE_DATE date ) partition by hash (test_id, test_type) Partitions 3 SUBPARTITION BY RANGE (CREATE_DATE);
When Tried, I am getting syntax error as invalid option.
When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error
SQL> insert into tbl_temp select * from tbl_mittal; insert into tbl_temp select * from tbl_mittal * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition
AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data.
I have few tables in Oracle 9i/10g , and they already have data in them. I am trying to migrate the data coming from various source systems into these Oracle tables. There is a chance that after loading I might get some unwanted data into these tables.
How do I remove just that data which I have loaded recently, and do not disturb the original data it already has.
Need to backup those tables and reload the data back if there is any problem, but I am looking at a different approach. I just don't want to change the existing system, as lot of users use the system.
How to find the size pf a partition in a partition table?I guess we need to query views like dba_tab_partitions but I am not very sure. will running dbms_stats.gather_table_stats('schema_name,'table_name,'partition_name')
I think that performance better partition table than non-partition table. How to assure partition table is better than non-partition table at SELECT operation?
I have compare a specific query EXPLAIN PLAN at partition table and non-partition table. both tables data is same. Is it true way or not?
As you can see, I removed the first four columns because the eventkey is the same. In this case, there is only the applicant which is different.So the rest should be blank.
I have a CLOB column in one of my tables (Table1), which stores very large (150MB+) XML files. I have new/another table (Table2) in the DB where I have an XMLType column. I want to take the CLOB data (xml) from table1 and remove some part of that and store the rest into to the XMLType column of Table2.
I want to remove the data inside the XML tags
<Attachments>
very long data goes here... which I don't need, which should be replaced with a single word
</Attachments>
store the CLOB to XMLType column after removing the unwanted data.
I am trying to add partition to table without partition
with following code
ALTER TABLE ACC_LOC1_TAB ADD PARTITION testpart BY RANGE (ALT_AUTHDT) (PARTITION UPTO_2010 values less than (31-mar-2010), PARTITION APR_JUN_10 VALUES less than (30-JUN-2010), PARTITION JUL_SEP_10 VALUES less than (30-SEP-2010), PARTITION OCT_DEC_10 VALUES less than (31-DEC-2010), PARTITION JAN_MAR_11 VALUES less than (31-MAR-2011))
I am using Oracle 11.2.0.1 Oracle Database.I have a table with 10 Million records and it's a Non Partitioned Table.
1) I would like to partition the table (with partition by range ) without creating new table . I should do it in the existing table itself (not sure DBMS_ REDEFINITION is the only option ) (or) can i use alter table ...?
2) Add one partition which will have data for the unspecified range.
i have table with range partition and list sub-partition..can i add one more list sub-partition if it is not possible , i have to drop first sub-partition.
Our application has been installed at customers in North America, Europe and South America for several years, in some cases, over 10 years. At least one of our customers has hundreds of gigabytes of data. We are considering options for cleaning out the old data.
The database runs on a variety of systems (Linux, Windows, Unix) and in several version (Oracle 9, 10, and 11). We need a solution that works in all environments.
Two of our main criteria for a successful solution are that: -It maintains application data referential integrity. Our application makes little use of foreign key constraints, so the cleanup process will apply critical business rules to candidate data to determine if it can be deleted or not. -The operation of the cleanup program does not impact use of the system in production.
For various reasons (license cost, installation issues) the partitioning option is not available to us.
Alternative 1: Flag records for cleanup
This requires adding a 1-character column to each table. That is a one-time operation done during implementation. The procedure applies the business rules and sets the flag according to whether a row is to be deleted or not. Rows marked for deletion can be checked, reset, exported, etc. Finally, a separate process deletes all marked rows.
Advantage of this is that the deletion process will use a full table scan to find the marked records. There is no index navigation, so hopefully less overhead. Disadvantage is that its updating application data which might affect user's perceived system response. There is some undefined concern that locking or other table activity involved with updating the flags could impact users.
Alternative 2: Build a list of keys for data to be deleted
We will build a list table during implementation. The first process examines the application data, applies the deletion rules and writes key information to the list for data that can be deleted. The list can be checked, reset, rebuilt and listed rows can be exported as required. Finally, the cleanup process uses the list to find and delete the data.
Advantage is that it doesn't update the application data as its building the list. Disadvantages are that it that there is some overhead in building and checking the list. The list requires more space than the flags in alternative 1 but we can handle that in various ways. The procedure needs to navigate key structures during the delete step as well as in the list-building phase.
I want to truncate table partition but I'm getting error:
CODEORA-02266: unique/primary keys in table referenced by enabled foreign keys
because of table has a nested table. Currently this column is not in use so I could drop it, but I want to avoid it (table is huge). Is there another posibility to do truncate partitions in this table? ALTER TABLE ... SET UNUSED doesn't resolve the problem.
