|
 |
| Recent blog posts: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Friday, 11 August 2006
|
| Compare a table to itself Newkid 04:10:09 |
| | Hi there!
Please guide me how to compare a table to itself. Problem is like this:
There is a service and for each service there are many templates. Each
template has many stages ( standard and customs). There are two
milestones say A and B. Now, one needs to compare for a particular
service and particular template how many stages A has which B hasn't
and vice versa or both has same stages.
So far, I extracted all the information and create a view. Now my
concern is how to compare view to itself and define 'Y' and 'N' under
respective position.
Beside this one needs to take care couple of conditions:
a) If stage id = stage id and stage dependecy = stage dependency then
display a single row with both Y Y (means, both has same stage)
b) If stage id = stage id and stage dependecy NOT EQUAL to stage
dependency then display two rows i,e, Y/N and N/Y
For instance, VIEW for your reference:
CREATE OR REPLACE FORCE VIEW "ROMAX"."TEMPLATE_NEG" ("RDT_RO_ID",
"RDT_PARTNER_RO_ID", "RDT_RS_ID", "RDT_RDS_ID", "RDT_STAGE_ORDER",
"RDT_DEPENDENCY", "RDT_REC_INTERVAL", "RDT_ACTION_BY", "RDS_NAME",
"RDS_RO_ID") AS
Select
"RDT_RO_ID","RDT_PARTNER_RO_ID","RDT_RS_ID","RDT_RDS_ID","RDT_STAGE_ORDER","RDT_DEPENDENCY","RDT_REC_INTERVAL","RDT_ACTION_BY","RDS_NAME","RDS_RO_ID"
from (Select A.RDT_RO_ID,A.RDT_PARTNER_RO_ID,
A.RDT_RS_ID,A.RDT_RDS_ID,A.RDT_STAGE_ORDER,A.RDT_DEPENDENCY,
A.RDT_REC_INTERVAL,A.RDT_ACTION_BY,
B.RDS_NAME,B.RDS_RO_ID from ROMAX_DELIVERY_TEMPLATE A left join
ROMAX_DELIVERY_STAGE B
on A.RDT_RDS_ID = B.RDS_ID) order by RDT_Stage_order;
Hope I'm making some sense...
Would appreciate prompt response!
Shyam
|
| |  8 answers | Add comment |
|
| oracle 10GR2 EM console how to set up Teresa 04:10:08 |
| | Hello
I have installed oracle 10GR2 on solaris 5.8 and when I created the
database I used a script, therefore the sysman schema was not created
and I don't have the nice GUI interface http://host:1158/em/console
Is there a way of adding this now to the database? If so how to do it?
Many thanks!
Teresa
|
| | 3 answer | Add comment |
|
| select record type Anil G 03:40:58 |
| | I have package specification having TYPE as
TYPE some_rec IS RECORD(
col1 varchar2(100),
col2 varchar2(100)
);
TYPE some_ntbl IS TABLE OF some_rec;
..........
..........
And i have Package Body some function trying to do following:
v_some_ntbl some_ntbl := som_ntbl();
....
SELECT some_rec(
tbl.col1,
tbl.col2
)
BUILK COLLECT INTO v_some_ntbl
FROM tableCol tbl
Some how oracle does not recognize record types, I am aware that this
can be done with Pure Object types, but those requires additional
maintainance.
Let me know,
Thanksm
Anil G
|
| | 1 answer | Add comment |
|
| Grouping / totalling on field change Ture Magnusson 01:08:53 |
| | Hello all! I cannot figure out if/how I can do this with an SQL query.
I would like a result which totals the weight for each quality whenever
the quality changes (based on the order of the ID field).
Original data:
ID, QUALITY, WEIGHT
1, A, 10
2, A, 11
3, A, 12
4, B, 11
5, B, 19
6, A, 9
7, A, 14
8, C, 4
9, C, 7
Desired result:
ENDID, QUALITY, WEIGHT
3, A, 34
5, B, 30
7, A, 23
9, C, 11
Best regards,
Ture Magnusson
Karlstad, Sweden
|
| | 7 answers | Add comment |
|
| session wait on "control file sequential read" event ,performance was very slowed Machao 01:05:23 |
| | OS version :hpux 11i
RDBMS Version :9.2.0.6
I haved a application.
it inserted data to a very large table,there was 1000+ partition in
this table.
the speed was very slowed.
I use "select sid,event from v$session_wait",
It was control file sequential read event.
i run the application on another db,there was a same table ,but the
table is smaller, it only has 100+ partition.
the speed was very fast.
what happend?
what can i do?
think you very much.
|
| | 4 answer | Add comment |
|
| Performance Courses Don 00:47:46 |
| | I am looking for a good instructor led Oracle performance tuning
course. Does anyone have any experience with the Oracle courses,
OraPub (Craig Shallahamer), or others? I'm a development database
engineer that would like to watch an application run and be able to
understand what Oracle is doing, if it is performing appropriately, and
how I might tune it. I fully understand tuning at the SQL level but
not at the instance level...i guess this is sometimes called 'reactive
tuning' but i'm not looking at it from a DBA perspective.
Thanks!
|
| | 7 answers | Add comment |
|
| Overall executions at DB level Spendius 00:27:28 |
| | Hi,
Do you think doingSQL> SELECT SUM(executions) FROM V$SQL[AREA]
at regular intervals relevantly reflects the number of total
SQL executions whatsoever in your database ?
(I'm not sure as I've noticed sometimes that substracting
a value from the next one returns a negative number
- guess it's because in the meantime Oracle rid V$SQL
of a few statements...)
Thanks.
Spendius
|
| | 7 answers | Add comment |
Thursday, 10 August 2006
|
| PLSQL breaks Re: what is the SQL equivalent of 'continue' and 'break' in C ? Ed Prochak 23:06:39 |
| |
happyardy@gmail.com wrote:
[]> Daniel,> I try to stay away from GOTO. GOTOs are not evils and are helpful> sometimes but still I dont like to use them. I avoid them as much as> possible.> I was trying to find out if SQL has any keyword that is equivalent of> 'continue' in C. C has GOTO too and I have always wanted to avoid that.> thanks> - Ardy
you keep asking about SQL, but discussing procedural programming.
keep in mind
SQL is a NON-procedural language. break and continue make no sense in a
language without loop constructs.
PL/SQL is a procedural language that include SQL statements. Rather
than using a C programming mentality, learn to program in PL/SQL (It's
very similar to ADA). PL/SQL uses the EXCEPTION model to provide
structured programming. It is actually very nice. Learn that model
rather than thinking in C and trying to emulate CONTINUE commands.
Also you might avoid the continue simply by reversing the result of
your if condition.
in C where you have
if ( condition) continue;
other statements to skip when condition is true
consider using
if (! condition )
{ other statements to skip when condition is true }
no need for the continue.
Trust me. While C and PL/SQL are both procedural languages, they do
have different programming models. You will be best served by learning
PL/SQL model as if it is unrelated to C.
HTH,
ed
|
| | Add comment |
|
| Trigger and Job (DDL) Florian Eberl 22:11:37 |
| | Hi@all,
I have the following problem:
After an insert on my table a trigger calls some procedures calculating a
value. If this is below a given value, an email is sent using the
smtp-package. Everything is working fine so far.
But in some rare cases, there's a very short time (some seconds) between
two insert-statement, which causes an email is sent and a moment later the
value is ok again (e.g. value= -2 => email, after the next insert two sec
later, value = +3 => no email!)
How can I delay the procedure testing the value for some 20 seconds or so?
I tried already the to DBMS_LOCK.SLEEP ( 20) but this changed nothing except
the whole thing is finished 20 secs later.
Next idea was let the trigger create a one-time-job an start this 20 secs
later => is not working, because a trigger is not allowed to execute a DDL -
Statement, right?
Does anybody has got an idea how to find a workaround for this problem?
Thanx
flo
|
| | 2 answer | Add comment |
|
| Reading the first bytes from a BLOB and convert to String Eriben 20:59:45 |
| | Aloha,
I am trying to create a PL/SQL procedure to recognize non-supported
filetypes that is stored as a BLOB in my database; more accurately it
is a ORDSys.ORDImage.
I want to do this by reading the first 1000 bytes of the file and then
do an INSTR after the occurence of a pattern, such as 'JFIF' or in my
case 'DICM' and 'MATRIX7'.
Does anyone have a best-practice for this kind of operation?
Kindly,
Erik Bengtsson
|
| | 1 answer | Add comment |
|
| Oracle Files - Index OCR'd PDF documents Ed 20:43:50 |
| | Is there a way to index Adobe PDF documents that where scanned in and
then the command "recognize text using OCR" used. Microsoft indexing
can be modified to index the layer created by the recognize text
command through installing Adobe iFilter
(http://www.adobe.com/support/downloads/detail.jsp?ftpID=2611). Is
there a similar procedure that can be done to do so in Oracle Files? We
are running Oracle Collaboration Suite v9.0.4.1.1, Oracle Files
v9.0.4.3 w/ patch v9.0.4.511 and patch v9.0.4.56.
|
| | Add comment |
|
| do disaster recovery without pfile Peter 20:15:39 |
| | Hello DBAs,
I took backup last night of a production database which included all
datafile with user managed backup and controlfile to trace. I didn't
take any pfile backup. Now I have to do disaster recovery because all
datafiles,controlfiles,redo log files,pfile and spfile are lost. I just
have backup and archive log files. Please help to recover database when
I don't have pfile backup because I can't assume everything of pfile
again.
|
| | 2 answer | Add comment |
|
| Need help with query which runs faster in SQL than ORACLE Faisal.Mansoor@Gmail.Com 20:05:01 |
| | Hello All
I am having performance problems with the following query. For the
following data
TABLE: UNION_TEST
user_name, user_id
A 1
A 2
A 3
B 2
B 4
For retrieving all rows for user A and those rows of user B for which
user A does not have a user_id. I have written following query for
this.
select * from UNION_TEST WHERE USER_NAME = 'A'
UNION
select * from UNION_TEST WHERE USER_NAME = 'B' AND USER_ID NOT IN
(SELECT USER_ID FROM UNION_TEST WHERE
USER_NAME = 'A')
which produces the following desired output.
A 1
A 2
A 3
B 4
But for large data set this query is running very slow.
//Create table
create table UNION_TEST
(
USER_NAME VARCHAR2(50),
USER_ID NUMBER(22)
);
//insert 30000 rows for A
BEGIN
FOR i in 1 .. 30000
LOOP
insert into UNION_TEST VALUES('A', i);
end LOOP;
END;
//RUN
select * from UNION_TEST WHERE USER_NAME = 'A'
UNION
select * from UNION_TEST WHERE USER_NAME = 'B' AND USER_ID NOT IN
(SELECT USER_ID FROM UNION_TEST WHERE
USER_NAME = 'A')
Even now the query runs in resonable time. But if I ran this query
UPDATE UNION_TEST SET USER_NAME = 'B'
Now this query takes more than 30 min to execute.
For SQL Server both scenarios take alomst same time.
Can any please explain this problem and recommend how can rewrite the
query to optimize it.
Thanks
Faisal
|
| | 6 answers | Add comment |
|
| Disaster recovery Bencio 19:34:00 |
| | Hi all !!!
Excuse me for my bad english !!!
I'm doing a disaster recovery frmo a server that i need to install a
new Windows Server 2003 because of corruption.
I have backup with autobackup of the controlfile.
I create a new installation of Oracle Server with the same path tree of
the original server.
Now i'm trying to restore database.
I've allready restore spfiles and controlfiles.
Now i'm tring to restore all database but i encounter an error like
this (this is my RMAN script and the result):
RMAN> run2> {3> allocate channel Channel1 type disk format
'E:\backup\setBCK\b_%u_%p_%c';4> set until SCN 186529587;5> restore database;6> recover database;7> }
allocated channel: Channel1
channel Channel1: sid=11 devtype=DISK
executing command: SET until clause
Starting restore at 10-AUG-06
channel Channel1: starting datafile backupset restore
channel Channel1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\ORADATA\EPEWEB\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\ORADATA\EPEWEB\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\ORADATA\EPEWEB\CWMLITE01.DBF
restoring datafile 00004 to C:\ORACLE\ORADATA\EPEWEB\DRSYS01.DBF
restoring datafile 00005 to C:\ORACLE\ORADATA\EPEWEB\EXAMPLE01.DBF
restoring datafile 00006 to C:\ORACLE\ORADATA\EPEWEB\INDX01.DBF
restoring datafile 00007 to C:\ORACLE\ORADATA\EPEWEB\ODM01.DBF
restoring datafile 00008 to C:\ORACLE\ORADATA\EPEWEB\TOOLS01.DBF
restoring datafile 00009 to C:\ORACLE\ORADATA\EPEWEB\USERS01.DBF
restoring datafile 00010 to C:\ORACLE\ORADATA\EPEWEB\XDB01.DBF
restoring datafile 00011 to C:\ORACLE\ORADATA\EPEWEB\EPE.ORA
restoring datafile 00012 to C:\ORACLE\ORADATA\EPEWEB\ECY2K.ORA
restoring datafile 00013 to C:\ORACLE\ORADATA\EPEWEB\FIERI.ORA
restoring datafile 00014 to C:\ORACLE\ORADATA\EPEWEB\DCS.ORA
restoring datafile 00016 to C:\ORACLE\ORADATA\EPEWEB\FIERI_NDX.ORA
restoring datafile 00017 to C:\ORACLE\ORADATA\EPEWEB\FIERI_PK.ORA
restoring datafile 00018 to C:\ORACLE\ORADATA\EPEWEB\EPE_NDX.ORA
restoring datafile 00019 to C:\ORACLE\ORADATA\EPEWEB\EPE_PK.ORA
restoring datafile 00020 to C:\ORACLE\ORADATA\EPEWEB\FLT.ORA
restoring datafile 00021 to C:\ORACLE\ORADATA\EPEWEB\FLT_NDX.ORA
restoring datafile 00022 to C:\ORACLE\ORADATA\EPEWEB\FLT_PK.ORA
restoring datafile 00023 to C:\ORACLE\ORADATA\EPEWEB\EVENT_NDX.ORA
released channel: Channel1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/10/2006 12:16:51
ORA-19505: failed to identify file "E:\BACKUP\SETBCK\B_44HQCGUN_1_1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
-------------------------------------------------------------------------------------------------------------------------
I verify the presence of the file "E:\BACKUP\SETBCK\B_44HQCGUN_1_1". It
exist in the precise path !!
I verify also the permission and that's ok.
I don't know what to do !!!
I have in the directory where i stored the backup, two different
backupset.
If i try to restore UNTIL one of them, RMAN indicates that it's unable
to open the other file and viceversa !!
Someone can help me ??
Thanks a lot !!
|
| | 4 answer | Add comment |
|
| Calculation of remaining space in LMT's... Spendius 18:32:43 |
| | Hi,
In 10g is there no other way to get info about remaining
room in locally managed tablespaces (with a calculation
*taking in account* the eventual AUTOEXTEND mode of
datafiles) than using DBMS_SERVER_ALERT procedures
and then SELECTs against DBA_ALERT_HISTORY ?
There are unfortunately no such views as
V$TEMP_SPACE_HEADER and
V$TEMP_EXTENT_MAP (that underlyingly query X$KTFTHC
and X$KTFTME structures) for regular tablespace datafiles...
By the way does running SELECTs against these views
cause Oracle's reads in temp. files space/bitmap headers ?
Thanks.
|
| | 2 answer | Add comment |
|
| Is there a way to delete a corrupted ASM diskgroup? Pooka 17:00:33 |
| | All,
I have an asm diskgroup that was corrupted by faulty hardware. I'd like to
remove it, and re-initialize it so I can restore the backup and get the
database back on line. I've tried to delete it, but unless it is mounted I
can't delete it.
Thanks,
Patrick aka pooka
|
| | 3 answer | Add comment |
|
| OEM Console Sabeer Oracle 14:53:45 |
| | I can log in to OEM with standalone mode.
How can I log in to OEM with Oracle management server? How do i set up
management server?
Thanks
|
| | 4 answer | Add comment |
|
| installing 9.2.0.4 x86_x64 on RedHat 4.0 NetComrade 14:15:22 |
| | I have followed instrallation instructions from this page:
http://download-uk.oracle.com/docs/html/B13670_06/toc.htm#BABIJIFJ
2 things
1) this is a top of the line AMD chip machine (v40z)
download of 3 disks took about 3 mins
uncompress took about 15 seconds each
install took _HOURS_.. i gave up and went to sleep
2) 2 things failed to link
isqlplus, and I believe the database itself. I was forced to ignore
them. (i believe the relink command can fix these). The installer
didn't complain about any packages missing.
Exception String: Error in invoking target install_isqlplus of
makefile /u81/app/oracle/product/920/sqlp
lus/lib/ins_sqlplus.mk
xception String: Error in invoking target install of makefile
/u81/app/oracle/product/920/rdbms/lib/ins
_rdbms.mk
I get the following warnings when trying to relink the client:
/usr/bin/ld: skipping incompatible
/usr/lib/gcc-lib/i386-redhat-linux/2.96/libgcc.a when searching for
-lgcc
/usr/bin/ld: skipping incompatible
/usr/lib/gcc-lib/i386-redhat-linux/2.96/libgcc.a when searching for
-lgcc
/usr/bin/ld: warning: i386 architecture of input file
`/usr/lib/gcc-lib/i386-redhat-linux/2.96/crtbegin.o' is incompatible
with i386:x86-64 output
/usr/bin/ld: warning: i386 architecture of input file
`/usr/lib/gcc-lib/i386-redhat-linux/2.96/crtend.o' is incompatible
with i386:x86-64 output
|
| | 4 answer | Add comment |
|
| TEMP tablespace Airmax 05:10:25 |
| |
Hello DBA’s
I have a problem with my TEMP tablespace. It’s 1 GB and its full.
Shouldn’t be empty? When the tablespace is full i have to restart the
database in order clean up the tablespace, but this is not a
solution!!! I don’t want to extend it becuase it will be full again. In
my previous job the table space was 800MB and it was always empty.
What I should do?
Regards
Panos
--
airmax
------------------------------------------------------------------------
airmax's Profile: http://www.dbtalk.net/m627
View this thread: http://www.dbtalk.net/t324583
|
| | 1 answer | Add comment |
|
| EXCEPTION INTO table storage Guest 02:10:43 |
| | Can someone point me to location of where Oracle stores reference table
for EXCEPTION INTO constraint option.
I have the following contstraint:
ALTER TABLE MY_Table ADD
CONSTRAINT MY_Table_UQ UNIQUE (I1)
DEFERRABLE
INITIALLY IMMEDIATE ENABLE VALIDATE
EXCEPTIONS INTO MY_Table_EXCEPTIONS
I was not able to find dependence reference between MY_TABLE and
MY_TABLE_EXCEPTIONS. I have checked ALL_CONSTRAINTS, ALL_DEPENDENCIES,
ALL_TABLES
Oracle 9.2.0.7
|
| | 1 answer | Add comment |
|
| create dynamic variables Mgmonzon 01:12:12 |
| | Hi Everybody,
Is posible to create dynamic variables in a PL/SQL procedure?
This is my problem, I have a table called TABLE_VARIABLE with this
values :
#VAR_NAMEVAR_VALUE
------- --------------- ------------------------
1SALARY1000
2DISCOUNT300
3NET(SALARY - DISCOUNT)
I want create a procedure where the variables names should be the same
name defined in the table TABLE_VARIABLE.
The user is able to create ne records and new variables names an the
names should be processed like a pl/sql variables in order to store
the result information in other table.
Sample :
Create or replace read_variables as
Begin
for reg in (select VAR_NAME, VAR_VALUE from TABLE_VARIABLE ) loop
<create variable defined in the field VAR_NAME in TABLE_VARIABLE> ;
<Assign value using the field VAR_VALUE in TABLE_VARIABLE> ;
End Loop ;
End ;
Is possible do that?
Mgmonzon
|
| | 9 answers | Add comment |
|
| to_char number format with optional decimal-point? Martin T. 00:14:11 |
| | Hello all!
Basically, what I want is the following.
Num -> Str
1 -> '1'
10 -> '10'
1.01 -> '1.01'
Is there a possible Number Format Model that will not display the
decimal if there are no decimal digits?
(I know this is the default behaviour of to_char, but I need aditional
formatting, so I need to spec the format model.)
Related to this: Is it possible to specify a number-format model that
will exactly reproduce the to_char default behaviour?
thanks!
best,
Martin
|
| | 7 answers | Add comment |
|
|
