PL/SQL :: How To Truncate Data In Table Partitions

Aug 9, 2013

I couldn't either DROP or TRUNCATE the table partitions that were created. Here are the DDLs and DMLs I'm using. 

Create table student(no number(2),name varchar(2)) partition by range(no) (partition 
p1 values less than(10), partition p2 values less than(20), partition p3 values less     
than(30),partition p4 values less than(40)); 
Insert into student values(1,'a');
Insert into student values(11,'b');
Insert into student values(21,'c');
Insert into student values(31,'d'); 

When I do the following query, it returns data.

SELECT * FROM STUDENT PARTITION(p1); 

But, when I try to perform any of the following queries, it says invalid partition name. 

ALTER TABLE STUDENT DROP PARTITION p4;
ALTER TABLE STUDENT TRUNCATE PARTITION p3;

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 9 Replies


ADVERTISEMENT

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

SQL & PL/SQL :: Move Data From Old Partitions To Corresponding New Partitions?

Jul 1, 2010

Initially 2008 to 2010 and 2009 to 2011 partitions are created, After that two more new partitions are created 2008 to 2009 and 2009 to 2010.

How can we move data from old partitions to corresponding new partitions.

View 9 Replies View Related

Backup & Recovery :: Recover Truncate Table Data?

May 24, 2012

Our client has this scenario:May 23th 4:00 PM a user truncate a critical table. The customer need to recover the data before truncate.

The recover materials list like this:
1. A cold backup of this database at May 1th.
2. The archive log except May 1th ~ May 15th.
3. Every day exp at 00:30 and 12:00.

Is there any way to recover the data before truncate table with these material?

View 3 Replies View Related

Data Guard :: Truncate Table Effect On Physical Standby

Nov 20, 2009

Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.

View 6 Replies View Related

Server Utilities :: Truncate Table And Load It With Data Present In File

Jul 17, 2010

My requirement is to to truncate the table and load it with the data present in file. In the control file, I used the "TRUNCATE" command as well.In case, if the file has some invalid data and sqlldr fails, my existing data will be lost. Is there any option in which the sqlldr does not TRUNCATE the table in case of a failure.

View 6 Replies View Related

SQL & PL/SQL :: Exchange Partitions Between Actual Table From It's Corresponding Staging Table

Nov 26, 2012

We have a table with interval partition. This table is accessed very frequently. We are suppose to exchange partitions between this actual table from it's corresponding staging table.

In order to keep the newly created partitions empty, is there a way to restrict other applications from using it before we push data from staging table to it's actual table.

View 4 Replies View Related

SQL & PL/SQL :: Is Truncate Data Recovered

Jan 19, 2012

Is it possible to recover a table which is Truncated

View 13 Replies View Related

SQL & PL/SQL :: TRUNCATE Can Rollback Data?

Jun 4, 2013

I have found one interview question no.7 @ [URL] According to that it can be rollbackQuote:The truncate command is a DDL operation and just moves the high water mark and produces few rollback data.

But, I read That is not true in the case of oracle but it is true in the case of SQL Server.

View 7 Replies View Related

Create Table As With Partitions?

Jan 8, 2011

If I try create table from the following syntax

create table a as select * from table b;

Then I could get only base table structure alone, I would like to get partition syntax as well.

View 1 Replies View Related

PL/SQL :: Create Partitions On Table

Sep 13, 2012

My developer came with a requirement of creating partitions on a table which has 40 million records. His exact requirement is to create as many as partitions in such a way that 1 partition should not exceed 5k-10k records and these records should be inserted/updated on the same date (i.e. using a column as source_timestamp field). How to accomplish this?

View 2 Replies View Related

Table DDL Extraction For Mentioned Partitions

Nov 10, 2010

I have tables in production which has got huge no of partitions(say more than 100), but I would like to extract table definiation along with mentioned few partitions(say 10 partitions) alone. How to do that, which way is the best to extract DDL with right format.

because when I use metadata package the format for the extraction is not good, is there a way to extract table definition with mentioned partition names.

View 2 Replies View Related

SQL & PL/SQL :: Partition Existed Table Into 3 Partitions

Mar 9, 2010

How can we partition the existed table.

The table is like this

CREATE TABLE my_table (
id NUMBER,
description VARCHAR2(50)
);

[Code]...

I want to partition this table into 3 partitions.

The first partition should contain the values less than 3.

The Second partition should contain the values less than 5.

The third partition should contain the values less than 6.

View 16 Replies View Related

Swap Multiple Partitions Into A Table?

Sep 28, 2012

We are using partition exchnage to swap individual partitions into table which then backed up.

This being done one partition at a time.

Is it possible to swap several partitions of a tabel in one go.

using Oracle 11.2.0.3

partioned by date, one partition of reach day.

Is it possible say to move the last 7 days partitions into the other table for backup using partition exchange?

View 9 Replies View Related

Create Partition without Range (data Not Matching Other Partitions)

Feb 21, 2013

I would like to cretae 3 partitions for a table

partition 1 = < 2011
partition2 = <2012
partition 3 = any other value not staisfying partition 1 and partition 2

i would like to know how to create a partition 3 without specifying the range so that the data which are not coming under the range of other partitions (Partition 1 & 2) will automatically goes to this partition 3.

Also in the above case if i insert data of year 2009 then where will it go , to partition 1 (or) 2 since both of them matches the condition (<2011 and <2012) (or) do i need to use between clause while creating partitions?

View 3 Replies View Related

SQL & PL/SQL :: Truncate Master Table?

May 17, 2013

The Scenario is that we have Master and detail table (With Foreign key enabled), we want to TRUNCATE Master table.

1) Is there any option which can Truncate the table without disabling the constraints for child tables...we want to Truncate the table forcefully..
2) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are records in child table)
3) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are NO records in child table

View 15 Replies View Related

Truncate Table Before Import

Aug 16, 2012

i need to import into table A. Before that i will do a truncate.

I will disable the constraint.

DO i need to disable any triggers on the table before importing?

View 12 Replies View Related

SQL & PL/SQL :: Store Procedure To Truncate And Copy Data

Jan 27, 2012

I am very new to oracle and SQL.I am trying to create a store proc that will copy 14 day of data into a table and then truncate the original table. When i compile following code....

CREATE OR REPLACE PROCEDURE STOPROC_TRUNCATE
( dateNum IN NUMBER )
IS
BEGIN
create table AUDIT_14Days as select * from AUDIT where TIMESTAMP >= (SYSDATE - dateNum);
truncate table AUDIT drop storage;
[code]....

View 8 Replies View Related

PL/SQL :: Trigger To Insert In One Table From Other And Truncate 2nd Table

Aug 2, 2012

I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .

If their is any problem in insert in to table t2 , the truncate command should not work .

Truncate command should work only after successfully insert command .

View 3 Replies View Related

Drop Some Partitions In Table On Production Environment

Jul 8, 2011

I have to drop some partitions in table on production environment (to get free space). The environment have to be continuously available. I was considering of use ALTER TABLE ... DROP PARTITION ... UPDATE INDEXES but it is slow, because of use clause UPDATE INDEXES. Is there another possibility to remove these data?

View 2 Replies View Related

Max Limit On Number Of Partitions / Can Have In A Table In Oracle

Feb 11, 2013

What is the limit on number of partitions on a table.on many forums , 1024k-1 is given the maximum limit.But Exactly , I am not able to understand this 1024k-1.

View 2 Replies View Related

SQL & PL/SQL :: Unable To Truncate / Drop Table

May 13, 2011

I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.

I am using Oracle 11g Release 11.1.0.7.0

View 17 Replies View Related

How To Grant Truncate Any Table To A User

Jan 30, 2013

on 11G R2,

We want to grant truncate any table to a user.

How ?

We should create the following procedure ?

1. Create the procedure to truncate the table.

create or replace procedure truncate_table (
table_name varchar2,
storage_type varchar2)

[Code]....

grant execute on <procedure_name> to <user>Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?

View 6 Replies View Related

Truncate Table That Has 2 Billion Rows

Mar 7, 2013

Our DB is 11g and approximately, how much time it takes and do we need to rebuild indexes after truncate?

View 5 Replies View Related

Server Administration :: Impact On Table Of Partition Truncate / Delete?

Oct 21, 2012

Any impact is there if is do the following:

ALTER TABLE MY_TABLE TRUNCATE PARTITION P1 UPDATE GLOBAL INDEXES;

will there be any lock on the table during this operation? DML operations will work without any issue or not?

View 10 Replies View Related

PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?

Apr 24, 2013

I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:

CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;

Recreate Steps:

1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data

[code]...

Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:

ALTER TABLE admin_docindex MOVE;

View 3 Replies View Related

Metadata For Some Of Partitions

Nov 10, 2010

i have many partitions in one table. i need metadata for some of the partitions. We have any option to get the metadata for partitions only.because while selecthing the table meatdata i am getting long script.

View 2 Replies View Related

PL/SQL :: Query To Get Last N Used Partitions

Aug 20, 2013

I have a day based partitioned table TAB1.Let say for month Aug 2013 Partitions are like P010813,P020813,P030813 up to P310813.When gather stats job will run i want to analyze only the last 3 used partitions based on current date, this would be P180813,P190813,P200813. write a query which will give the last 3 used partitions.e.g.

If run query on 20-AUG-2013.P180813P190813P200813FOR 25-AUG-2013P230813P240813P250813

View 1 Replies View Related

PL/SQL :: Dropping Partitions At Once

Nov 2, 2012

ALTER TABLE table_name DROP PARTITION (partition_1000);
ALTER TABLE table_name DROP PARTITION (partition_1001);
...
.........
......
ALTER TABLE table_name DROP PARTITION(partition_1320);(b

it is a delta partition,so trying to remove 320 partitions at once in pl/sql developer for a single table.

Like this i have to remove for more then 15 tables one by one, will this effect the database like filling up the archinve log destination by writing more logs.

kind of problems that i am going to face , as i am doing it on the production box directly.

View 5 Replies View Related

Select From Multiple Partitions

Nov 2, 2010

I have more 100 partition in a table, I would like to query 10 partitions alone in single statement, Hope it could be possible like query data for single partitions, provide the syntax for the same.

Because if I try to query for all the partition then the query is Hanging due to the large no of data, then I can query single partition by partition then it takes more than a day. so, I would like query data for 10 partition in a single select.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved