DBMS_LOCK.SLEEP is now deprecated, the new SLEEP is DBMS_SESSION.SLEEP

We need sleep function many a times in our code, may be for application logic or even sometimes for monitoring purpose. We always had this available with sleep() function that resides within DBMS_LOCK ==> DBMS_LOCK.SLEEP

This was always a big security risk as granting access to DBMS_LOCK will make a way to get access to other functions/procedures within which is not always necessary.

So from 18C, Oracle comes with SLEEP function within a publicly granted package ==> DBMS_SESSION.SLEEP.

DBMS_SESSION.SLEEP

From 18C DBMS_LOCK.SLEEP is deprecated, but it is still present for backwards compatibility.

I would suggest, whoever is planning to upgrade their databases to 19C in near future, should upgrade their PL/SQL codes to use DBMS_SESSION.SLEEP instead of DBMS_LOCK.SLEEP. Also one should make sure to revoke any grants to the DBMS_LOCK package where they were intended to give access to only SLEEP procedure.

References:

ER 23557076 : PUBLIC SLEEP FUNCTION

Hope u will find this post very useful. 🙂

Cheers

Regards,

Adityanath

 

Why should people attend AIOUG events :-)

Dear Connections,

I was chatting to one of my dear friend yesterday during Sangam19 and he turned to me and asked “Why should people attend any AIOUG events”. It made me pause to think for a moment. Any type of such events is going to take up your time and will usually cost a decent amount of money. In addition, you’re taking time away from work & its not for vacation.

This post is to share my personal top reasons as to why people should attend AIOUG events:

Expand your professional network:

These events provide great opportunity to network with like minded people and industry peers.It is always helpful to have a healthy professional network. These events bring together people from all different geographical areas under single roof. Also this helps you meeting with people in your field that you haven’t connected in a while.

Expand your knowledge:

No matter how experienced you are at your field, everyone can learn. These is the best opportunity to hear from all Industry experts in person. You will meet all Oracle Gurus wherein they will share their experiences, their learning on the latest technologies. Also you can get guidance from them, also you can clear your doubts.

Learn beyond your skills:

When you are working for the same company for the years, your work & knowledge can become monotonous. Most of the times due to busy work schedule, its very difficult keep yourself up with the technology changes and advancements. These events can help you to know about newest technologies in the market also you can plan your careers accordingly.

To present yourself:

These events are the best opportunities wherein you can market yourself, share your ideas, knowledge. Here you can meet someone who can influence your professional career dramatically. You can develop a reputation as an expert to your peers.

Get certified:

All AIOUG events, provide your considerable discounts on all Oracle certifications. Especially in during Sangam19 Test Fest, people completed their certifications almost in free of cost.

Have Fun:

Last but of course not the least is the fun part. These events are fully filled with fun activities. These events can be a much needed break from your day to day life.

I am happy share some of the best pics from Sangam19:

 

Hope u will find this post very useful. 🙂

Cheers

Regards,
Adityanath

 

Drop empty tablespace fails with ORA-14404 or ORA-144047 partitioned table contains partitions/subpartitions in a different tablespace

Recently I was performing database reorg operation on one of pre-prod environment with traditional way of table/lob movement followed by index rebuild. After moving all segments from current tablespace to new, I was ready to drop original one.


SQL> select count(0) from dba_segments where tablespace_name='MY_TABLESPACE';
  COUNT(0)
----------         
0

And it errored out!!!


SQL> DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TABLESPACE INCLUDING CONTENTS AND DATAFILES
*ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

As first step, I tried purging dba_recyclebin, but it didn’t help.

After searching on metalink, I found following note with similar issue: ORA-14404 or ORA-14407 When Trying to Drop a Tablespace (Doc ID 1674989.1)

which talks about two causes of this issue:

1. An attempt was made to drop a tablespace which contains tables whose partitions or subpartitions are not completely contained in this tablespace. 

OR

2. This can happen due to deferred segment creation introduced in 11.2.

First cause was matching my case:

I tried finding out tables whose partitions were spread across other partitions along with tablespace MY_TABLESPACE & moved to it to new tablespace as well:


SELECT    'alter table '|| table_owner|| '.'|| table_name|| ' move partition '|| partition_name|| '   tablespace MY_TABLESPACE_NEW;'  FROM (SELECT DISTINCT table_name, partition_name          FROM dba_tab_partitions         WHERE tablespace_name = 'MY_TABLESPACE') a,       dba_tab_partitions b WHERE b.tablespace_name <> 'MY_TABLESPACE'       AND a.table_name = b.table_name;

After moving all affected table partitions, I once again tried dropping tablespace: MY_TABLESPACE. It once again failed with same error: ORA-14404.
Then I found same issue with some of index partitions as well, for which I used following command:


select distinct 'alter index ' || index_owner || '.' || a.index_name || ' rebuild partition ' || a.partition_name || ' tablespace MY_TABLESPACE_NEW;'from ( select distinct index_name,partition_namefrom dba_ind_partitionswhere tablespace_name = 'MY_TABLESPACE' ) a, dba_ind_partitions bwhere b.tablespace_name <> 'MY_TABLESPACE' and a.index_name = b.index_name;

Once indexes were rebuild, I was able to drop tablespace MY_TABLESPACE without any issues 🙂

Note: You may also get ORA-14407 is some cases, wherein sub-partitions are the issue, for which you will use above commands just with sub-partitions views.

Just to elaborate seconds possible cause of this error: deferred segment creation:


The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION  set to TRUE.So in simple words, if you have set this parameter to value TRUE, particular table will be populated in dba_tables once created, but will not be populated in dba_segments unless we insert a row to it. So in the case of defered segments, you will not be able to drop tablespace.


Hope so u will find this post very useful:-)

Cheers

Regards,
Adityanath

SQL Developer providing incorrect query results than TOAD/sql*plus

Some days before I came across very weird issue with SQL Developer. One of the developer in my company was complaining that, she is getting “no rows selected” after executing some select query. According to her it was an unexpected behaviour & expecting some rows from that query.

After having some chat got to know that she was using SQL Developer as tool for running that query.

I tried executing same query by logging with her credentials on my PC but through TOAD (not SQL developer). Unexpectdely I got 300 rows with same query.

Means When I run the one query directly on SQLPLUS or TOAD then it displays complete results, while I run the same query with SQL Developer then it does not provide me any result. 😦

After searching on metalink I found : SQL Developer Returns Zero Rows on Table while SQL*Plus Returns Positive Count (Doc ID 415920.1) 

Which states this issue happens when the query is dependent on the results of USERENV(‘LANG’).

I checked USERENV(‘LANG’) settings in both the tools & found that :

TOAD : SELECT USERENV (‘LANG’) FROM dual; => US
SQL Developer : SELECT USERENV (‘LANG’) FROM dual; => GB

After following below steps for changing LANG settings on sql deveoper issue resolved 🙂


1. Start sqldeveloper.
2. Go to Tools-->preferences-->database-->NLS parameters.
3. Change LANGUAGE to AMERICAN.
4. Restart sql developer.

For putting some light on this behaviour, Enviornment in our case was Oracle eBusiness Suite which has lots of language specific views (only returns records where userenv(‘LANG’) matches an installed language in eBusiness).

Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath