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
ADVERTISEMENT
Oct 7, 2011
EMP_ID IN_DATE QTY2
1070410/6/11 6:00 AM
1070410/6/11 3:00 PM
1070410/6/11 9:00 PM
1070410/7/11 6:00 AM
1070410/7/11 3:00 PM
1070410/7/11 9:00 PM
1070510/7/11 6:00 AM
1070510/7/11 3:00 PM
1070510/7/11 9:00 PM
following is the table where every employee have 3 records we need to put the flag datewise and employee id wise minimum in_date always be set to 1 and remaining 2 maximum dates for each employee date wise always set to 2 in qty2 field.
Output requirement is as follows:
EMP_ID IN_DATE QTY2
1070410/6/11 6:00 AM 1
1070410/6/11 3:00 PM 2
1070410/6/11 9:00 PM 2
1070410/7/11 6:00 AM 1
1070410/7/11 3:00 PM 2
1070410/7/11 9:00 PM 2
1070510/7/11 6:00 AM 1
1070510/7/11 3:00 PM 2
1070510/7/11 9:00 PM 2
View 4 Replies
View Related
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
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
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
Aug 21, 2012
Database version
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Productionredo logs multiplexed.
[code]....
+ when the redolog 5 was archived how the archiving process works ?
let say both the log members are clean in case which one will be archived 5a or 5b ?
+ I can also see only one archive log is creating during log swtich
View 2 Replies
View Related
Apr 22, 2013
I'm currently working on a project which is to archive the old data and then purge the same data from the main table.
Here is a detail description:
There are around 50 odd tables from which I would need to archive the old data(matching certain filter conditions...not date based). Meaning I have to store the data in a temp table. Once stored in temp table then I would have to delete those rows from the main table. This temp table will be later exported and stored on ARchive database(a seperate database). These tables are very huge. One of the table is actually 250 GB in size. And all these tables have many indexes built - both normal and bitmap.The 250 GB size table has 40 million rows that need to be archived and purged. The total number of rows in the table are 540 million.On this table alone there are 50 bitmap indexes and 2 normal indexes. This table is partitioned based on date column.This date column is not used/useful in identifying the old data. There are around 20 tables which are quite similar in size to the above described table. Rest of them are little small when compared to the above table.
We have to execute this activity over a weekend which gives us about 48 hours time to complete the activity. Best possible ways to handle this activity. Most importantly should be able to complete the activity within the specified 48 hour window.
The solution what we are now thinking of is:
1. Create the temp table ---Create tmp_tbl as select * from main_table where <<conidtions identifying old data>>
2. Once the temp table is created. Make copy of indexes that exist on the main table and eventually drop them.
3. Execute a PL/SQL script to perform the bulk delete from main table and commit for every 100000 rows.
4. Once the bulk delete is finished then recreate the indexes on the main table using the copy made at earlier step.
Our main worry is about the step#4. Considering the size of these tables and the number of indexes to be built,we are not sure how long the index re-creation will run for each table.
depending on the possibilities we may have to split the activity in to 2-3 phases spreading across 2-3 weekends. Even then we are not sure whether we will be able to pull off this activity.
The database we are using is Oracle 10g.
View 1 Replies
View Related
Jul 30, 2013
I just want to write some data in a particular table,But I dont want it to be archivedSo is it possible to disable archiving in session level Oracle 11g Rel 2
View 3 Replies
View Related
Jan 27, 2013
My RMAN backup failed with below error.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 01/26/2013 22:48:56
RMAN-11003: failure during parse/execution of SQL statement: alter system archive log current
ORA-00258: manual archiving in NOARCHIVELOG mode must identify log
RMAN>
Recovery Manager complete.
View 2 Replies
View Related
Apr 29, 2011
I am trying to run the following script but some how i am getting message: anonymous block completed. where i am doing wrong and also how can i display the values with their column names.
set serveroutput on size 1000000
Declare
v_pr_nbr document.document_nbr%type:= ' ' ;
cursor c1 is
[code]...
View 7 Replies
View Related
May 2, 2011
I'm trying to run the following procedure and I'm getting he following message:
Warning: execution completed with warning
PROCEDURE etl_bago Compiled.
The procedure is created, the problem is that when I tried to run it with parameters I'm getting this error:
exec etl_bago ('july19.csv');
Error report:
ORA-06550: line 1, column 7:
PLS-00905: object SYSTEM.ETL_BAGO is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:
%s"
[code]......
Btw, I'm using SQL Developer 1.5.3. And the procedure script is here:
[URL].....
View 33 Replies
View Related
Jun 23, 2011
is there a way to find-out how many GB completed in x time-frame by RMAN on oracle 11.2.0.2.
View 5 Replies
View Related
May 10, 2011
Is there a way to know if a procedure has been completed/ still running?
View 2 Replies
View Related
Sep 26, 2010
I've created one procedure with following code.
PROCEDURE MaxNo IS
A Number;
BEGIN
Select Max(Supplier_ID)+1 into A from supplier ;
END;
SQL> Select Max(Supplier_ID)+1 from supplier;
MAX(SUPPLIER_ID)+1
------------------
6
when I execute it in a SQL PLus
SQL> SET SERVEROUTPUT ON
SQL> exec MaxNo;
PL/SQL procedure successfully completed.
View 4 Replies
View Related
Dec 22, 2010
I have to run a resource extensive SQL. It utilizes CPU to a great extent. I've created a new user just to run this particular report. I need to ensure that this particular user doesn't uses more CPU.
Hence, I need to generate an alarm (email) only if a threshold is exceeded for a certain period of time (above x% for y minutes),(example more than 80% for 10 min.), so that I can either kill the process or continue running it depending on CPU usage.
Can I do it using scripts or OEM. I couldn't find any specific metrics to do it in latter. (I'd prefer scripts though. )
My system details are as follows:
1. Database: Oracle 10g R2
2. Unix: Sun OS 5.10
View 2 Replies
View Related
Sep 17, 2013
I am not a DBA, we are using Oracle 10g DB for our PLM applictaion Enovia, we are now trying to do 4GT in our windows 2003 server where our DB kept, while reading articles about this. we are informed that /LARGEADDRESSAWARE (IMAGE_FILE_LARGE_ADDRESS_AWARE) flag needed to be set to True in the application (here it is Oracle) to use more than 2GB in the RAM.
So My question is "Whether the corresponding flag is set to TRUE or not in Oracle 10g"? Is there any way to check that?"
View 1 Replies
View Related
Jun 7, 2012
I have records like the following
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N
ABCD 2/14/2012 N
ABCD 2/20/2012 Y
ABCD 3/01/2012 N
ABCD 3/10/2012 N
[Code]...
I have to write a select statement to to keep the first record and then pull only the records when the Valid_Flag changed. The result set should be like below.
Program_Name Effective_Date Valid_Flag
ABCD 2/10/2012 N -- I have preserved the first record
ABCD 2/20/2012 Y -- Valid_Flag chages to a Y for teh first time and so on.
ABCD 3/01/2012 N
ABCD 3/14/2012 Y
ABCD 3/25/2012 N
ABCD 4/25/2012 Y
If there is no change in the flag, I do not have to pull that record.
View 3 Replies
View Related
Oct 18, 2012
I have a scenario, where I need to find the bucket values with the flag as "Y". The problem is i have 5 buckets
Bkt1 Bkt2 Bkt3 Bkt4 Bkt5 Flag
------------------------------------------
1000 500 230 100 677 Y
789 100 122 666 888 N
783 110 142 166 788 N
781 110 112 616 988 Y
709 130 121 661 878 N
Here I need to write a SQL query in Oracle 10g fetching the respective bucket value(for all the 5 buckets) along with the flag as "Y" and more importantly should have the bucket name as well.This is needed as in the UI we need to display the respective bkt value which is "Y" as bold.
So in the final List I need to have the VO which should contain the respective bucket name, respective value and flag as "Y"
Some thing like this I need to get in my final ArrayList in java
Bkt1 1000 Y
Bkt1 781 Y
Bkt2 500 Y
Bkt2 110 Y
Bkt3 230 Y
Bkt3 112 Y
Bkt4 100 Y
Bkt4 616 Y
Bkt5 677 Y
Bkt5 988 Y
how we can do it using the SQL query.
View 16 Replies
View Related
Aug 20, 2010
I do not want the "PL/SQL procedure successfully completed." message in my output file - I don't see/recognise an option to suppress or remove this.
-- Test Proc
--
set linesize 500
set pagesize 50000
set tab off
set wrap off
set serveroutput on
set feedback on
[code]...
PL/SQL procedure successfully completed.
View 2 Replies
View Related
Oct 11, 2011
I am trying to import the schema into 11g database, which i took on Oracle 9i database. While import is running, data file is full as auto extension was not enabled.
I got the following error:
. . importing table "WO_GL_ACCOUNT_SUMMARY"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table PWRPLANT.WO_GL_ACCOUNT_SUMMARY by 1024 in tabl
espace PWRPLANT
IMP-00018: partial import of previous table completed: 7055845 rows imported.
Then I increased the datafile size and finally Import terminated successfully with warnings. At this point, I want to know whether WO_GL_ACCOUNT_SUMMARY Table was imported with out missing any rows .
View 7 Replies
View Related
Mar 5, 2008
I have a SQL link table called student_monitor containing the follwing attributes and e.g. data
student_id, Class_id, Predicted_Grade, Actual_Grade
1 1 A C
2 1 B B
3 1 C B
I need to be able to flag which students are under achieving on their target grades and dont know how to do this or the best way to do this as i cannot calculate the diff between a char?
View 1 Replies
View Related
Jul 29, 2013
I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.
The table has fields like:
category - string,
item - string,
creation_date - string ('YYYYMMDDHHMI'),
complete_flag - boolean,
etc.
Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date. Is there a way to accomplish this?
View 8 Replies
View Related
Dec 27, 2012
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE
v_Hire_date date :='12-Dec-2005';
v_five_years BOOLEAN;
BEGIN
IF
[code].....
View 17 Replies
View Related
Jan 9, 2012
The stand alone stored procedure has 2 parameter, an IN and OUT...
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,
tablename OUT VARCHAR2)
This procedure has multiple inner blocks. Intention is to control execution of each of the inner blocks basing on the value of flag field obtained from a "processlogtable". This processlog table has structure as below.
jobname varchar2(100)
controldate (date)
controlflag varchar2(1)
I have the below code snippet at the beginning of each inner block that checks value of the flag and then proceeds with execution of that block. The intent is to avoid redundant call of a block that has successfully executed first time. That is, once a specific inner block fails for some reason, the re-execution of the stored procedure should AVOID re-executing the PRIOR successed steps.
I've the code set up as below, but the prior successed block(s) code gets re-executed again once the procedure is re-executed after a failure.
CREATE OR REPLACE PROCEDURE someprocedure( businessdate IN NUMBER,tablename OUT VARCHAR2)
CURSOR c_missingtablename
IS
SELECT datatablename
FROM (
SELECT UPPER(datatablename ) TABLE
FROM
WHERE datatableName IN ('Aaa','BbB','CcC');
[code]....
View 5 Replies
View Related
Jul 15, 2013
I would like to spool a clob column to a flag file, however some of the clob are greater than 32k, and I have to have the same record in a single line in the file. Is there any way to achieve this through spooling?
set heading off
set feedback off
set term off
set long 1000000
set longchunksize 500000
set line 32767
set trimspool on
set pagesize 50000
spool file.txt
@--this is my select statement.
spool off
exit
View 1 Replies
View Related
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
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
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
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
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