SQL & PL/SQL :: Finding Queries That Took More Than 15 Secs To Complete?
Aug 31, 2010how to find out queries that took more than 15 seconds to execute.
View 4 Replies
ADVERTISEMENT
SQL & PL/SQL :: Fraction Of Secs In Timestamp
Mar 12, 2011where exactly the FRACTION of seconds being used, in the TIMESTAMP Data Types.
Example in the below result.
CURRENT_TIMESTAMP
-----------------------------------------------------------------12-MAR-11 10.35.39.691342 PM +05:30
Converting MySQL Queries To Oracle Compatible Queries
Jan 23, 2007our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
RAC ASM Complete Shutdown And Startup
Jul 7, 2011I need to take the certain logs on my 3-Node, 10gR2 RAC with ASM on Solaris 10.I have been asked to shutdown the database, nodeapps, listener, asm and crs and then restart crs. Below are t steps that I have prepared.
CODE
====Enable trace=========
ENABLE LEVEL 5 tracing-
./crsctl debug log crs allcomp:5
====Stop database, asm, listener, nodeapps finally crs===
1. STOP DATABASE
srvctl stop database -d DB_NAME
[code]....
Workflow :: Activities Which Are Not COMPLETE
Jan 16, 2013I'm trying to abort some workflow activities which are not 'COMPLETE' and then purge them. I'm using "WF_ENGINE.AbortProcess" to accomplish this task and then purge using "WF_PURGE.Total" to purge the activities. During this process, does any notifications fire?
View 2 Replies View RelatedComplete The OCA In 10g For Oracle Administration
Aug 5, 2013I have done IZ007 in 9i. Now I wanted to complete the OCA in 10g for Oracle Administration. which exam I need to go for to complete 10g OCA.
View 1 Replies View RelatedPrivate Strand Flush Not Complete
Feb 5, 2013The "private strand flush not complete" is a "noise" error, and can be disregarded because it relates to internal cache redo file management.
However this "private strand flush cannot complete" error is sometimes accompanies by a hung database.
These are quotes from MR Burleson himself .
But can this be really ignored as it's also mentioned "hung database"
SQL & PL/SQL :: How To Get Complete Structure Of Materialized View
Aug 27, 2010I was trying to get the structure of mview through ALL_MVIEWS, but I was not getting the complete one.ORDER BY clause was missing.
how we can get the complete mview structure.
Replication :: How To Monitor Complete Refresh
Oct 29, 2008I am using basic replication in oracle 10.2.0.3. My materialized view object contains around 3 million records. Usually we are using FAST refresh but in my materialized view having some problem that’s the reason I am going to Complete refresh.
If I start complete refresh it will take 8-12 hours to complete refresh.
My Question is: During Complete refresh how to monitor complete refresh running or not except using
v$mvrefresh.
Is there any way to monitor complete refresh because suppose target database not available/network problem/ dead lock occur in my database. I can’t able to track refresh.
Even there is no notification in alert log.
Performance Tuning :: Checkpoint Is Not Complete
Sep 15, 2005I am running Oracle 9.2.0.1 on Solaris 9. On just about a daily basis we perform sqlldr loads that load on the order of 300000 rows. I frequently see in my alert log:
Checkpoint not complete
Current log# 5 seq# 176431
I have 5 redo logs each of 10M in size. If I check what is going on in v$log and correlate to the alert log when it throws the checkpoint error I always notice that I have one current log (which is good) and the rest are in a status of 'ACTIVE'. It seems that when this happens I get the checkpoint error.
What can I do to get rid of this checkpoint error? Should I increase the size of my redo logs? Is there a good way to go about estimating what size redo logs I should have?
ORA-39714 - Upgrade Script Failed To Complete
Jun 14, 2010while upgrading to 11.1.0.7 from 10.2.0.4 at the end we are facing this error
ORA-39714: upgrade script utlmmig.sql failed
*Cause: A normal database open was attempted, but the upgrade script utlmmig.sql failed to complete.
*Action: Use the UPGRADE option when opening the database and then run utlmmig.sql.
i ran the utlmmig.sql script and i am facing this issue
SQL> -- we also need to update the statistic
SQL> begin
2 dbms_stats.delete_table_stats('SYS', 'OBJ$MIG');
3 dbms_stats.delete_table_stats('SYS', 'USER$MIG');
4 dbms_Stats.gather_table_stats('SYS', 'OBJ$MIG', estimate_percent => 100,
5 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
6 dbms_Stats.gather_table_stats('SYS', 'USER$MIG', estimate_percent => 100,
7 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
8 end;
9 /
begin
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"
ORA-06512: at line 2
compiled the invalid objects using utlirp.sql and utlrp.sql but of no use..
SQL & PL/SQL :: How Make Complete Schema Public Synonym
Apr 15, 2013how to make the complete schema public synonym, instead of creating public synonym object by object.
View 2 Replies View RelatedClient Tools :: How To Create Complete Setup
Mar 27, 2012I would like to pursue pl/sql developer as my carrier.So I would like to create a setup in the following manner in my windows 7(64 bit) laptop.Since I heard that,In real time the setup will be look like as follows
Windows7 as front end
Toad --> to practice Pl/Sql(Preferred use in windows7)
Solaris OS (To install Oracle 10g in Solaris & practice scripting)
Virtual Box (Open source tool to run both windows and solaris)
How I can create setup so that I can accomplish following things(To gain knowledge in programming/scripting).
1.Both OS has to be run simultaneously with Virtual Box So that
Oracle will be running in backend(Solaris)
2.Practicing Oracle Pl/sql in Toad (Windows7)
3.Practicing shell scripting in Solaris.
Is it possible to create the setup as i mentioned above.
SQL & PL/SQL :: Loading Complete XML File Into Oracle Table
Mar 19, 2012I am loading data from XML file into Oracle table.This program is working fine for small XML files. If I try to load large XML file with multiple pages, only first ten records are loaded. Here is the procedure.
PROCEDURE Test_xml_read(p_tag varchar2,p_xml_file varchar2,p_path varchar2)
AS
BEGIN
INSERT INTO stg_xml_table(
Productid,productname,price)
select y1.productid,y1.productname,y1.price
y2.categoryid,y2.categoryname,y2.categorypath
FROM xmltable('ProductFeed/Products/Product'
passing xmltype(bfilename('TEST_DIR1', 'sample.xml' ), nls_charset_id('CHAR_CS'))
[code]...
what changes to be done to load multiple pages of data pages table.
Application Express :: Auto-complete To Combobox - Where Is LOV
Jan 9, 2013A few years back, most likely under Apex 4.0, I started using Jquery - Autocomplete for looking up employee names, hostnames, etc. This used some Javascript that someone had posted (don't recall who), and it worked pretty well. I would add a stanza like this to the page def (to be run after the page loads). The X02 passed some extra info to the on demand procedure to work with the search.
$("#P28_REQUESTOR").autocomplete('APEX', {
apexProcess: 'PERSON_SEARCH_ACTIVE',
width: 600, multiple: false,
x02: 'Hostmaster:Owner Ok',
[code]...
END;The g_x01 value is what the user is typing into the text box - once they hit three characters, it gets sent to Oracle for processing via the on demand procedure. The g_x02 (and 03, 04,..) are additional parameters - perhaps from a parent LOV. General concept with the returned data is the first element is the text to be searched, the second is the actual value (typically an integer - primary key), and the third value is what ends up in the text box once a value is selected (the searching value often has additional identifying info.)
This approach worked pretty well, although it did not play well with cascading LOVs. But once I upgraded to Apex 4.2, I started hitting other issues with jQuery version differences, and some odd failures. To make matters worse, the original developer of the Javascript module, abandoned it - was not doing additional work with it.
Combobox
I did NOT want to re-invent the wheel, and it seemed to be a much better approach to move into PlugIn world - ideally using a plugin that someone ELSE wrote and was maintaining. I looked at the LOV Friendly Autocomplete plug in - and even installed it a few places - and discovered that it did not play well with my earlier autocomplete code.
My concern with using the LOV Friendly Autocomplete - is that I didn't know how to provide any of the "Target Name" search info to the LOV (since that is what is being typed by the user at the time).I also started looking at the Combobox plug in - which seemed even closer with Lazy Loading=Yes (I also liked the ability to add new items on the fly) - but I was not sure how to provide the "Target Name" info the the Pipelined function that generates the list of values.
I want to get rid of all of my old Autocomplete javascript and move to one (or more) plugins - (Which is why the details above don't matter, just trying to provide some background)Is this a good direction (Combobox or LOVFA)? How/Where do I connect my search (lov generation)?
RMAN :: Delete Backup And Complete Job Details
May 21, 2013I deleted all my RMAN backup with delete backup command .but when i run query on v$rman_backup_job_details, show information, i don't know that v$rman_backup_job_details read data from where? and how to delete complete that v$rman_backup_job_details don't show any thing?
View 8 Replies View RelatedSQL & PL/SQL :: Update Complete Flag Most Recent 2 Rows By Category
Jul 29, 2013I 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?
Forms :: FRM-91500 - Unable To Start / Complete Build
Oct 9, 2009I am trying to modify an existing form in oracle apps. I copied all the necessary objects and modified the form. I can compile the object in windows. When I am trying to compile the object in Linux server using SSH I am getting the following error.
FRM-91500: Unable to start/complete the build.I am using the following command for compile
f60genm userid=apps/apps batch=yes module= FORMNAME.fmb module_type=form.
Replication :: Alter Materialized View From Complete To Fast?
Jan 4, 2008I am having two server A and B.B is the replica of A.I have to alter the materialized view of one of the table in server B,of which Master table is in both the servers .I do not have any Materialized view log on both the servers and want to convert the Refresh method from Complete to Fast refresh.One way of this altration is to create materialized view log on master table.
View 6 Replies View RelatedSQL & PL/SQL :: Using INSTR In Where Clause To Give COMPLETE MATCH Strings?
Dec 9, 2011We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .
The procedure builds the string by inserting single quotes in the following manner.
P_BU_LST is the parameter which have comma separated values
'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value
i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';
This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS
ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;
INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577
here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.
But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.
SQL & PL/SQL :: Receiving ORA-29913 And ORA-30653 Errors And Query Does Not Complete
Sep 23, 2010Receiving ORA-29913 and ORA-30653 errors and query does not complete. The queries that receive the error are shown below <> represents substitute your filename; There are actually three different queries that must run to produce an expected output. Those queries run in the order which they appear below:
Query1
select distinct HLQ "highlevel", RESOURCE "RES" from <filename1>
Query2
select distinct ENT.RESOURCE "RES", ENT.ID "PLEX"'
case
when ENT.ACTION is null then nextkey'
else ent.action
end "ACT",
case
when ENT.ACTION is null then ENT.CONDITION_NEXTKEY
end "NRULE",
'TESTSET' "ENTTYPE", replace(My.Owner, '"',' ') "Owner"
from <filename1> ENT, <filename2> ORS
where ENT.f1 = ORS.f2
and ENT.APPLICATION = '<entertypehere>'
Query3
select ENT.ID "AID", ENT.RESOURCE "RES", ENT.ID "PLEX", replace(My.Owner, '"',' ') "Owner", TESTSET' "ENTTYPE" from <filename1> ENT, <filename2> ORS
where APPLICATION = '<entertype>'
and ENT.<f1_column_name> = ORS.<f2_column_name>
Error returned is: "Ora-29913: error in executing ODICEXTTABLEFETCH callout; Ora-30653: reject limit reached"
In the log file of the job that runs these queries, I can also see the following
NULL if (CAS9 = ~) error processing column C5 in row 1 for datafile /home/oracle/DataDir/<filename>_metadata.data
I have checked and it looks like the routine creates ODICEXTTABLEFETCH as it needs it and then throws it away leaving me with Ora-12899 Value too large for column C5 (actual: 13, maximum: 10)
RMAN :: Complete Procedure To Restore Database On New Location
Aug 29, 2012I have taken full backup of database plus archivelog. Then copied them to new server..I want the complete procedure to restore the database on new location.
View 3 Replies View RelatedLink To Download Oracle Complete Reference Ebook?
May 24, 2010I'm a webdeveloper working in .net with sqlserver.Now in my office,we're going to do a proj with oracle DB.I'm idiot about oracle.Then i browsed in internet for oracle and i found out something like orcale8i,9i,10i,oracle developement and all. i dont know where to start to refer.any link to download oracle complete reference ebook?
View 2 Replies View RelatedReports & Discoverer :: Report Not Printing Complete Line On Second Page
Oct 28, 2010I am trying to create a report, but for the second page its trying to print the text from the middle of the page, i.e its cutting half line, suppose if the line has description 'HELLO WORLD 123456' I see only 123456, the whole report is cutting the first 15 characters for the second page?
View 1 Replies View RelatedServer Utilities :: How To Find Estimated Time To Complete Import (imp)
Apr 17, 2012I am using imp / Schema level. Dump file contains 3 schemas with 120 GB size.Imp is running now. How to find completion time. So that I can inform to application developer about activity completion time.
View 4 Replies View RelatedReplication :: Alternative For Complete Refresh / Delete Records From MVIEWS?
Aug 19, 2009TABLE NAME :ABC
(PARTITION BASE TABLE - 84 Partition and each Partition has 500-800 Millions of Records)
/* Step1 Create MATERIALIZED VIEW LOG */
CREATE MATERIALIZED VIEW LOG ON ABC;
/* Step2 Create MATERIALIZED VIEW Refresh after Every 20 Minutes */
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/ (24*60) AS
SELECT * FROM ABC
WHERE TMSTP > SYSDATE-1;
Scenario:Intially data extracted will be "Greater than SYSDATE-1 = 18/08/2009 11:27:39 " - 76 Millions of Records After 20 Min of FAST Refresh, it will add 2500 Records
i)Is query in the MVIEWS (MV_ABC) SYSDATE-1 will be changed on each FAST Refresh, as observation (as per definition) it is incrementing the records to intial extracted data?
ii)As COMPLETE Refresh will be expensive to execute on the partition base table on each refresh.
Is there any alternative way to achieve the Nature of COMPLETE REFRESH in Materialized View?
iii)Is Delete or Conditional Delete option is possible in Materialized View (i.e not a delete from the base table but the delete from MVIEWS)?
iv)What is the machnism for populating the logs for MVIEWS?Is it overhead for the system and better than a Trigger based approach?
Server Administration :: Time Remaining To Complete Table Statistics?
Jan 16, 2012I use the following query to find out the remaining time to complete the table statistics which is running currently.
SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE
TOTALWORK != 0
AND SOFAR != TOTALWORK
order by 1;
SOFAR column shows 9325 and totalwork column shows 12287.How to calculate the columns in terms of hours and minutes ?
And also sometime the query never shows the output of the query for the current running query.
Replication :: Materialized View Complete Refresh Automatic Execution
May 5, 2010how to check the time taken for materialized view to execute at one time.
My materialized view is a complete refresh which automatically executes at 3:00.
a) I have queried dba_jobs and dba_mview_refresh_times, and matched the last_date and last_refresh to find the total time. How can I be know that JOB 80 corresponds to my view MVIEW_UNION in a much easier way?
SQL> select job,last_date,total_time from dba_jobs order by last_date;
JOB LAST_DATE TOTAL_TIME
---------- ----------------- ----------
80 05/05/10 03:00:02 5255
SQL>select name,last_refresh from dba_mview_refresh_times where owner
NAME LAST_REFRESH
------------------------------ -----------------
MVIEW_UNION 05/05/10 03:00:02
b) Also is total_time the time it took the query to execute today at 3:00, i.e it started at 3:00 and ended at around 4:45(3+5255 msec) or is it the overall time it has taken since the day it was scheduled in dba_jobs( i.e 1 week back)
c) Also my source table dosent contain any indexes and I have created indexes on materialized view, i want to know if the complete refresh will recreate the indxes.
Data Guard :: Archive To Complete Recovery On Standby Database
Oct 22, 2013i have found an issue regarding log archiving on dest1. yesterday one sequence number 76871 not archive to dest1.alert logfile content as follow. i configure standby and ship archive manually with window copy command. i need this archive to complete recovery on standby database.
Mon Oct 21 09:29:28 2013
ARC2: Completed archiving log# 3 seq# 76869
Mon Oct 21 09:39:28 2013
Thread 1 advanced to log sequence 76871
Current log# 2 seq# 76871 mem# 0: D:ORACLEORADATAORC1REDO02.LOG
[code]....
Server Administration :: TNS Error - DBCA After Database Creation Script Complete
Aug 15, 2010I'm getting a "TNS disconnect" after database creation script creation in DBCA. Haven't figured out what's going on. I'm attempting to create a 2nd database on one linux 64 server. I get all the way through DBCA, but it won't go the final mile. Any thoughts? I've tried setting the port to a different port from the existing LISTENER, creating a LISTENER2 and associating it with the 2nd instance.
View 1 Replies View Related