SQL & PL/SQL :: Write A Statement That Shows Time / Name / IP / Received And Sent
Jan 17, 2013
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)
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.
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 have Oracle 10g. In a table with just one record there is a clob with the following xml. In the following clob, there could be any number of Emp elements.
I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order" FROM customers INNER JOIN orders ON customers.customer_id=orders.customer_id LEFT JOIN order_details ON orders.order_id=order_details.order_id LEFT JOIN items ON order_details.item_id=items.item_id ORDER BY Customer_name
If I like to identify the executed time of a particular SQL Statement, beside v$sql, is there any other dictionary or lookup table that have this piece of information?
Why v$sql is not sufficient, because this is a recurrsive update statement which is regularly called, and thus the last_load_time is overwritten.
My archivelog had been purged due to our scheduled backup.
Is there any other way to identify when the particular SQL statement is executed?
The code written in PL SQL.I am using '&' operator to enter the values at run time in nested case statements.But when I chosen a value for case statement even though it is asking all the values which are not part of the selected case statement.I need only the values related to my choice in case statement.
choice1:= &CHOICE_NO1; CASE(choice1) WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('** ENTER YOUR CHOICE TO PERFORM THE OPERATION: **'); DBMS_OUTPUT.PUT_LINE('** (1)INSERTION (2)DELETION (3) UPDATION **'); [code]....
that is my exact code.when I enter value 1 for choice1 and 2 for choice2 It should ask the values only for student_ scorecard. delete_ record (& STUDENTID); &studentid. But it is asking all the functions (student_scorecard.existing_marks(&ID,&SUBJECTNAME));(student_ scorecard. std_ major_ name (&STD_ID)) and procedures.I dont want that.
My company use a sybase database that runs business jobs. Currently we run SQL queries from Perl to gather time information on the jobs. Now we have an application that is using Oracle. The server it is on, doesn't have perl, so I am using a shell script to login to sqlplus and run a query for a job and it's end time. I have accomplished this. However, here is the 2 problems I am having.
1. The query reults are returned in Scientific time, I'm able to convert that to EPOCH time in the SQL syntax, however, it comes back with a 13 digit time, instead of 10. The last 3 digits are zero. How can you remove the last 3 digits in the query or convert the 13 digits to Human Time. Right now when you see the select statement, I am doing a to_char to get it to EPOCH time.
2. How to only show the latest time in the query and not show ALL job end times from it's past runs.
Here is my shell script, and I do realize this maybe a select statement syntax solution to one or both, but the UNIX time stamp is puzzling.
#!/usr/bin/sh sqlplus -S username/password@JAWSPROD <<eof> myfile set heading off feedback off verify off select JAWS_APP.JAWSJOB.JOBNAME, to_char(JAWS_APP.JOBRUN.ENDTIME) from JAWS_APP.JAWSJOB, JAWS_APP.JOBRUN where JAWS_APP.JAWSJOB.JOBID = JAWS_APP.JOBRUN.JOBID and JAWS_APP.JAWSJOB.JOBNAME in ('pa_box_settle'); exit
We are firing a normal Drop command on our database and the database version is 10.2.0.4.The database is running on AIX v5.The command is taking more time than usual .
When i am monitoring the session i can see that a call is being made to procedure "aw_drop_proc".Could i ask you if this is something that is taking more time than usual.
We are not having any partitions on the nested tables .We have a pack of tables and we are dropping this pack through a procedure.The pack comprises of nested tables & normal tables.To drop a nested table it is taking around 6 seconds(Table with no rows) and a normal table(With no rows) it is taking 17 milli seconds.We have a partition on Normal table.
The same operation in windows is taking very less time when compared to AIX.
In my code I am using delete statement which is taking too much time to execute.
Statement is as follow:
DELETE FROM TRADE_ORDER_EMP_ALLOCATION T WHERE (ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT) IN (SELECT ARTEMIS_SOURCE_SYSTEM_ID,NM_ARTEMIS_SOURCE_SYSTEM,CD_BOOK_KEY,ACTIVITY_DT FROM LOAD_TRADE_ORDER WHERE IND_IS_BAD_RECORD='N');
Every column in "IN" clause and select clause is containing index on it
Every time no of rows which to be deleted is vary (May be in hundred ,thousand or hundred thousand )so that I am Unable to use "BITMAP" index on the table "LOAD_TRADE_ORDER" column "IND_IS_BAD_RECORD" though it is containing distinct record in it.
Even table "TRADE_ORDER_EMP_ALLOCATION" is containing "RANGE" PARTITION over it on the column "ARTEMIS_SOURCE_SYSTEM_ID". With this I am enclosing table scripts with Indexes and Partitions over it.
way for fast execution in of above delete statement?
After restarting the listener, we could connect using the connect identifier from the local server. However, when trying to connect from a remote client we received the error of "no listener". In the remote client the identifier is defined with host=ip_address. TNSping from the client to the host was successful.
When we changed listener.ora to host=up_address we could connect ONLY from the remote computer. Only after changing the host in tnsnames.ora file to the IP as well - we could connect from both the local server and the remote client.
I usually use host=localhost in the listener file (and the local tnsnames file) and never had this problem before.
I have a table with an index on the file_id column, defined in the same way in a test site and a production site.The only difference of these two tables is the amoun of data contained on it.the test site table contains about a million of records.the production table contains about 17 millions of records.
When I do a select on the table in test site, it returns the information in a sorted way.however, when I do the same select on the production site, the information is received in an unsorted way.
I assume that the index in production site might need to be rebuilt, because usually a big amount of data is extracted and deleted from the table and new information is inserted constantly.this situation does not occur in test site, information change is not very usual.
if rebuilt of index would actually to get the results on a sorted way, or the only way to get it is to add an "order by" statement in the query statement.
I am trying to read a message from Oracle queue using OCCI.I am getting this run time error:
ORA-24550: signal received: [si_signo=11] [si_errno=0] [si_code=2] [si_int=-389971137] [si_ptr=0x34e8c1833f] [si_addr=0x615db0] Killed.I have checked for the line that is throwing error and found below line causing it:
*messageFromQueue = cons.receive(Message::RAW);
It seems like RECEIVE function is throwing this error: Here is my code:
. I need to configure simple standby database. I have followed this[URL]...-guard-setup-11gr2.php tutorial to do that Problem is that primary db cannot log on to the standby db. Informations privided below
:Primary DB:CentOS 6.4Oracle 11gR2ORACLE_SID=primdb1SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS; MESSAGE--------------------------------------------------------------------------------ARC0: Archival startedARC1: Archival startedARC2: Archival startedARC1: Becoming the 'no FAL' ARCHARC1: Becoming the 'no SRL' ARCHARC2: Becoming the heartbeat ARCHARC1: Beginning to archive thread 1 sequence 31 (336165-356856)Error 12514 received logging on to the standbyPING[ARC2]: Heartbeat failed to connect to standby 'stbydb1'. Error is 12514.ARC1: Completed archiving thread 1 sequence 31 (336165-
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
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
I 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 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.
