I see a strange result when I check for the offset in different databases,From the same SQL*Plus window.
SQL>
SQL> conn sys/**********@db1 as sysdba
Connected.
SQL>
SQL> col sysdate for a20
[code]....
where this difference comes from?why in db1 it shows +10:00, and on db2 it shows +11:00?
formulating sql query
Basically what I want is that I need to get desired result in such a way that, whenever Transaction type is Sales Order Issue, I want last TRANSACTION_COSTED_DATE of 'Intransit Shipment'
INVENTORY_ITEM_ID TRANSACTION_COSTED_DATE TRANSACTION_TYPE R
123 28-06-2012 21:36 Intransit Shipment
123 23-07-2012 01:25 Sales order issue 28-06-2012 21:36
123 30-07-2012 05:20 Sales order issue 28-06-2012 21:36
[Code]...
Lag with offset 1 doesn’t work as it will only go to previous row, What I want is that it should go to row above where transaction type is Intransit Shipment
Sample data and query I tried
with sampl_rownum_reset as
(select '123' inventory_item_id,
to_date ('28-Jun-2012 9:36:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
transaction_costed_date,
to_date ('28-Jun-2012 9:35:23 PM ', 'DD-MON-RRRR HH:MI:SS AM')
[Code]....
We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.
Service Time : 15.36%
Wait Time : 84.64%
This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.
As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.
My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?
View 1 Replies View RelatedWhen I execute my form, on pressing key F5 it displays the all available block names in the form. I want to restrict this.
View 5 Replies View Relatedi created a form by using wizard...i want to improve that form...
I created one lov which is attached in one of form field..now when i press f9 lov displayed..
But when i select any one record in that LOV , its does not come in my form field
I got the below message in trace file. What does the line "swap info: free = 0.00M alloc = 0.00M total = 0.00M" trying to say?
I have
RAM=1.5G
Swap=3.5G
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /home/oracle
System name:Linux
Node name:linuxdev
Release:2.6.5-7.97-default
Version:#1 Fri Jul 2 14:21:59 UTC 2004
[Code]....
Imagine a table below and I want to write a statement that shows time,name,ip,received and sent (SELECT *). (time, city, ip are primary key lets say).However there is the condition that I want:
It should be grouped for each CITY and I want to get max(RECEIVED) by descending order for each days. How can I do that?
select *
from table
group by city
having max(received)
Is it possible to get output without giving condition to other elements (IP, SENT... etc)
TIME- NAME- IP- RECEIVED- SENT-
10.01.2013,LON,10,2342 ,326
10.01.2013,PAR,10,563463 ,3
10.01.2013,LON,30,23412 ,574
11.01.2013,NY,20,36545 ,246357
11.01.2013,NY,40,23423 ,5
11.01.2013,TOK,40,45475 ,3453
11.01.2013,TOK,30,23423 ,574574
12.01.2013,LON,10,574 ,2342
[code]....
I am trying to investigate my execution plans against my DB; and was informed i can check current status of SQL scripts using:
Performance tab > SQL Monitoring > Monitored SQL Executions
I click on SQL ID i want to investigate and comes back with blank screen instead of the overview tab.
See Oracle URL below for screenshot
Figure 6-8 Monitored SQL Execution Details Page
[URL]
I have been trying to figure out how to write a query that shows each building code building name and number of rooms from a database with four tables : emp, build, room, roombook
View 9 Replies View RelatedI have EM Agent 10g installed in its own Oracle Home (c:oracleagent10g) and Oracle 11g installed in its own Oracle Home (c:oracle11.2.0) The Server shows in EM console but the only target is the EM Agent,.. neither the Listener or the Database show under the targets tab in EM. Also, when DBCA is ran and we get to step 4 it shows NO AGENTS FOUND.
Have installed patch: 8825226 and 8968580 for EM Grid Control (Windows 2003 R2 32Bit) And 9138201 for 10g Agent on Server 2008 R2 (64bit)
I am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code
I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.
I need to create a report that shows only the DD-MON of a birthday while ignoring the year and then having the output in DATE form so I can sort chronologically. My input is in DD-MON-YYYY format.
I have used Date_Trunc to pull out the month and day but the 29-Feb dates are giving me the ORA-1839 error message.
I am using Java print API (javax.print package) to send a bunch of documents for printing. Below is the code section that I am using to print documents through java program. When the document is sent for printing, I see the Job name is created properly in the print spooling queue on windows machine. But, when i go to the actual printer, the job name is different on the printer than what i saw in spooling. Since I am printing 100s of documents in batch, it gets very difficult to identify which document did not print, in case of issues. I also used the java.awt.print package . The java.awt. print. PrinterJob has a method setJobName(String). When i used this package, I got the name appear properly in both places. But I wish to use javax.print with the document name appear on printer queue.
public void printDocument(File pDoc, PrintService pService, DocFlavor pFlavor)
throws Exception { logMessage(true, "Printing Doc::" + pDoc.getAbsolutePath());
FileInputStream is = new FileInputStream(pDoc);
// Create the print job DocPrintJob job = pService.createPrintJob();
//Set print request attributes with file name as job
[code].....
why a Gantt chart would show only the first 15 rows of the query?! I validated id to be unique , parent_id properly pointing to the parent. When I artifically create another level, the chart still subborny shows only 15 rows. The actual query returns 1590 rows.
View 1 Replies View RelatedWhen I am loding the data in person table through sql loder runs successfully without errors but when i check the person table it shows me zero records. Following is the details about what i done.
here are the details of data files.
1 Ahmed Baraka 1000 1.87 1-1-2000
2 John Rice 5000 2.4 10-5-1998
3 Emme Rak 2500 2.34
4 King Size 2700
5 Small Size 3000 31-3-2001
And The control File.
OPTIONS ( ERRORS=0)
LOAD DATA
INFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.dat'
BADFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.bad'
DISCARDFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.dsc'
INTO TABLE "KAILAS"."PERSONS" REPLACE
FIELDS TERMINATED BY X'9' TRAILING NULLCOLS
[code]...
I have a virtual machine which have Windows Server 2008 R2 Datacentre 64-bit.
When I install Oracle Database 11gR2 and click on "SETUP" file, a message box namely JAVA VIRTUAL MACHINE LAUNCHER and display below error;
_"Could not find the main class. Program will exit."_
Same setup runs successfully on my server with Windows Server 2008 Enterprise.
Is it 11gr2 supports a virtual machine which have Windows Server 2008 R2 Datacentre 64-bit ? or some other?
in my catalog database I see in Rc_datafile mismatch sizes - column bytes shows data different from the real sizes of files .
Does this view updated after copy of database into catalog ?
when i performed incomplete recovery i try to set until time but it shows RMAN-20207:until time or recovery window shoubld be before resetlogs.
View 3 Replies View RelatedI got a primary database with a logical standby database running Oracle 11g. I got two client applications, one is the production site pointing to the primary one, another one is just a backup site pointing to the logical one.Things will only be written into the primary database every mid night and client applications can only query the database but not add, update nor delete.And now, I want to apply the latest patch on both of my databases. I am also the DNS administrator, I can make the name server pointing to the backup site instead of the production one.I want to firstly apply the patch on the logical one, and then the physical one.
I found some reference which explains how to apply patches by adopting "Rolling Upgrade Method". however, I want to avoid doing any "switch over" mentioned in the reference because I can make use of name server. Can I just apply patches as the following way?
1)Stop SQL apply
2)Apply patches on logical standby database
3)let the name server point to the backup site
4)Apply patches on the primary database
5)Start SQL apply
6)Let the name server point back to the production site
If flashback is enable in physical standby database 1. If we failover at 11AM can I flash back NEW primary database to 6 AM ? 2. if I convert physically standby database to snapshot standby database at 11AM , Can I flashback snapshot standby database to 6 AM and do some works on it (DML operations) then converting the snapshot standby database into physical standby database ?
View 1 Replies View RelatedDatabase Version - 10.2.0.4.0
OS - Red Hat Linux
We have configured oracle one way stream between two databases. Source database is capturing the changes (No downstream configured). Configuration was working fine but destination database was lagging behind very much i.e about 15 days behind the source database. We are ok with this but the problem is now that , as per client request we have restored previous backup and open the database with resetlog option in source database. After resetlog , archivelog sequence has been changed and stream is not working.
Can I apply the previous archivelog (before resetlog archivelogs ) in destination database anyway.Source database is a production database.
I tried to clone a 2 node rac database to single instance non rac database using existing backup. I have not used connectivity to target or catalog. rman duplicate finished with below messages:
rman auxiliary sys/******@dbracdup
RMAN> duplicate database to dbrac spfile backup location '/oracle/backup';
...
...
Finished recover at 25-JUL-12
Segmentation fault
And the database was in mount stage, and when i tried to open database it failed with below error:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19838: Cannot use this control file to open database .
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....
An SQL query is taking a lot of time than usual and not completing even left after hours! The query joins a table with a quite complex view.
The same query in a test database completes in less than 2 mins.
I would like to export the sql plan from test database to prod database.
how to export/import in 10.2.0.4 version for a particular sql statement's execution plan.
i am installing oracle database 8.1.7 on dell server power edge 2650 first time database successfully installed but when i want to crate new database by Database Configuration Assistant it is not working for new database creation.
View 1 Replies View Relatedwe have a production database 'X'. Now i have created a test database 'T' and did'nt configured another listener to it! The issue is when i cam connecting to oracle through sqlplus i am directly connecting to Test database 'T' but not the production database 'X'----ofcourse i can login to production DB afterwards. but initially i want to access the production database 'X'.
View 15 Replies View RelatedI need to refresh a PROD database into TEST database. The PROD and TEST runs on 10g. I need a full refresh. Is there any pre req's which i should keep in mind ?.
View 1 Replies View Relatedcreating the standby database from Active database using RMAN and getting the below issue after i executed the duplicate command.
Version of Database:11g(11.2.0.1.0)
Operating System:Linux 5
Error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/21/2012 17:26:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
[code]....
provide any work arounds to proceed further in creating the standby database.
I have installed oracle 11g standard edition one and created both primary and standby database. now i want to know how to switch(convert) primary database to standby database.
View 8 Replies View Related