Oracle DBA Tips

Try this: BEGIN DBMS_SCHEDULER.set_scheduler_att...

2012-02-24T20:30:44.969+01:00

Try this:
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute (
attribute => 'default_timezone',
value => 'US/Eastern');
END;

Thanks a lot. This really came in handy when i was...

2012-01-13T07:19:55.495+01:00

Thanks a lot. This really came in handy when i was in need of help.

Very helpful. Thanks. Now I know what Toad is doin...

2011-06-29T20:50:54.573+02:00

Very helpful. Thanks. Now I know what Toad is doing in the background to fix chained rows.

it is good to find out that there are as easy as t...

2011-06-21T18:10:29.150+02:00

it is good to find out that there are as easy as this kind of solution on the internet that solves exactly the problem you're encountering.

I like it very much!

2011-06-20T23:28:40.761+02:00

I like it very much!

2011-06-08T16:47:35.591+02:00

This comment has been removed by a blog administrator.

Thanks for your help!

2011-05-27T20:33:15.880+02:00

Thanks for your help!

Good point P.Katsoulis!

2011-05-24T14:36:47.705+02:00

Good point P.Katsoulis!

Please check your row size and data block size. It...

2011-05-24T14:36:17.572+02:00

Please check your row size and data block size. It happens when a row cannot fit into a single Oracle block ever, because it is just too big.

I followed these instructions in 11.2.0.2 and the ...

2011-05-23T22:50:58.340+02:00

I followed these instructions in 11.2.0.2 and the query still shows the same chained rows after following these steps. Am I missing something? I re-analyzed the tables but still get the same result.

After reading this blog, its my sincere feeling th...

2011-04-16T14:33:46.851+02:00

After reading this blog, its my sincere feeling that you own the exact idea of what are you deliberating.I seriously wish to congratulate you for zeal for the work you own that has made you stand today at this stage.Genuinely,there has not been even once that I have visited your post and went without getting some good knowledge.Get Going.And yes i have bookmarked your site dbatips4u.blogspot.com .

hi, new to the site, thanks.

2011-02-18T06:36:22.231+01:00

hi, new to the site, thanks.

Thanks alot , this saved my life

2010-11-12T14:24:42.192+01:00

Thanks alot , this saved my life

Genial fill someone in on and this mail helped me ...

2010-09-25T23:35:15.959+02:00

Genial fill someone in on and this mail helped me alot in my college assignement. Gratefulness you for your information.

I love when there's an easy-to-find post with ...

2010-09-15T18:40:38.749+02:00

I love when there's an easy-to-find post with exactly the info you're looking for. Thanks!

find -name "*.trc" -exec rm {} \; would ...

2010-01-03T12:33:40.643+01:00

find -name "*.trc" -exec rm {} \; would make it too ... one line ;)

Well written post. So by that experience, is it go...

2009-11-05T17:44:51.818+01:00

Well written post. So by that experience, is it good to kill the oracle process from oracle itself? Can you write down SQL (v$session, V$process and v$lock/DBA_WAITERS) so that we get the OS process number directly and execute the kill -9 pnumber?

Hi, when i execute the query select * from User_S...

2009-08-28T19:57:11.154+02:00

Hi,
when i execute the query
select * from User_Scheduler_Jobs
i also get the error

ORA-01882: timezone region %s not found

can any one help me.
my oracle client version 10.1.0.2.0

Denis, If both of your server had GLOBAL_NAMES se...

2009-07-14T11:30:54.327+02:00

Denis,

If both of your server had GLOBAL_NAMES set to true and GLOBAL_NAME was without any suffix, it should have work without any issue. See below where I have tries something similar,

[oracle@oratestbox admin]$ sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Jul 14 11:15:32 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production

SYS@erpstage> select * from global_name;

GLOBAL_NAME
-------------------------------------------------------------------------------
ERPSTAGE

Elapsed: 00:00:00.00
SYS@erpstage> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean FALSE
SYS@erpstage> alter session set global_names=true;

Session altered.

Elapsed: 00:00:00.01
SYS@erpstage> show parameter global_names

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE
SYS@erpstage> CREATE DATABASE LINK "ERP_DIFF" CONNECT TO "test_ro" IDENTIFIED BY test USING 'ERP';

Database link created.

Elapsed: 00:00:00.26
SYS@erpstage> select * from global_name@ERP_DIFF;
select * from global_name@ERP_DIFF
*
ERROR at line 1:
ORA-02085: database link ERP_DIFF connects to ERP

Elapsed: 00:00:00.59
SYS@erpstage> CREATE DATABASE LINK "ERP" CONNECT TO "test_ro" IDENTIFIED BY test USING 'ERP';

Database link created.

Elapsed: 00:00:00.01
SYS@erpstage> select * from global_name@ERP;

GLOBAL_NAME
------------------------------------------------------------------------------------------------------
ERP

Elapsed: 00:00:00.50
SYS@erpstage> select value from sys.v_$parameter@ERP where name='global_names';
VALUE
------------------------------------------------------------------------------------------------------
TRUE

Elapsed: 00:00:00.12
SYS@erpstage>

BTW, I found a way to create the db link I wanted....

2009-07-14T02:38:19.803+02:00

BTW, I found a way to create the db link I wanted. See: this link

Thanks for the infomation. I did some test based o...

2009-07-14T02:07:30.408+02:00

Thanks for the infomation. I did some test based on your tip. However, I still have unwanted suffix to the dblink after update:

SQL> show user;
USER is "SYS"
SQL> update GLOBAL_NAME SET GLOBAL_NAME='DBWRKEV1';

1 row updated.

SQL> commit;

Commit complete.

z9xxx94@DBWRKEV1> select * from global_name;

GLOBAL_NAME
---------------------------------------------
DBWRKEV1

z9xxx94@DBWRKEV1> create database link dbwrk920 connect to z9xxx94 identified by vxxx using 'dbwrk920';

Database link created.

z9xxx94@DBWRKEV1> select * from global_name@dbwrk920;
select * from global_name@dbwrk920
*
ERROR at line 1:
ORA-02085: database link DBWRK920.REGRESS.RDBMS.DEV.US.ORACLE.COM connects to DBWRK920

Note
The global_name is true in dbwrkev1(10g). dbwrk920 is a 9i db.

Spend some time with dblink , but no luck so far.

Thanks you, very informative

2009-06-03T09:07:48.533+02:00

Thanks you, very informative

Carefully disable (enabled triggers, constraints) ...

2008-12-21T12:05:00.000+01:00

Carefully disable (enabled triggers, constraints) before deleting rows of the table and enable after inserting. if not you might not be able to start the procedure at all or even have unexpected results by the enabled triggers (for example an insert-trigger might reactivated and affect other tables etc.)

Thank you for the appreciation! Pleasure is all mi...

2008-12-11T13:11:00.000+01:00

Thank you for the appreciation! Pleasure is all mine :-)

Thank you, that was short and to the point. So mu...

2008-12-09T17:46:00.001+01:00

Thank you, that was short and to the point. So much to remember, glad people like you make my job a bit more easier and enjoyable.