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.
View 3 Replies
ADVERTISEMENT
Oct 28, 2010
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.
View 2 Replies
View Related
Apr 4, 2011
got this Errormessage "ORA-12713: Character data loss in NCHAR/CHAR conversion" during a rman backupjob! How can I fixed it?
View 3 Replies
View Related
Aug 29, 2012
To remove the last comma end of string and load the Clob data into table. create table test(name clob)
View 2 Replies
View Related
Jul 22, 2010
I want to move data from one partition to another partition in a same table.
Ex: Table A has Partitions P1 and P2. I want to move data from P1 to P2.
Is this is possible?
View 3 Replies
View Related
Nov 16, 2010
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 ..
View 2 Replies
View Related
Oct 11, 2010
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.
View 2 Replies
View Related
Mar 25, 2011
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?
View 11 Replies
View Related
Mar 11, 2013
IMPORT PARTITION TABLE Through Data Pump.
I have a table with RANGE PARTITION. I wanted to import this into another server with the same partitions.
But when I imported the table, The table created with the Partition but the data is not inserted in partition wise.
But I could see the Entire table's ROW COUNT.
View 5 Replies
View Related
Jun 26, 2010
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)
View 3 Replies
View Related
Mar 28, 2013
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 .
View 9 Replies
View Related
Jun 19, 2012
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.
View 1 Replies
View Related
Jan 2, 2013
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.
View 8 Replies
View Related
Jun 14, 2011
i want to create a new partition for version 2
existing table is as below
create table test
(
name varchar2(100),
version NUMBER(12)
)
[Code]....
View 15 Replies
View Related
Jul 10, 2012
I have two tables in which one is partitioned table with the following details.
CREATE TABLE "SCOTT"."TBL_MITTAL"
("ACCOUNT_NAME" VARCHAR2(50 BYTE),
"BILL_NO" VARCHAR2(50 BYTE),
"BILL_DATE" VARCHAR2(50 BYTE),
"CLI" VARCHAR2(50 BYTE),
"ANI" VARCHAR2(50 BYTE),
[code].....
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.
ACCOUNT_NAMEBILL_NOBILL_DATECLIANICHARGE_START_DATEDURFROM_LOCATIONTO_LOCATIONINVOICE_IDCIRCLE
10000010357423128271095119301-Feb-111723006000931488182328-JAN-11 11.30.54.000000000 AM59.04CHANDIGARHJAIPUR271095119
10000011844187128348720198715-Jun-121409470011825531896615-MAY-12 09.10.36.000000000 AM28.03CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531927115-MAY-12 09.10.41.000000000 AM38.32CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531933015-MAY-12 09.10.46.000000000 AM28.81CHANDIGARHBANTWAL348720198
10000011844187128348720198715-Jun-121409470011825531930215-MAY-12 09.10.53.000000000 AM28.96CHANDIGARHBANTWAL348720198
[code].....
I also tried to upload the same data using sqlldr.
sqlldr log file contents is as follows:
Total logical records skipped: 0
Total logical records read: 1857532
Total logical records rejected: 801092
Total logical records discarded: 37
[code].....
So some sqlldr bad file contents is as follows.
100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,
[code].....
View 2 Replies
View Related
Oct 23, 2010
Can try this approach to recover:[URL]...
View 2 Replies
View Related
Jun 24, 2010
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.
View 8 Replies
View Related
Jun 8, 2011
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')
View 3 Replies
View Related
Mar 25, 2011
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?
View 2 Replies
View Related
Jun 10, 2013
I do have a query which gives me two rows of data.(This might be more than two in other cases)
The data I get is formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
European Patent EP 101 Mokilosu
What I want to get should be formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
Mokilosu
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.
The code I do use to get this data:
SELECT
TABLECOUNTRY.COUNTRYDESCRIPTION "COUNTRY_DESCRIPTION"
,TABLECOUNTRY.COUNTRYCODE "COUNTRY_CODE"
,OWNER.NAME "APPLICANT"
,CASEEVENT.EVENTNUMBER "EVENT_NUMBER"
,TO_CHAR(CASEEVENT.EVENTDATE,'DD.MM.YYYY') "EVENT_DATE"
[code]....
So what to modify in the query to get these columns of the second row blank?
View 17 Replies
View Related
Mar 8, 2011
remove duplicate data in oracle-sql. can u post me the sql query to remove duplicate data with example.
View 1 Replies
View Related
Aug 3, 2012
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.
View 2 Replies
View Related
Aug 11, 2010
i loaded the data from csv file. and i used FIELDS TERMINATED BY x'09' values.but the end of the value having white space within it..
ZIP_CODE
"33004[] like box
"33004[]"
"33004[]"
"33004[]"
"33004[]"
how can i remove the white space when i load the data.
View 1 Replies
View Related
Aug 14, 2012
I have a little problem, using SQLPLUS I use "set linesize 300", so in the END of output file file. I got spaces after info ! I mean
select client_id || ';' || date || ';' || amount info
from table
1;FEB-2012;5000........
2;DIC-2011;10000.......
500;MAR-2012;300000.... (. = spaces)
How I remove spaces after Data ?
View 5 Replies
View Related
May 5, 2011
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))
it will raise error ora-14020
View 4 Replies
View Related
Feb 21, 2013
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.
View 5 Replies
View Related
May 28, 2010
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.
View 6 Replies
View Related
Oct 17, 2011
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.
View 3 Replies
View Related
Jul 11, 2011
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.
View 2 Replies
View Related
May 8, 2013
how to remove duplicate rows from table?
View 6 Replies
View Related
