[Oracle-l] using different database (SID) in a subselect with SQL*Plus / Shell Scripts

On the database you are connecting to you would create a database link to the remote database and then either reference the link in the sub-select or create a synonym for the remote object that provides the link:

Select testfield from [email protected]

HTH -- Mark D Powell --

Thaks Mark.

CREATE DATABASE LINK local
CONNECT TO hr IDENTIFIED BY password
USING 'local';

After this database link is created, hr can query tables in the schema
hr on the local database in this manner:

SELECT * FROM [email protected];

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_5005.htm

cheers Sven

Just a small question about db links:

is that secure? Is the password stored hashed? What are the security
disadvantages of db links?

cheers Sven

Hi,
I know that seems crazy but please clarify that for me ,let say for
10.2.0.3 but that does not matter .

Let say we got table t with col1, col2, col3 and index on that table on
col1, col2 and
query

select col1,col2,col3 from t where col1 = 'value' ;
Let say that
TABLE ACCESS BY INDEX ROWID was used for that query

My question from where Oracle retrieves col1,col2 values ?
From index block and then col3 value from table block via rowid found in
index block ?
Or maybe only rowid from index and then col1,col2,col3 values from table
block only ?

I'm asking because wonder if its possible to get different values if
access plan is full table scan .
Suppose You've got 'corrupted' index block (or rather changed by block
editor) with different value than in table block for some column value .
So it is possible to get different values if access path differs ?
Regards
GregG

Hello,
in your case index is not getting used..i suppose...

you mean ..you created a table and some indexes..block corruption is on
index and you fired query..
it should error out in this case...

index is not meant to give the subset (fulfilled by query) of data BUT to
present it in the manner you created the index...the query clause is going
to give the subset of data...

thanks...subodh

It's difficult to say for certain (and may be version dependent), but it's
probably getting columns from the index whenever possible.

Two arguments in favour:
a) If you call dbms_xplan with the 'projection' option then you will see (with
your example) that col2 appears in the projection for the index line of the
execution plan - this is inconclusive.
b) if you set up your table so that you have a chained row with columns (colX
and colY, say) in the "chained" part of the row and a new index (col1, colx),
then you can show that the logical I/O and continued fetches are different when
you run
select col1, colX, col3 where col1 = constant
compared to
select col1, colY, col3 where col1 = constant
a few variations on this theme are fairly convincing


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Thanks, that might explain strange behavior of our obj$ dictionary table,
because we are unable to drop one user table .
The reason is (I think) when index access is used table name is
'corrupted' (different name) than that in obj$ data block :).
So we are getting object does not exists but when You force full scan
on obj$ , displayed name is proper .
Regards
GregG

This is a good case for
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 12';
drop table <table_name>;
exit;

to see what is actually going on. Since a drop is actually a long list of
sql's and proc's, something like

drop
--+ gather_plan_statistics
<table_name>;

followed by an xplan last query will just throw an error.

Doing separate session traces for a pair of tables (one that you either
create for the purpose of dropping or otherwise don't care about and the
other the "won't drop" table) should give you a nicely different pair of
trace files to look at to see where the wheels fall off. You'll want the
test drop table to be of a similar table type, for example a regular heap
table, a partitioned table, or an iot so the pieces of the drop litany that
are invoked and used are similar.

mwf

-----Original Message-----
From: [email protected]
On Behalf Of Grzegorz Goryszewski
Sent: Wednesday, November 23, 2011 1:45 PM
To: [email protected]
Cc: [email protected]
Subject: Re: value from index block or table block
Thanks, that might explain strange behavior of our obj$ dictionary table,
because we are unable to drop one user table .
The reason is (I think) when index access is used table name is 'corrupted'
(different name) than that in obj$ data block :).
So we are getting object does not exists but when You force full scan on
obj$ , displayed name is proper .
Regards
GregG

Thanks Mark,
We actually did that some time ago, but Oracle support does not allow that .
Fortunately looks like, during upgrade to 11g the new index is created
on obj$
in place of old with new columns so upgrade should solve our problem in
supported way or
just fail :)
Regards
GregG

Hi,
Im considering which partitioning option to choose but first some
background .
DW database and a lot of tables partitioned by time_id which is number
representing day number from some time ,
and second column which is application_id (fixed numbers like 1,2,3,4 no
more than 10) .

Queries are like that (simple joins with some predicates to get
partition pruning )
select * from
tab1 , tab2
where
tab1.id=tab2.id
and tab1.time_id = 123 and tab1.application_id in (1,2) .

So the ETL process is based on exchange partitions and regathering stats .

First solution is

partition by (time_id, application_id) --composite partition key
pros:
looks simple :)

other is
partition by time_id and subpartition by application_id lists partitioning .
pros:
seems more obvious and by book

My question is, which approach is better from, performance/maintanance
point of view ?
Regards
GregG

Let me get this straight: You are experiencing the return of incorrect
values (meaning either a bug or a corruption) and Oracle Support will not
authorize your use of setting an event either at the session or system
level?

a) Have you asked on your SR? Denying your suggestion of using a diagnostic
likely to show the problem would create both an interesting legal situation
and a troubling review situation for the support analysts involved when you
escalate the call.
b) Make a physical clone using OS level tools and do it there. A physical
clone will not repair anything.

Alter session rather than alter system should be sufficient. That was a
sloppy cut and paste.

"Our customers are not sheep to be shorn." - An oracle executive I won't
out.

Don't be a sheep.

Regards,

mwf

-----Original Message-----
From: [email protected]
On Behalf Of Grzegorz Goryszewski
Sent: Thursday, November 24, 2011 12:22 PM
To: [email protected]
Subject: Re: value from index block or table block
Thanks Mark,
We actually did that some time ago, but Oracle support does not allow that .
Fortunately looks like, during upgrade to 11g the new index is created on
obj$ in place of old with new columns so upgrade should solve our problem in
supported way or just fail :) Regards GregG

Hi Mark,
sorry but looks like I was no precise enough .
We provided 10046 ,and that confirmed corruption in I_OBJ2 index (if
memory serves me right name)
but as a solution oracle said please do exp/imp of your database .
Looks cool but our db is 15TB in size :) .
The problem is You cant rebuild that index (cant remember exac ora err
for that ) in normal way,
You have to open database in upgrade/migrate mode and with some event
try to rebuild but its
not supported as far as I know .
Finally we did drop offline for data file with that object and
physically removed it .
Only problem is rman warning about that unrecoverable tablespace .
Regards
GregG

Goodness. You dropped a data file out of the SYSTEM tablespace?

Or are you writing that you dropped the tablespace containing the object
referenced by the corrupt I_OBJ2 index? (All the indexes of sys.obj$ are
born in SYSTEM unless you have an altered sql.bsq file.)

If that is the case, did the offline drop clean up the entries in I_OBJ2?
Then I'm surprised just dropping the offending referenced object didn't do
the trick.
If no clean up took place, then I would think you still have a corrupt block
in I_OBJ2.

At this point I'm mostly just curious about exactly what did transpire,
since I don't understand.

Regards,

mwf

-----Original Message-----
From: [email protected]
On Behalf Of Grzegorz Goryszewski
Sent: Saturday, November 26, 2011 10:29 AM
To: [email protected]
Cc: [email protected]
Subject: Re: value from index block or table block
Hi Mark,
sorry but looks like I was no precise enough .
We provided 10046 ,and that confirmed corruption in I_OBJ2 index (if memory
serves me right name) but as a solution oracle said please do exp/imp of
your database .
Looks cool but our db is 15TB in size :) .
The problem is You cant rebuild that index (cant remember exac ora err for
that ) in normal way, You have to open database in upgrade/migrate mode and
with some event try to rebuild but its not supported as far as I know .
Finally we did drop offline for data file with that object and physically
removed it .
Only problem is rman warning about that unrecoverable tablespace .
Regards
GregG

Exactly, that was the only table in that tablespace ,when we tried drop
that table Oracle
said object does not exists (because of corruption). So we offline
dropped the only datafile for that
tablespace .
Well not sure about that. Ill try to check this on monday . Thats realy
interesting whats Oracle
doing with objects (in dictionary terms) when You offline drop datafile
they belong to .
Hope that explains Your doubts .
Regards
GregG

That makes sense. When you get a chance, I recommend you get a dump of that
index block with your favorite block editor to see if there are any more
entries in it. Also, I worry a bit about whether the index structure to that
block or just the leaf block is corrupt. I believe you may be still hanging
fire. So you might want to try validate index to see how bad it is. I guess
I'd make a test physical clone of the minimal set to test that validate on,
because I'm not sure whether your release might mark the index invalid or
dump core if it fails.

Is transportable tablespace (TTS) instead of a complete reload a
possibility? Perhaps you could make a Segway (stupid word autocorrects
Segway to a leading capital, as if it was only a dangerous personal
transportation device and not a normal noun, just like apple. Hmm, MS Word
treats apple normally) a tablespace at a time patching up with dblinks to
the new place until it is all there. Even though your total size is pretty
big ( was it 15 TB?) if you dictionary is reasonably modest TTS shouldn't
take too long.

Good luck. That sure is nasty.

(In 1988 I had the similar experience of support telling me to
export/import. But my database was 7 GB.

I know, you can hardly build an empty dictionary that small now. But with
25Mhz chips and 5600 RPM nearly no cache drives, an export was a 7 day
operation. No one will ever know how long the import would have taken
because it never completed.

Regards,

mwf

-----Original Message-----
From: [email protected]
On Behalf Of Grzegorz Goryszewski
Sent: Saturday, November 26, 2011 12:58 PM
To: [email protected]
Subject: Re: value from index block or table block
Exactly, that was the only table in that tablespace ,when we tried drop that
table Oracle said object does not exists (because of corruption). So we
offline dropped the only datafile for that tablespace .
Well not sure about that. Ill try to check this on monday . Thats realy
interesting whats Oracle doing with objects (in dictionary terms) when You
offline drop datafile they belong to .
Hope that explains Your doubts .
Regards
GregG

Hi,
its more for blog post but Im not blogging so maybe share here :) .
Looks like we hit (in 10.2.0.3 env) :
*DBMS_SERVER_ALERT.SET_THRESHOLD HANGS FOREVER AT RELIABLE MESSAGE [ID
794589.1]

looks not bad (relaible message is idle wait right ?) but when I've
tried to deal with hanging processes via kill -9 processes are no
longer on os pid lists but
from Oracle point of view we still got sessions for that ospids and PMON
is unable to proper clear that session .
From PMON trace:
**** 2011-11-25 13:44:35.047
found process 0x25f5f8bd0 pid=40 serial=2 ospid = 15291 dead
found process 0x25f5f93b8 pid=42 serial=1 ospid = 30345 dead
*** 2011-11-25 13:44:45.060
found process 0x25f5f8bd0 pid=40 serial=2 ospid = 15291 dead
found process 0x25f5f93b8 pid=42 serial=1 ospid = 30345 dead
*** 2011-11-25 13:44:47.064
found process 0x25f5f8bd0 pid=40 serial=2 ospid = 15291 dead
found process 0x25f5f93b8 pid=42 serial=1 ospid = 30345 dead

in alert log PMON is unable to clean up process bla bla .
After restarting EM grid agents there are two new hanging processes on
dbms_server_alert.set_threshold still reliable message .
When You strace that proces You can see
strace -p 30540
Process 30540 attached - interrupt to quit
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
getrusage(RUSAGE_SELF, {ru_utime={0, 352946}, ru_stime={0, 95985}, ...}) = 0
getrusage(RUSAGE_SELF, {ru_utime={0, 352946}, ru_stime={0, 95985}, ...}) = 0
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)
semtimedop(819218, 0x7fbfff7270, 1, {1, 0}) = -1 EAGAIN (Resource
temporarily unavailable)


so its timeout on semaphore set operation call .
There is SR open but Oracle not responded so far .
Dont want to be so dramatic but Im sure shutdown immediate will not help
here :) .
Any ideas how to deal with session hanging on that event (reliable
message ) ?
Regards
GregG

Very Nice, JL.

Now if the index is actually corrupt that will present problems, but if
someone has the energy to use a binary editor on a data file to carefully
modify an index leaf block's column value without changing its length
between two values where there is lexical room to make the change without
changing the position for sorting, you can probably actually see.

As for me, I'm convinced enough by your demo using chained rows that I can't
muster that energy.

Regards,

mwf

the main drawback of this schema is that is doesn't prune predicates like

tab1.time_id between 123 and 133 and tab1.application_id in (1,2)

this works fine with composite range - list partitioning
If there are queries using DATE values (not direct the surrogate key) you
may wont to check the post
http://www.db-nemec.com/HowIDefineATimeDimensionUsingSurrogateKey.html

HTH

Jaromir D.B. Nemec