|
 |
| Recent blog posts: | |
|
| They have birthday today: | |
|
| Forums: | | |
|
| Discuss: | |
|
| Recent forum topics: | |
|
| Recent forum comments: | |
|
| Модератор: | |
|
Wednesday, 2 August 2006
|
| Can a query reuse data??? Oraboy 00:29:37 |
| | I understand that data blocks are kept in the buffer cache but can
someone clarify the following
If i issue a query thats run before as well as avoiding a parse can
Oracle make use of the previous result set or does it always go back
and fetch the data from either the buffer cache or disk??
Thanks
|
| |  3 answer | Add comment |
|
| Newbie: combining two selects Tuxfan 00:12:23 |
| | I am trying to combine the following two SQL statements into
one select statement. The first column in both statements
are the same. It looks like I need a nested statement, but
I don't know how to do that.
SELECT node_data.server, COUNT ( node_data.node_name )
FROM node_data, account
WHERE node_data.node_name = account.node_name
AND acct_number = '7777'
GROUP BY node_data.server
SELECT node_data.server, COUNT ( node_data.node_name )
FROM node_data, account
WHERE node_data.node_name = account.node_name
AND acct_number = '7777'
AND lastacct < 2
GROUP BY node_data.server
|
| | 4 answer | Add comment |
Tuesday, 1 August 2006
|
| id of last inserted row? Mark Harrison 23:32:19 |
| | I've got a table with an autoincremented identifier, as
specified below (some cols truncated for brevity).
I do something like
insert into bar(name) values('mark');
and I would like to get back the id that
was just inserted for me.
How can I do that?
Many TIA!
Mark
REM FOO BAR
CREATE TABLE "FOO"."BAR"
("ID" NUMBER(20,0) NOT NULL ENABLE,
"NAME" VARCHAR2(64 BYTE) NOT NULL ENABLE,
CONSTRAINT "BAR_PK" PRIMARY KEY ("ID") ENABLE
) ;
CREATE OR REPLACE TRIGGER "FOO"."INS_BAR" BEFORE INSERT ON BAR
FOR EACH ROW
BEGIN SELECT BAR_SEQ.nextval INTO :new.ID FROM dual; END;
/
ALTER TRIGGER "FOO"."INS_BAR" ENABLE;
CREATE UNIQUE INDEX "FOO"."BAR_PK" ON "FOO"."BAR" ("ID")
;
--
Mark Harrison
Pixar Animation Studios
|
| | 2 answer | Add comment |
|
| Distinct query not working after database upgrade!! The J Man 23:16:07 |
| | Hey there,
i would really appreciate some help as this is driving me crazy!!!!
I have a query that runs over two tables. (these are simplified
versions!)
Table A AID text
---------------------------------
1 blah
2 foobar
Table B BID AID text date
-----------------------------------------------------------------
1 1 blah date x
2 1 more blah date y
3 1 even more blah date z
Simply put the query i used to run was:
SELECT DISTINCT A.AID, A.TEXT FROM A, B WHERE A.ID = B.ID AND mydate >=
BID.date.
Using Oracle 10g.1 this query used to work perfect where it would
return just one row of AID and text.
My DBA recently upgraded to Oracle 10g.2 and all hell broke loose in
the system! This query no longer works at all, and no amount of
tinkering i do i cannot get it to work. this query will now return
every row in table B that has the unique Id of table A. It is as if the
distinct clause has just stopped working.
this is driving me nuts and i would be glad if someone could help me.
Even if its to say "hey dummy, of course thats not gonna work you need
to do this" 
Thank you,
J.
|
| | 5 answers | Add comment |
|
| Not very good at SQL, trying to query multiple tables Guest 22:36:36 |
| | I know what I have and what I want but not how to get there. Can
someone suggest a SQL SELECT statement to give the desired result:
Some background info:
I have four tables which contains some data about paper mill
production.
The paper is produced on wide and long rolls of paper called REELS.
After a REEL is produced, it is re-rolled and cut into shorter and less
wide SETS of ROLLS. There is also a table called RUNMASTER which
contains some information for a production period where several REELS
are produced.
A REEL can typically be 6.7 meters wide and contain 27,222 meters of
paper.
The ROLLS from such a REEL could be:
SET 1: 10,000 meters long, 2.10, 2.10 and 2.10 meters wide. (0.4 meters
width waste)
SET 2: 10,000 meters long, 2.10, 2.10 and 2.10 meters wide. (0.4 meters
width waste)
SET 3: 7,000 meters long, 3.20 and 3.20 meters wide (0.3 meters width
waste)
(222 meters lenghth waste)
RUNMASTER
runorder
reelwidth
REELMASTER (several reels per runorder)
runorder
deptno
reelno
grade
gradeweight
reellength
SETMASTER (several sets per reel)
reelno
setno
setlength
ROLLMASTER (several rolls per set)
reelno
rollno
rollwidth
This is the result I want:
For REELMASTER.DEPTNO = 6 and REELMASTER.BEGTIME >=2006-07-01 and
REELMASTER.BEGTIME <2006-08-01
Grouped for each REELNO and SET
REELNO, SETNO, to_date(BEGTIME), GRADE, GRADEWEIGHT, REELLENGTH,
SETLENGTH, REELWIDTH, sum(ROLLWIDTH)
The resulting rows for the example reel I mentioned before would be:
123456, 1, 2006-07-01, GRADEX, 150, 27222, 10000, 6,7, 6.3
123456, 2, 2006-07-01, GRADEX, 150, 27222, 10000, 6,7, 6.3
123456, 3, 2006-07-01, GRADEX, 150, 27222, 7000, 6,7, 6.4
Best regards,
Ture Magnusson
Karlstad, Sweden
|
| | 6 answers | Add comment |
|
| RAC: crs_start Guest 22:27:04 |
| | Hi,
Oracle10g 10.2.0.2.0 + CPUJul2006 (64 bit)
RHEL AS 4.3 (64 bit)
3 node RAC cluster
Question: Using the crs_start command to start/stop services.
As per Oracle documentation.....
2) OracleВ® Database Oracle Clusterware and Oracle Real Application
Clusters
Administration and Deployment Guide
10g Release 2 (10.2)
Part Number B14197-03
Page 260 says
"Note:
Do not use the Oracle Clusterware commands crs_register, crs_profile,
crs_start
or crs_stop on resources with names beginning with the prefix ora
unless either
Oracle Support asks you to, or unless Oracle has certified you as
described in
http://metalink.oracle.com. Server Control (SRVCTL) is the correct
utility to
use on Oracle resources. You can create resources that depend on
resources that
Oracle has defined. You can also use the Oracle Clusterware commands to
inspect
the configuration and status."
What has been the experience with folks using crs_start/crs_stop?
The problem I have is that the service name and the database name is
the SAME and I am unable to stop/start the service using srvctl.
$ srvctl start service -d RAC
Service name cannot be same as the database default service name RAC
$ srvctl start service -d RAC -s "RAC" -i RAC1
PRKO-2120 : The internal database service RAC cannot be managed with
srvctl.
$ srvctl start service -d RAC -s "RAC"
PRKO-2120 : The internal database service RAC cannot be managed with
srvctl.
Unfortunately I can't use another service name at this point.
Thanks for any help.
wagen
|
| | 2 answer | Add comment |
|
| upgrade 9i to 10g --> issues with connecting as sysdba Afshin 22:09:36 |
| | Hi,
- I just installed oracle10g on solaris enviroment where there was
oracle 9i running.
- OS user oracle is the owner of oracle 9i
- I created new user oracle10 and installed oracle 10g with the new
user as part of groups : oinstall, dba, oper
- After setting up my enviroment variables accordingly, now I want to
connect as sysdba with this oracle10 user:
$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 1 12:31:46 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
conn sys as sysdba Enter password:
ERROR:
ORA-01031: insufficient privileges
Any idea where I need to touch for getting this privileges?
|
| | 2 answer | Add comment |
|
| ultrasearch: transfer metadata from one instance to another Steph 20:41:35 |
| | Hi,
This is regarding Oracle 10GR2:
In the course of switching to a newer database server we also want to
transfer ultrasearch to this new server. I'm reluctant of having to
enter all the metadata again into ultrasearch's administration
interface. How could I accomplish a complete transfer of all metadata
to the new ultrasearch instance?
(read the documentation, didn't find anything appropriate, so ...)
Thanks,
Stephan
|
| | Add comment |
|
| Self-Join combined with Outer Join? Guest 20:04:09 |
| | I guess this is probably a simple problem, but I can't for the life of
me figure it out. Say I have the following table:
job_id batch_id action date
------------ ------------- ------------- --------
1 1 sent 1/1/01
2 1 received 1/2/01
3 2 sent 1/3/01
4 2 received 1/4/01
5 3 sent 1/5/01
I want to write a query that will display as follows:
batch_id sent received
------------ ------------ ------------
1 1/1/01 1/2/01
2 1/3/01 1/4/01
3 1/5/01
I can self-join the table so that it gives me a query displaying
batch_id 1 and 2 no problem. I just can't make it display rows for
which there is no received action.
Any thoughts?? I'm desperate!!
|
| | 5 answers | Add comment |
|
| Changing schema? Major 19:19:42 |
| | Oracle version 9 g?) and 10.2 G
What is a easy way to move all objects from schema to other one?
Exp/Imp (with remapping schema)?
Do is in the DBA studio any other ways?
|
| | 4 answer | Add comment |
|
| Separating Tables and Indexes Guest 19:19:10 |
| | With a RAID5 disk system is there any advantage to separating tables
and indexes into separate tablespaces?
Thanks
Art
|
| | 6 answers | Add comment |
|
| v$buffer_pool_statistics: How to interpret data? Alex Schonlinner 18:03:26 |
| | Hi,
I'm observing something which I cannot explain (Oracle 10.2.0.2 on
8-CPU HP/UX):
I thought that v$buffer_pool_statistics contains upto date information
about how many physical reads have been done since instance startup and
how many reads in total have been done.
Now I select from the view and store the values in Excel.
Then I do a FTS on a quite large table (about 12 GB in size, buffer
cache only 2 GB), thus I would assume that after this query the
physical reads and total reads increased by about 12 GB of data.
I assumed that the v$ view does not contain real time data so I waited
about 5 minutes after the query to give it the time to reflect the new
block reads.
But I don't see the data, and I see only a small increase in the
physical reads, although the hard disk was used to 100% (using glance
utility), so the query really used the hard disk, it really used a FTS
and it really did not fit into the buffer cache.
Here is a table which contains the data from v$buffer_pool_statistics
before (KEEP, RECYCLE, DEFAULT) and 5 minutes after the FTS (KEEP*,
RECYCLE*, DEFAULT*):
NAME KEEP KEEP*
BUF_GOT 3928800 3931367
FREE_BUF_INS 3642027 3644697
DIRTY_BUF_INS 2986 2986
DB_BLCK_CHG 3459789 3459789
DB_BLCK_GETS 170622247 170622247
CONSIST_GETS 881402100 881412561
PHYS_READS 3916261 3918828
PHYS_WRITES 86056 86056
NAME RECYCLE RECYCLE*
BUF_GOT 12138557 12138590
FREE_BUF_INS 12043825 12043825
DIRTY_BUF_INS 4927955 4927955
DB_BLCK_CHG 47761840 47761840
DB_BLCK_GETS 47826166 47826166
CONSIST_GETS 10026356 10027706
PHYS_READS 11449803 11449836
PHYS_WRITES 6740056 6740056
NAME DEFAULT DEFAULT*
BUF_GOT 53661160 53662236
FREE_BUF_INS 39830003 39830787
DIRTY_BUF_INS 744520 744520
DB_BLCK_CHG 246043831 246051058
DB_BLCK_GETS 235948780 235955328
CONSIST_GETS 2387282941 2388548485
PHYS_READS 47240215 47241120
PHYS_WRITES 6538609 6540360
Where is my mistake? I used to determine the buffer cache hit ratio in
a given time interval by querying this view and determine the
difference between the number of physical reads and total reads (i.e.
db_block_gets+consistent_gets).
But using the information above that seems to be wrong...(I know I
should not concentrate too much on buffer cache hit ratio)
Regards,
Alex
|
| | 1 answer | Add comment |
|
| Drop empty table and lms flush message acks Mirfalk 09:51:22 |
| | Hi everybody,
I'm working on oracle 10g+cluster+ASM and I'm facing a strange and
unexpected behaviour.
I'm trying to drop an empty (but big), table and after one hour and an
half the drop statement is still there waiting to be completed.
There are no locks currently active on that table and the session
executing the statement is spending most of the time waiting for "lms
flush message acks".
The table is NOLOGGING and it's size is about 20000 Mb.
The devices managed by ASM are not so fast (20M/s) but this doesn't
seems to be the problem. I've got the sensation that the two sides of
the cluster do not agree about the storage management.
Any Idea?
TIA
A.D.
|
| | 8 answers | Add comment |
|
| OLS script T 09:43:19 |
| | Is anywhere on the net script for using OLS on 9.x platform??
Oracle 10g include OLS option so there is no need for additional scripts..
|
| | 2 answer | Add comment |
|
| ORA-1654 , Oracle asking for very large extent Guest 08:57:03 |
| | Hi,
I received the following message :
ORA-1654: unable to extend index LAWSON.GLTSET3 by 8192 in tablespace
I1
The tablespace is set to EXTENT MANAGEMENT LOCAL AUTOALLOCATE.
Is there a way I can effect the extent size that oracle is asking for?
My tablespace has 7GB of free space but none of the free extents is
large enough.
Any suggestions on the best way to handle this?
Lew
|
| | 19 answers | Add comment |
|
| NT authentication with Oracle 9i Krishna Dwivedi 07:58:45 |
| | Hi All,
I am using Oracle 9i as a backend for ASP.net web application. And i
want to have windows authentication for the application for that i have
set sqlnet.authentication_services to NTS in the sqlnet.ora file on the
oracle server. The .Net code is on machine A and Oracle DB is on
machine B.
When browser settings on A for logon for intranet are set as "automatic
logon only in intranet zone", the application works fine. But when i
change the settings to "Prompt for user name and password", supply my
NT user name and password it gives me following error:
ORA-12638: Credential retrieval failed
I want to know the role of NT logon credentials in initializing the
Authentication adapter. How and when does it get initialized? Also
whats the solution for this problem.
Regards,
Krishna
|
| | 3 answer | Add comment |
|
| Can shareplex compress the information when it replicates data between database? Bin 06:34:09 |
| | Hi
We would like to use shareplex to replicate data between different
cities. The source database is very busy, and bandwidth is slow.
This is from quest's knowledgebase.
"By default, the queue files are not encrypted. SharePlex for Oracle
supports 3rd party compression of files for network transport, and 3rd
party encryption as well."
I wonder which third-party products are available. Anyone has used
them?
Thanks in advance,
Bin
|
| | 3 answer | Add comment |
Monday, 31 July 2006
|
| Help with script editor in xe PhilB 23:55:12 |
| | I am working through this tutorial:
http://st-curriculum.oracle.com/tutorial/DBXETutorial/index.htm
and I am at What to do first --> Loading Data. stage
I have the file loads_sample.sql on my computer.
I upload it and loadsample is shown as an icon in Home>SQL>Sql Scripts.
When I click on it, nothing appears in the script editor so I cannot run
this script.
Any ideas?? TIA. phil
|
| | 1 answer | Add comment |
|
| AIX vs Solaris Racman 22:32:51 |
| | I need to give a presentation on the benefits of AIX vs Solaris.
Can everyone share your thoughts. Keep in mind that I`m not looking
to dismiss either platform, I think they both work very well with
specific databases,
storage and apps. Thoughts please!
Thanks
|
| | 15 answers | Add comment |
|
| Select for Update in multithreaded application Nick Selwyn 22:24:58 |
| | Greetings
I have a multithreaded application where one thread is responsible for
reading from the database and another thread is responsible for
inserting or updating the same database. We have a mechanism within the
app that prevents overlapping reads of the same row, and this serves us
well so far.
We now need to be able to run multiple instances of the application
simultaneously, and therefore need a mechanism that allows locking of
rows between the different processes to prevent overlapping reads
between the different instances.
We had thought about using SELECT FOR UPDATE NOWAIT as the
inter-process lock, but have recently discovered that this has a
problem in our existing setup, where the lock would be obtained by one
thread and released by another thread. The symptom is that the update
thread cannot get access to the table to update it as the reader thread
has the lock.
Has anyone else run across this issue, and if so, could they shed some
light on the matter?
We are running Oracle 9.2.0.7 on AIX5.2, but for legacy reasons are
using Oracle7 APIs.
Thank you
Nick
|
| | 4 answer | Add comment |
|
| SQL Server tables to Oracle Aman.Oracle.Dba 21:36:04 |
| | Please inform can I transfer sql server tables to Oracle, without using
sql loader and external table concept.
How................
|
| | 4 answer | Add comment |
|
| Avoiding repeating code in PL/SQL DML Martin T. 19:41:13 |
| | Hey all. (Oracle 9.2.0.1.0 on Windows XP)
I have the following DML in my PL/SQL code:
----
delete from machine_down_times
where start_measure_id in (
SELECT m.id
from measures m
where m.order_id = p_order_id
and time_stamp >= v_delete_from_date
and time_stamp < v_delete_to_date
)
and stop_measure_id in (
SELECT m.id
from measures m
where m.order_id = p_order_id
and time_stamp >= v_delete_from_date
and time_stamp < v_delete_to_date
);
----
As you see, I use the same nested subquery twice in that delete
statement.
Since repeating code by c&p is rather evil in my opinion I would like
to know how to avoid such constructs. (_Especially_ if I have that
range SELECT from measures... appear a few more times in my PL/SQL
package)
* VIEW -- ... clutters schema namespace with something only used in my
package
* PL/SQL Collection -- ... performance? It would have to be public to
be used as TABLE(v_collection) (?)
* Inline VIEW -- (what if I have the same SELECT in multiple DML
stmts?)
thanks a bunch!
best,
Martin
|
| | 2 answer | Add comment |
|
| jinitiator problem - hand icon Guest 19:11:57 |
| | Hi,
I'm using:
Oracle 8.1.7
Forms 6i
Internet Explorer 6
Windows 2000
Jinitiator 1.1.8.16
The problem is that the forms program calls a reports program that
generates a
pdf file. Once the report has been generated the forms program calls:
web_show_document
This should create a new IE window that displays the report.
This works OK for all PCs except for one particular PC. On the problem
PC
a hand picture is shown with a yellow star for about 2 seconds.
This is what I have tried:
- Open up a pdf document to make sure that the Adobe Acrobat reader is
working
- works OK
- Checked the server to make sure that the report has been generated -
and it has
- Actually enter the web address of the generated report - and it works
OK
- Create another window in IE with File -> New - and works OK
- Check the PC's event log for any error messages - there was one
earlier that day
saying "The browser was unable to promote itself to master browser"
Can you please help:
1) What does the hand picture mean?
2) Have you seen a similar problem and how did you fix it?
Thanks
|
| | 3 answer | Add comment |
|
| How to distinguish b/w normal trace file and different Sessions enabled SQL trace files? Ghalib 18:28:41 |
| | Is there any way to differentiate between normal trace files and
different Session's SQL enabled trace files which generate in udump
folder on Operating System?
I want to have a script which distinguishes between different
Session's SQL enable trace files and normal trace files.
So we can know that these files are generated due to enabling sql trace
utility.
Thanks in Advance.
ASAD
|
| | 8 answers | Add comment |
|
|
