"Oracle parse sql and stores explain plan, if the same sql comes again then oracle don't parse it again (soft parse) and gets the explain plan through cursor sharing."
Is there any parameter through which I can set cursor_sharing refresh interval to generate new execution plan?
I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.
drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;
CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
[code]....
I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.
i am using oracle 10g on solaris 10 os.currently archived log is generated by size wise 52 mb.i want to know whar is the best practice for archive log generation . it should be time interval or size wise.
View 1 Replies View RelatedI 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]/...
The developer team wants to test their application. For the same they need some data which resides at oracle 8i instance. However they are not able to create a dblink between 11g and 8i as this is not supported by oracle.
Now they want the data to be refreshed from 8i to 11g by exp/imp.
Apart from exp and imp is there is any other alternatives?
How to perform db refresh.
View 1 Replies View RelatedSteps to Refresh test Oracle 9i schema from production schema on a Windows platform..
View 3 Replies View RelatedI wanted to know the checkpoint information and the interval of checkpoint for my instance.I have checked alert.log file for the same.
Is there is any other way or any view exists to get that information?
I will have to proceed with Oracle 9 database refresh from production server to integration server. 5 biggest schemas must be exported and imported. They constitute 97% space used in a database. This is very big database so I would like to be sure that everything will go smoothly. That is why i want to ask you some questions.
Have you got any clues for me before I start with exp/imp? From my side i will tell you that I will have to exp/imp schema by schema because there is small space both on production and integration disk for a dump. First thing I thought are dependencies between schemas that are exported and that which are not, and also between schemas that are exported/imported one by one.
This is procedure that I plan:
For every schema that is to be refreshed
{
1. Export schema with ROWS=N CONSTRAINTS=Y
2. EXPORT schema with ROWS=y CONSTRAINTS=N
3. Import schema from step one
4. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT.
5. Import schema with rows
}
ALTER TABLE ENABLE CONSTRAINT
With above procedure i think that I will avoid problems with dependencies between schemas exported/imported one by one. But my concern is if there are any dependencies between those schemas and schemas that are not exported. Is there an way to check it before refresh ?
I am getting ORA -32320 when I tried to fast fresh MVs that were truncated.I am able to complete refresh all MVs. However, some MVs are still giving me ORA-32320 when I try to do fast refresh even after complete refresh.
DBMS_MVIEW.EXPLAIN_PLAN says the MVIEW can be fast refreshed.
ERROR at line 1:
ORA-32320: REFRESH FAST of "dev"."Claim_line" unsupported after cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
Quote:ORA-32320:
REFRESH FAST of "string"."string" unsupported after container table PMOPs Cause: A Partition Maintenance Operation (PMOP) has been performed on the materialized view, and no materialized view supports fast refersh after container table PMOPs. Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after PMOPs using the DBMS_MVIEW.EXPLAIN_MVIEW() API.
how to again make the MVs fast refreshable?
I am trying to refresh tablespace containing indexes only. Can you let me know whether i am on the right track. Below is my query :
expdp directory=expdp DUMPFILE=user_index.dmp LOGFILE=user_index.log TABLESPACES=user_index
impdp directory=expdp dumpfile=user_index.dmp logfile=user_index_impdp.log tablespaces=user_index
Iam having the following query, After executing schema refresh using export & import , getting count of database objects comparison to be done,
-- SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;
SQL> SELECT 'select count(*) from '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;
The output expected was to display each table name in a schema following below with corresponding number of records to be displayed, but it wasn't showing correctly.
I have a problem, we have some datas in a table for example 7500 rows in a table name called table1 upto 11:am today. but after 11:25 am i have only 5500 rows. in that table.
the table can be accessed by many users here. we dont know when the delete happended in that table. is there any query to find the transaction log of this particular table.
the deletion should be happended between 11:00 am to 11:30 am. but we have retrieved the data using timestamp query. but we need to know when the query issued and by which user the query has been issued.
How to set time interval in schedule job i need five minutes interval..
View 5 Replies View RelatedI have created a job using DBMS_SCHEDULER and I want it to run every 30 seconds:
begin
dbms_scheduler.create_job(job_name => 'jobu',
job_type => 'PLSQL_BLOCK',
[Code]....
My question is how can I take the value 30 from a configuration table? Let's say I have a query like select value from config_table where property = 'job_interval' that returns the number 30. How can I set this value to be the repeat interval for my job?
schedule the dbms_job to run every Wednesday in 20min interval. I am using below script but getting error
exec dbms_job.isubmit(44,'ER2.LOAD_CRID2_DATA;',trunc(sysdate),'trunc(SYSDATE,''WEDNESDAY'') + 20/1440),TRUE);
IN OUT ROOM
1/20/2011 7:26:00 AM | 3/28/2011 10:34:51 AM | 1
3/23/2011 12:53:00 PM | 3/28/2011 10:34:51 AM | 2
3/14/2011 10:44:00 AM | 3/14/2011 3:05:00 PM | 3
2/24/2011 2:31:00 PM | 2/25/2011 9:02:00 AM | 1
2/22/2011 1:32:00 PM | 2/23/2011 9:56:00 AM | 2
2/22/2011 1:30:00 PM | 2/22/2011 1:32:00 PM | 1
12/9/2010 6:42:00 PM | 12/11/2010 1:38:00 PM | 3
3/8/2011 4:48:00 PM | 3/28/2011 10:34:51 AM | 1
Here I need an out put like below
room 1 2 3 Total
date 7am-12pm 12pm-7pm 7pm-7am 7am-12pm 12pm-7pm 7pm-7am 7am-12pm 12pm-7pm 7pm-7am
01/20/2011 2 3 1 1 3 3 2 4 1
01/21/2011 1 2 2 1 0 0 0 0
...........................................
.......................................
Total
i have to divide into 3 groups and take a count 7am-12pm, 12pm-7pm, 7pm-7am groups
It looks so complicated to me, because IN time and OUT time together how we do it.
suppose one person 6am IN and out 8PM means he will be in 7am-noon , noon to 7pm, 7pm-7am -- 1, 1 1 on 3 interval another scenario is if one person in 2am in the morning it has to be previous days count. Is this possible to do it in query.
If a Interval Partitioning can be created on a table for every fortnight ? db version is 11g.
View 3 Replies View RelatedInterval partitioning I keep getting the below error on a table.A more discerning eye is needed
PARTITION DEC_2012 VALUES LESS THAN (TO_DATE('01-01-2013', 'DD-MM-YYYY')),
*
ERROR at line 26:
ORA-14037: partition bound of partition "DEC_2012" is too high
CREATE TABLE STATISTICS_PART
(
ID_KEY NUMBER(10) NOT NULL,
LUD DATE DEFAULT sysdate,
[code]....
I have one page with an interactive report (Page 10) and another with a form to update each entry (Page 20).
The table that these pages refer to have a column called MY_INTERVAL_COL of type INTERVAL DAYS TO SECONDS.
I can successfully display the contents of MY_INTERVAL_COL by extracting DAYS, MINUTES, and SECONDS on Page 10:
TO_CHAR(EXTRACT(DAY FROM MY_INTERVAL_COL)) AS MY_DAYS,
TO_CHAR(EXTRACT(HOUR FROM MY_INTERVAL_COL)) AS MY_HOURS,
TO_CHAR(EXTRACT(MINUTE FROM MY_INTERVAL_COL)) AS MY_MINUTES,
However, the Automated Row Fetch process appears to ignore columns of type INTERVAL DAYS TO SECONDS on Page 20.
:P20_INTERVAL (database column MY_INTERVAL_COL is used)
:P20_DAYS (trying to convert :P20_INTERVAL using TO_CHAR(EXTRACT(DAY...
:P20_HOURS (and so on...)
:P20_MINUTES
Does Automated Row Fetch ignore columns of type INTERVAL DAYS TO SECONDS?
Oracle 11gR2 used. Apex 4.1
I have a procedure that has a 'INTERVAL YEAR TO MONTH' parameter. What value do I pass to this parameter?
View 1 Replies View RelatedOther than the obvious to me, where interval partitioning creates partitions as needed. Is there any performance benefit from using interval partitions vs date range partitions.
One draw back for me is that developers do access the partition name in some of their queries, so if I use date range partitioning this will not break their code. I could not find a way to assign a name to a partition when using intervals, is this always system generated or can this be over-ridden.
I am running Oracle 11.1.0.7 soon to be running on 11.2.0.0
I'm having trouble using interval data types in a procedure. I need to pass a number of minutes as a parameter, and then use them for arithmetic on a timestamp with time zone. This works no problem:
set serveroutput on
create or replace procedure tstz(mins varchar)
as begin
dbms_output.put_line(systimestamp - interval '10' minute);
end;
[code]...
I've tried a few variations of data type and type casting for the parameter, but I can't make it work.
I have this remote database A and database B. DB A has 10 views and DB B has 10 tables. I have to pull out data from views of DB A and load into tables of DB B at regular intervals. How do I do this job?
View 3 Replies View RelatedWe have a table like below.
Create table Item_max
(
CCN varchar2(10),
Run_date date,
Item varchar2(10),
status varchar2(1)
[code].....
Currently we are inserting the cycle data for all Items every day into above table for all the CCN's.Currently there are 100k Items in our DB and gets inserted every day into above table with Different Rundates for all CCN's.
We need the above data for 6months and we would like to purge the data based on the RunDates.
We thought of creating a Partition on CCN and Interval Subpartition on Run_date with Interval of every 1 day but I could get any materials on the net to have List-Interval partitioning in Oracle 11G.
Also List - Range partitioning doesnt work here as the Dynamic partition used(using Maxused) will contain the data for all Run_dates instead of particular rundate.
I have this requirement to pull the ACTIVE days that a participant holds true in a given month for a specific position he/she holds.The three date ranges here are: LOA dates(leave of absence), XFER dates(TRANSFER in/out of the position),Quality compliance(QUAL start/end dates).So,
INACTIVE DAYS = [(LOA + XFER + WW) - OVERLAPPING DAYS OF (LOA+XFER+QUAL)]
CREATE TABLE PAYEES
(
RUN_PERIOD DATE NOT NULL,
PAYEEID VARCHAR2(20 BYTE) NOT NULL,
LOA_START_DATE DATE,
LOA_END_DATE DATE,
[code]....
I am able to pull out the no of days on a individual basis(count of days for Xfer/LOA/Qual) but I am really not sure as to how should i go about determining the overlapping days between 3 date ranges to determine the ACTIVE/INactive days.
Let's say I have this table:
create table TEST
(
CF VARCHAR2(16),
START_DATE DATE,
END_DATE DATE
)
with
insert into test (CF, START_DATE, END_DATE)
values ('ME', to_date('01-01-2011', 'dd-mm-yyyy'), to_date('31-12-2010', 'dd-mm-yyyy'));
I need make a select where I get n rows, where n is the number of trimesters that compose the date interval in the table. Each of the returned rows must have as start_date/end_date the boundaries of that trimester.(ALL start dates are the first day of a month, and all end dates are the last day of a month)
In this case I need to get:
ME - 01/01/2010 - 31/03/2010
ME - 01/04/2010 - 30/06/2010
ME - 01/07/2010 - 30/09/2010
ME - 01/10/2010 - 31/12/2010
When I connect to a Oracle database(remote) using TOAD client, because of the settings of timeout done at the server end(in Oracle), the TOAD session gets time out in 5 minutes.
I want to know how I can increase the timeout interval or if I can keep on pinging the database server so that the TOAD connection does not gets timeout?
PS: When we connect to a unix server using putty, we can keep on pining the server at regular timeout interval by going to Change Settings->Connection->Set KeepAlive
Is there a similar way for TOAD?
I Have created a interval partitioned table with local index.But when automatic partitions are created based record insertion, whether local indexes will be created for each newly created partition or not? If created, how to check
Below is the code which I tried
CREATE TABLE interval_date
(
date1 date,
days VARCHAR2(50)
)
PARTITION BY RANGE(date1)
INTERVAL (NUMTODSINTERVAL(45,'DAY'))
[code]....
I am trying to remove duplicates from a table with over 10million records. Below query is working fine but it doesnt contain any COMMIT interval. I have to commit after every 20k or 30k records deletion for which IF loop is necessary.
Query:
delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);
