SQL & PL/SQL :: Drop Partitions With Zero Rows

Sep 26, 2011

Query to drop partitions on a table who have no.of rows as zero.

select 'ALTER' || '' || 'TABLE' || TABLE_NAME || 'DROP' || 'PARTITION' || PARTITION_NAME from dba_tab_partitions where TABLE_OWNER='xyz' ;
select count(*) from table_name partition (partition_name);

View 14 Replies


ADVERTISEMENT

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

SQL & PL/SQL :: Trigger To Drop Partitions And Local Indexes

Sep 29, 2011

writing a trigger to drop partitions with zero rows which are older than 6months and drop the local indexes and rebuild the global indexes for any schema in a databaase ?

I have tried the below code :

declare
v_statement varchar2(600);
v_rows number;
begin
for x in (select *
from dba_tab_partitions
[code]........

I want to avoid using row number and also want to dynamically select a schema when executing the script.

View 39 Replies View Related

Drop Partitions Stale Percent Of Stats?

Nov 12, 2012

In my database,stale_percent is set to 10. and i have table which has partition. i have dropped table partition dropped which has 10% of data. I would like to know whether oracle will consider only insert,update,delete as stale percent or will it include the dropping paritition data also. Because my stats gather is not running. When i include drop partition data it exceed 10% of stale_percent,But excluding dropped partition it is not exceeds 10% of stale.

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ---------- ----------- --- -------------
sample_DATA_DATA 235825577 0 0 11-NOV-2012 NO 3
test_DATA_DATA 811618472 0 0 11-NOV-2012 NO 12
sample_DATA_DATA SYS_P2665099 3005966 0 0 11-NOV-2012 NO 0
sample_DATA_DATA SYS_P2665119 3873671 0 0 11-NOV-2012 NO 0

[code].....

View 6 Replies View Related

Takes Long Time To Drop Tables With Large Numbers Of Partitions

Jul 17, 2013

11.2.0.3 This is for a build. We are still in development. No risk of data loss. As part of the build, I drop the user,re-create it, re-create the objects. Allows us to test the build all the way through. Its our process. This user has some tables with several 1000 partitions. I ran a 10046 trace and oracle is using pl/sql to do loops to do DML against the data dictionary. Anyway to speed this up? I am going to turn off the recyclebin during the build and turn it back on. anything else I can do? Right now I just issue 'drop user cascade'. Part of is the weak hardware we have in the development/environment. Takes about 20 minutes just to run through this part of the script (the script has alot more pieces than this) and we do fairly frequent builds. I can't change the build process. My only option is to try to make this run a little faster.

View 3 Replies View Related

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

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

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

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

PL/SQL :: Ranking Partitions - Value Identifier

Jul 4, 2012

Lets say I have a table like the following -

ID--------DATE_TIME-----------------VALUE
101----- 01/01/2012 14:00:00 ---12
101----- 11/01/2012 23:00:00 ---17
101----- 13/01/2012 10:00:00 ---22
101----- 19/03/2012 08:00:00 ---7
101----- 19/03/2012 19:00:00 ---7
101----- 19/03/2012 20:00:00 ---7
101----- 20/03/2012 02:00:00 ---3
101----- 20/03/2012 03:00:00 ---3
101----- 21/03/2012 13:00:00 ---14
101----- 21/03/2012 14:00:00 ---14
101----- 21/03/2012 21:00:00 ---13
101----- 21/03/2012 22:00:00 ---13
101----- 21/03/2012 23:00:00 ---13
101----- 22/03/2012 00:00:00 ---13

I'm looking for a script to partition the data into sections where the VALUE is the same over a constant period of time with no breaks. I'd like to give each partition a value to identify it by.

So the outcome of the script would be the following -

ID--------DATE_TIME-----------------VALUE-----IDENTIFIER
101----- 01/01/2012 14:00:00 ---12----------1
101----- 11/01/2012 23:00:00 ---17----------2
101----- 13/01/2012 10:00:00 ---22----------3
101----- 19/03/2012 08:00:00 ---7------------4
101----- 19/03/2012 19:00:00 ---7------------5
101----- 19/03/2012 20:00:00 ---7------------5
101----- 20/03/2012 02:00:00 ---3------------6
101----- 20/03/2012 03:00:00 ---3------------6
101----- 21/03/2012 13:00:00 ---14----------7
101----- 21/03/2012 14:00:00 ---14----------7
101----- 21/03/2012 21:00:00 ---13----------8
101----- 21/03/2012 22:00:00 ---13----------8
101----- 21/03/2012 23:00:00 ---13----------8
101----- 22/03/2012 00:00:00 ---13----------8

I was trying to do something with trunc(date_time) but that didnt work out right as the blocks of data can carry over several days as seen in the rows with IDENTIFIER = 8.

View 7 Replies View Related

Partitions On Materialized Views?

Nov 29, 2012

Can we add partitions for materialized views like tables ? ALTER TABLE owner.tablename ADD PARTITION p1 VALUES LESS THAN (2012,12);

Like is there any syntax for mview ALTER TABLE mv.mviewname ADD PARTITION ..... ?

View 2 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

PL/SQL :: Dictionary For Materialized View Partitions

Sep 2, 2012

I created a materilaized view with partitions and i need to add partitions dynamicaaly using stored procedure for that i need to check whther the partiions with the same name existing.where can i see the partition names for a materilaized view is there any table like "USER_TAB_PARTITIONS"?.if the same query exists in the forum

View 3 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

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 View Related

Range Partitions On A Date Column

Jul 7, 2012

I have a table which has 2 range partitions on a date column currently.

CREATE TABLE TABLEA (
     RUN_TIME INT NOT NULL
)
PARTITION BY RANGE (RUN_TIME)
(
     PARTITION DATAONE VALUES LESS THAN (20110101000000) TABLESPACE SPACE1
     PARTITION DATATWO VALUES LESS THAN (MAXVALUE) TABLESPACE SPACE2
);

I am planning to drop one partition i.e DATAONE. So table will have one partition left for MAXVALUE. Does it make sense to have a partition with MAXVALUE? Isn't it same as TABLEA in terms of number of records? TABLEA is also in tablespace SPACE2. Should i remove partition DATATWO also? If i have to what is the best way to move all DATATWO records to base table TABLEA?

View 6 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

Partitions For Archiving - Date With Completed Flag?

Jan 29, 2011

I am working on an archiving strategy. I want to roll off transactions that are older than seven days, but only if they are flagged as Completed. The numbers of transactions are very large so this is a worthwhile venture.

The only strategy I have been able to come up with so far is to partiton on date. Then when 7 days comes up, sweep the about-to-be archived day for the few remaining not Completed transactions, put those into a new table (a new version of this partiton) and switch partitions. Each day I do this until the older parititions are empty.

View 7 Replies View Related

SQL & PL/SQL :: Two Partitions Based On Gender Male And Female

Mar 27, 2010

I have a table with 1 million rows,all rows are based on the gender male and female.

So that i have done two partitions based on the gender Male and Female.

After that i performed the query

SELECT COUNT(*) FROM student WHERE gender='MALE';

In that case performance is improved,because oracle scans a single partition instead of scanning entire table.

Suppose i have partitioned according to the date column and i performed the query

SELECT COUNT(*) FROM student WHERE gender='MALE';

In that case performance will be improved or not

View 3 Replies View Related

PL/SQL :: Use Securefile For New Partitions Made By Interval Partitioning

Oct 19, 2012

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.I have a range partitioned table having lobs as basicfile. Due to storage issue and other business constraints , it is determined not to change existing lobs to securefile.

However ,we want new lobs to be in securefile and alter table to have  interval partition+.While researching, I found sql to change lob in range partition to securefile by using

alter table t1 add partition t1_p2 value less than (10000) lob (col3) store as securefile (tablespace tbs_sf1)

What to do similar  in case of interval partition.

View 3 Replies View Related

Server Administration :: Taking Partitions Offline In Tables

Feb 4, 2012

In an attempt to take older data off line and allow database refreshes to be faster, tablespaces associated with partitioned table data for a given time period was taken off line, leaving only tablespaces that relate to the current time period online. In effect, tablespaces related to 2010 and earlier were taken offline from a table.

1. Without giving a filter on the partition key (the business date) to scan for data greater than the dates in the off lined tablespace partition, we get a ORA-376/ORA-1110 error (data file cannot be read at this time).

2. Materialized views using fast refresh or refresh on commit, will also not work because of the partitions being off line.

Queries directly querying the tables are manageable from an application point of view.But the materialized views failing to aggregate is a bigger problem.

how we can manage this situation? I know that I can move the partitions to a different table in a tablespace to be taken off line. But if possible, we wanted to solve this without doing a move partition.

View 2 Replies View Related

Performance Tuning :: How Oracle Organizes Records In Partitions

Mar 9, 2012

I have a question regrading how oracle fetches record in case of partitioning...

we have a table of nearly 6 crores of records.. that table has several columns among which the DBA has given the range partition on COL1 and hash subpartition on COL2...

CREATE TABLE ORDER_BOOK
(CUST_ID NUMBER(10),
ORDER_DATE DATE,
...........,
...........,
...........)
PARTITION BY RANGE (CUST_ID)
SUBPARTITION BY HASH (ORDER_DATE)
[code]........

I want to know how the rows will get organised in the tablespaces...the DBA didn't mention any tablespace name in the main partition and he has mentioned the tablespace names only in the subpartition template. so how the records will get organised.

View 5 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

Export/Import/SQL Loader :: Query Parameter And Partitions

Nov 28, 2012

Version : 11.2.0.3

EMP_DTL table is a subpartitioned table (Range partitioned by MonthID and subpartitioned by COUNTYR_CODE).

It has 40 million records. I just wanted to export 100,000 records altogeter from all partitions for testing purpose.

But when I ran the below expdp with QUERY , it was exporting 100,000 records from each subpartition of the table !!

expdp "'/ as sysdba'" tables = HRTB_CMBH.EMP_DTL dumpfile=EMP_DTL_BKP.dmp DIRECTORY= DATA_PMP1 QUERY=HRTB_CMBH.EMP_DTL:"where rownum < 100001" LOGFILE= exp-partitionedTable.log The log

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201205"."TCO_201205_IND"  38.48 MB  100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201206"."TCO_201206_IND"  42.51 MB  100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201205"."TCO_201205_HKG"  31.28 MB  100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201206"."TCO_201206_HKG"  32.97 MB  100000 rows

[Code]....

This is not mentioned in the Utitilies document. Is this expected behaviour ?

View 2 Replies View Related

Server Administration :: Increase Interval For Date Ranges - Maintaining Partitions?

Jun 3, 2010

I am studying Oracle® Database VLDB and Partitioning Guide 11g Release 2 (11.2),

ORA-14767: Cannot specify this interval with existing high boundsby giving an example other than given in the above mentioned document.

the example given in the document is as follows:

To increase the interval for date ranges, then you need to ensure that you are at a relevant boundary for the new interval. For example, if the highest interval partition boundary in your daily interval partitioned table transactions is January 30, 2007 and you want to change to a monthly partition interval, then the following statement results in an error:

CREATE TABLE transactions
( id NUMBER
, transaction_date DATE
, value NUMBER)
PARTITION BY RANGE (transaction_date)

[code]/...

View 3 Replies View Related







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