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.
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?
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
For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ? For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?
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.
I'm trying to find a way to ADD new partitions to local indexes and at the same time specify their tablespaces without having to DROP and RECREATE.
Here´s an example table based on yearly partitioning:
CREATE TABLE "TABL_ANOM" ( ANOM_TS TIMESTAMP(6) NOT NULL , ANOM_TIPO NUMBER(2, 0) NOT NULL , ANOM_NIVEL NUMBER(2, 0) NOT NULL , ANOM_ID NUMBER(10, 0) NOT NULL
[code]...
Here´s an index def for the table:
CREATE INDEX "TABL_ANOM_INDEX1" ON "TABL_ANOM" ("ANOM_NIVEL") LOCAL (PARTITION DGSCOPSX_2011 TABLESPACE DGSCOPSX_2011 ,PARTITION DGSCOPSX_2012 TABLESPACE DGSCOPSX_2012 )
OK. Now I want to add partitions for 2013 so for the table I use:
ALTER TABLE TABL_ANOM ADD PARTITION DGSCOPS_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE DGSCOPS_2013;
and this works fine for the table but I can't find a similar command to simply add additional partitions to the indexes. I know that I can drop and recreate the indexes with the additional partition defs but on some of my tables, I'm dealing with hundreds of millions of rows and I think it would take way too long to drop and recreate all indexes on all partitions.
Also related is the PRIMARY KEY index partitions. Is there a way to add partitions (specifying the tablespaces) without having to DROP and re-ADD the CONSTRAINT with the additional partition for 2013?
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)?
I have partition based table one the basis of year month. And we have 8 local indexes on this table. Every month we have to create a new partition and load data into this partition and the volume of the data is around 14million and the load process is taking long time due to indexes. Is it possible to drop the indexes from particular partition?
SNRCTL> stat LISTENER Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production Start Date 19-JAN-2013 00:50:10 Uptime 0 days 0 hr. 29 min. 51 sec Trace Level off Security ON: Local OS Authentication [code]....
In every oracle documentation for e.g:11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1] we found the local listener status showing both local-ip and vip. Why is not showing in our case?
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.
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
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.
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.
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 -
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.
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?
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.
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
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
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?
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.
