i ahave a table with below
01-oct-12,ARTACT001,Memory
04-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
05-oct-12,ARTBRIO,CPU
Required output as below which dates are not available my table
02-oct-12,ARTACT001,Memory
03-oct-12,ARTACT001,Memory
01-oct-12,ARTACT001,OS
02-oct-12,ARTACT001,OS
[Code]...
Database version
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for HPUX: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
I have this query
select dept_id,qc_subtype_id,equip_code,drive_id
from
(select distinct dept_id,
decode(qc_subtype_id,
[code]...
My requirement is to find the closest date from col B(a.p_date) to the dates in col A(d.p_date). I got the following which works great:
SELECT
d.p_no_s,
d.p_date,
MIN(a.p_date),
MIN(a.p_date)-d.p_date||' Number of Days' NUM_OF_DAYS
[Code]...
Output:
p_no_s p_date MIN(a.p_date) MIN(a.p_date)-d.p
-------------------- ---------- ---------- -------------------------------------------------------
Z1575560 15/06/2008 29/07/2008 44 Number of Days
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008 27/12/2008 36 Number of Days
Z1575560 17/12/2008 27/12/2008 10 Number of Days
Problem:
For 1st and 2nd row,
I am getting 29/07/2008 as a.p_date for both 15/06/2008 and 15/07/2008 which is wrong in my scenario. This is because data is missing in the second table for row 1 (similarly for row 3). So What I want is :
Z1575560 15/06/2008
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008
Z1575560 17/12/2008 27/12/2008 10 Number of Days
Say for eg: a person is in city A. He is departing on (15/06/2008 ) and arriving on 29/07/2008 and again departing on 15/07/2008 which is not possible.
It should be departing on (15/06/2008 ) and arriving between 15/06/2008 and 15/07/2008 (missing data hence null is required here) and departing on 15/07/2008 and arriving on 29/07/2008 .
let me know how to achieve this.
I have a procedure called SANDEEP_INC105657, which calls another procedure called "Send_Prodcontrol_Mail_Pr". This procedure should be sending out an email with the following message:
From: <sandeep.tanjore@xxx.xx.ca>
Subject: Remit Advice Slips Report (TR000336).
To: sandeep.tanjore@xxx.xx.ca
Report TR000336 - Remit Advice Slips.
Total number of records that needs slips printed are: 1
However what's happening is we had a database change from 10g to 11g and ever since this has happened the email is sent with "From", "Subject" and "To" but the body(content of the email : Total number of records that needs slips printed are: 1) is missing.The code in Send_prodcontrol_Mail_pr is as follows:
CREATE OR REPLACE PROCEDURE Send_Prodcontrol_Mail_Pr( v_mailhost IN VARCHAR2,
v_sender IN VARCHAR2,
v_recipient IN VARCHAR2,
v_subject IN VARCHAR2,
v_message1 IN VARCHAR2,
v_message2 IN VARCHAR2,
v_message3 IN VARCHAR2)
[code]....
Any reason why the body of the email is missing? I tried the following:
replaced utl_smtp.data(mail_conn,mesg); with utl_smtp.write_data(mail_conn, utl_tcp.CRLF||mesg);
It sends out the email as required but it repeats the "From" two times with no "TO", and "Subject"... however then embeds the whole "from" "to" and the body in an email.
SELECT c.table_name CHILD_TABLE, p.table_name PARENT_TABLE
FROM user_constraints p, user_constraints c
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.constraint_name = c.r_constraint_name
AND c.table_name = UPPER('ODS_TSAF_MES_PC');
and output is
child table parent table
ODS_TSAF_MES_PC ODS_TSAF_MES_PCTYP
ODS_TSAF_MES_PC ODS_TSAF_MES_PC
ODS_TSAF_MES_PC ODS_TSAF_MES_PCSTAT
i tried
SELECT A.piecestatus from ods_TSAF_MES_PCSTAT A WHERE NOT EXISTS
(SELECT * FROM ODS_TSAF_MES_PC B WHERE B.piecestatus = A.piecestatus);
and i found one piecestatus values is 'I' but i am not getting where it is related to the table and in which row it is getting affected?
As data completeness, data consistency, data integrity are some of features of good quality data. What is best approach and best practice to deal with missing or null values in database? I am facing such type of situation. In a main table, there are several columns have null or missing values.
Get rid of null or missing values. Should I consult source documents from where data was fed into database to achieve maximum data completeness?
While Configuring Data Guard for ORacle 10g (10.2.0.4) 64 bits on Windows 2007 Server 64 bits.I got few questions
1. What is the Default mode of Standby Database?
2. Should we Always Start Physical Standby Database to Recover Missing Redo Archive Log?
SQL> startup mount;
ORACLE instance started.
Total System Global Area 591396864 bytes
Fixed Size 2067496 bytes
Variable Size 163578840 bytes
Database Buffers 419430400 bytes
Redo Buffers 6320128 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
3. When there are missing Redo Log Archives e.g.
----On Standby Database--------
SQL> SELECT RESETLOGS_ID,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
2 ORDER BY RESETLOGS_ID,SEQUENCE#;
RESETLOGS_ID SEQUENCE# S ARC
------------ ---------- - ---
812980008 15 A YES
812980008 16 A YES
812980008 17 A YES
812980008 18 A YES
[code]....
65 rows selected. Log 8, 9, 10, 11, 12, 13, 14, 15 are missing.
How to Apply / Recover These Logs on Standby Database?
I did the datapump export and import from one schema to a new schema in the same database. I had to use different tablespace. I used the following parameters in the parfiles :
export parfile
directory
dumpfile
logfile
parallel
import parfile
directory
dumpfile
logfile
parallel
remap_schema
remap_tablespace
Tell me whether I need to use different parameters than the one I used? Can I use both remap_schema and remap_tablespace at a time?
I performed a switchover test of my Exadata databases last night. Both databases are running 11.2.0.2 (BP7) on top of GI of the same version.I'm using Data Guard Broker to administer the Data Guard configuration.
I have, as you'd expect, the standby_file_management set to AUTO, so any file changes/additions/deletions that are made on Primary should be applied to Standby also.And they have been. Until last night.
When I had switched over to running Primary on the Standby site, I got this error message:
Tue Jun 04 22:27:12 2013
Errors in file /u01/app/oracle/diag/rdbms/exdw1pdg/exdw1pdg1/trace/exdw1pdg1_ora_26630.trc:
ORA-25153: Temporary Tablespace is Empty
I checked and my two temp tablespaces existed, but had no files in them. These files are 200Gb and 448Gb in size, so you'd think you'd notice them going missing. This wasn't by any means the first time we switched over (and, yes, I did create temp files for Standby when I built it and first switched over)
We've switched over to Standby multiple times and even ran a whole day's processing against it and haven't seen this. Ultimately, it wasn't a big deal, because I just created a tempfile for each of the tablespaces and off we went.Nothing in MOS seems to mention something like this. Basically, it looks like the switchover process decided to eat my tempfiles but keep my temp tablespace defintion. Odd.
OS: Solaris
DB: Oracle 11.2.0.1 EE
Not Using ASM or RAC
I have a Production database that is in archivelog mode and a Standby DR server. Both servers (Prod, Standby) have exact same structure and db name/version.
We manually scp archive logs and recover them to a manual standby database via SQL Scripts "cron". (I.E. set autorecovery on; recover standby database;)
We recently got out of sync with our log files and have not been applying them to the standby. As part of Prod Maintenance, these log files were deleted and are not available anymore.
I've tried several ways to "rebuild" our standby database. I have tried to Shutdown prod, backup all the db files and scp them to standby, re-create standby controlfile and startup mount and recover standby. Every time I try to apply a new archive log via recover standby, these are the errors:
ORA-00279: change 211077622 generated at 1/27/2012 12:18:42 needed for thread 1
ORA-00289: suggestion : /oradump/arch/PROD/PROD_arch_1_69486_736618850.arc
ORA-00280: change 211077622 for thread 1 is in sequence #69486
ORA-00308: cannot open archived log '/oradump/arch/PROD/PROD_arch_1_69486_736618850.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradevices/PROD/oraPRODsystem1.dbf'
When I check v$log_history, the new logs have not been applied. I've also tried the "Restore from incremental backup via SCN" method with same results. Is there a way to re-create the standby clean and ensure that the log chain that is currently broken gets fixed or reset? I would eventually like to get DataGuard in here, but that's not the case at the moment.
In my database I found some anomalies like
SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....
The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.
SQL> SELECT * FROM COUNTRIES;
CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...
why the country table is missing in user_segments data dictionary table.
I am running the following query but getting an error
Select
main.dietdate,
main.dietCombinationId,
main.diet_combination_name,
main.DTYPE,
Sum(main.TOTALVAL) as SCount,
[Code]....
ERROR at line Where Dem.diet_combination_id = DESS.diet_combination_id main , mas_diet_combination mmh ORA-00907: missing right parenthesis
finding where I am getting wrong
"WORST query formatting as the above query was written (by some developers) in MySQL and being migrated to oracle"
i'm trying to create table and im getting this error all the time.
CREATE TABLE Category (
Name CHAR(20) NOT NULL,
CategoryId INTEGER UNSIGNED NOT NULL,
RRO DOUBLE NULL,
LRO DOUBLE NULL,
IHRRO DOUBLE NULL,
PRIMARY KEY(Name, CategoryId)
);
My query
SELECT node_nme, os_nme, os_vrsn_nme, CPU_QTY, mch_id_nme
FROM node_t
WHERE node_nme IN
(
SELECT DISTINCT upper(node_nme) as node_nme FROM node_t WHERE row_status_cde IN (1,4)
[code]...
Now, when I run this i get the missing parenthesis error, though I don't konw why.The subselect in the IN works fine and returns exactly what it should.
I have been looking at this now for a good 2 hours. It seems like a simple error but I just cant see it. I must of counted the brackets 20 times. I have run this code in SQL developer using actual values and it works fine.
If your wondering about the esig class it just runs the sql with a few other SQL statements. I have changed the code below to a SELECT * statement just so see if it was actually that statement that was the problem. The select worked so it must be this.
Original
- oracle Code
$esig->setSQL("INSERT INTO document_ecopy
(DOCUMENT_ECOPY_ID
,DOCUMENT_UUID
[Code].....
i´ve got a table with colomns (time, value) ... for example:
time value
10:00 10
11:00 20
12:00 25
13:00 28
15:00 31
17:00 38
you can see, that the row 14:00 and 17:00 are missing. How i can create the missing values? The value should be the value from the last point. The result should be this:
time value
10:00 10
11:00 20
12:00 25
13:00 28
14:00 28
15:00 31
16:00 31
17:00 38
How can i do this ?
Ten thousand (10000) records ,but in the TABLE that has a uniqure sequence of column named seq, find only 9 thousand (9000) records. I want to find out the missing records. The following is my approach, but it is not the best way
1.CREATE TABLE my_public_seq (seq number(10));
2.
DECLARE
vn_seq NUMBER(10);
BEGIN
FOR i IN 1..10000
LOOP
INSERT INTO my_public_seq VALUES(i);
END LOOP;
COMMIT;
END;
3. run sql
SELECT seq FROM my_public_seq
MINUS
SELECT seq FROM my_original_table_that_missing_some_records;
How to get the missing records using only one SQL statement.
create table t (
number_from number,
number_to number);
insert into t values(1,3);
insert into t values(5,9);
insert into t values(10,15);
commit;
I need to create a stored function that could find/return missing numbers between number_from and number_to for each record in table t.
For example:
Number_fromNumber_to missing
13
594 from previous record, this record is supposed to start from 4
I have following query. I checked manytime but I am unable to find the reason of error Ora-00907
SELECT Working_Days(sdate,edate),
Ddecode(Physical_files Between 1 And 500 And Working_Days(sdate,edate) Between 1 And 30,-1,
decode(Physical_files Between 501 And 3000 And Working_Days(sdate,edate) Between 1 And 180,-1,0)) col2
FROM table1 INNER JOIN table2
ON table1_Sno=table2_Sno
In this query, working_days is function that return working days.
Im running the following code and receiving 'error ORA-00907: missing right parenthesis' when I try and run the
to_char(((FAF_CALC_CCONTRIB (FA_ASSESSMENT.IDENTIFIER,'C'/7)*(TRUNC(TO_DATE(CPC_PROVISIONS.ACTUAL_END_DATE,'DD-MON-YY') - TO_DATE(CPC_PROVISIONS.ACTUAL_START_DATE,'DD-MON-YY')))),'FM9999990.00'),
If I remove this line then the SQL runs without problems. I think it's the first part FAF_CALC_CCONTRIB (FA_ASSESSMENT.IDENTIFIER,'C'thats causing the problems because I am using near identical code in another script which works perfectly.
im trying to take a query that pulls all the info in the current tables, and narrow it down to one day plus current. here is the error i am getting.
(SELECT OH.ORD_CNTRL_NO, OH.ORD_NO, OH.ASSGN_SHIP_DATE, oh.account_no, sum(OL.ORD_QTY) AS ORD_QTY
*
ERROR at line 5:
ORA-00936: missing expression
old query:
select a.assign_no, a.assign_type, a.act_end, X.ord_no, x.account_no,
X.ASSGN_SHIP_DATE, X.ORD_QTY, td.from_loc, sum(td.act_qty) AS ACTQTY, sum(td.exp_qty) AS EXPQTY
from clsd_assign a, clsd_task t, clsd_task_detail td, sku s,
(SELECT OH.ORD_CNTRL_NO, OH.ORD_NO, OH.ASSGN_SHIP_DATE, oh.account_no, sum(OL.ORD_QTY) AS ORD_QTY
[code]...
new query
select a.assign_no, a.assign_type, TRUNC(A.ACT_END), to_char(a.act_end), X.ord_no, x.account_no,
X.ASSGN_SHIP_DATE, X.ORD_QTY, td.from_loc, sum(td.act_qty) AS ACTQTY, sum(td.exp_qty) AS EXPQTY
from clsd_assign a, clsd_task t, clsd_task_detail td, sku s
where a.act_end between to_char(sysdate -1, 'dd-mon-yyyy') and to_char(sysdate, 'dd-mon-yyyy')
[code]...
i am posting create and insert statements for a problem i am facing
create table tgh(NAME VARCHAR(20),LANG VARCHAR(20))
insert into tgh values('AC','ORIYA')
insert into tgh values('DF','BENGALI')
insert into tgh values('ik','english')
the below statements are for table copu
create table copu(CNAME VARCHAR(20),CLANG VARCHAR(20))
insert into copu values('ACdf','oriya')
insert into copu values('deg','ptot')
when i do another insert as shown below
insert into copu (cname,clang) values where
copu.cname not exists in( select tgh.name,tgh.lang from tgh where upper(tgh.name)=upper(copu.cname))
i get ora-00936 missing expression
CREATE TABLE titledds
( Title_Id NUMBER(4) PRIMARY KEY
TName VARCHAR2(20) NOT NULL UNIQUE
Price NUMBER(5)
Discount NUMBER(2)
TextCat VARCHAR2(5)
);
wen i enter this im getting an error @ TName VARCHAR2(20) NOT NULL UNIQUE
ORA-00907: MISSING RIGHT PARENTHESIS
im using 11g 2nd release..
In one of my table i found some rows missing, how can i recover it ?????
View 4 Replies View RelatedCurrently my Source Database capture state stream is "Waiting for Dictionary Redo FIRST SCN" some logfiles are missing. My problem now is how to recover those missing logfiles.
View 1 Replies View RelatedI have a table named tbl_student which contains 2226 rows have a column student_code of datatype number which contains student number in serial order from 1 to 2226.
I want to know what numbers between 1 to 2226 are missing.Is there a way in SQL by which I can query the above table for the desired result. There were 2246 records in all which means 20 unknown numbers (between 1 and 2226) are missing. No records have been deleted.
i want to create an report which has all missing invoice no (e.g November invoice posted were from 1000001 to 1000100 how do we check if all the invoices has been entered)
Table INVOICE_MASTER
Column NAMe
Invoice no
Invoice date
Invoice Type
How can i create that report.
CREATE TABLE LOCATION(
Locid NUMBER (5) LOCATION_LOCID_PRIMARY KEY,
Bldg_Code VARCHAR2 (10) NOT NULL,
Room VARCHAR2 (6) NOT NULL,
Capacity NUMBER (5));
CREATE TABLE FALCULTY(
[code]...
I am trying to execute the following query
SELECT inst_id, SID, serial#, module,username, machine, action, logon_time,status,event FROM gv$session
WHERE SID in
(
SELECT sid,id1,request FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request
)
And I received the following error message
ORA-00907: missing right parenthesis
But when I execute
SELECT inst_id, SID, serial#, module,username, machine, action, logon_time,status,event FROM gv$session
and
SELECT sid,id1,request FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0) ORDER BY id1, request
separately. It works fine. Why is it erroring out when I include the query in WHERE clause. Rather than the work around, I would like to know the reason for the need of right paranthesis.
I issued the command
grant select(employee_id, job_id) on employees to scott;
But it returns error as
HR:orcl > grant select(employee_id, job_id) on employees to classuser;
select(employee_id, job_id) on employees to classuser
*
ERROR at line 1:
ORA-00969: missing ON keyword