Guru99: PL/SQL 101 Completely Free!

Hey all!
Just passing by today to share something I found really inetresting, and the best: it’s all free!
Do you know Guru99?

It’s a preety nice repository with lots of free tutorials and guides for PLSQL, SQL and several other languages, plus some other IT-related knowledge, like Big Data, PMP and etc.

It’s not rare to have people asking me “How to Start learning SQL” or saying “All material (including grepora :D) is too much specific, we don’t have good general documentation”.
Even Oracle having a pretty nice documentation on the products and features, general concepts are usually forgotten. Aaaaand, now I have something to link on those cases! 😀

Having a look on Guru99, First I found this guide PL/SQL First Program: Hello World, including detailed steps on:
– How to access SQL* Plus
– Connecting to Database
– How to write a simple program using PL/SQL
– Declaring and usage of variables in program
– Commenting Codes

Then found an entire category PL/SQL Tutorials. Talking to Alex (Editor), seems over 120 hours were spent on it. And they were well spent, the material is really very easy to read and understand. Chapters on this Course are:
1. Introduction to PL/SQL
2. Blocks in PL/SQL
3. PL SQL First Program: Hello World
4. PL/SQL Data Types
5. Complex Data Types in PL/SQL
6. PL/SQL Decision Making Statements
7. Loops in PL/SQL
8. Subprograms: Procedures and Functions in PL/SQL
9. Exception Handling in PL/SQL
10. SQL in PL/SQL
11. Packages in PL/SQL
12. Triggers in PL/SQL
13. Object Types in PL/SQL
14. Dynamic SQL in PL/SQL
15. Nested Blocks in Pl/Sql
16. PL/SQL Identifiers

Continue reading

Purging a SQL Cursor in 10g

Hello all,
Having issues to purge SQL Cursor in a 10g database?

DBMS_SHARED_POOL.PURGE is available from 10.2.0.4 on, but according to “DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4 (Doc ID 751876.1)”, it is event-protected.
This means you need to set event 5614566 to be able to use the purge.

Example:

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.


sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         1

sys@PRODB10> alter session set events '5614566 trace name context forever';

Session altered.

sys@PRODB10> exec dbms_shared_pool.purge('00000003B0898118,1807156054','C')

PL/SQL procedure successfully completed.

sys@PRODB10> select count(*) from v$sqlarea where sql_id='gbaq7x7myqxjw';

  COUNT(*)
----------
         0

Purge Recycle Bin Older than…

Hello all!

Have your recyclebin being full/big but don’t want to make a complete “PURGE RECYCLE BIN;”?
Want remove only older than x days (like older than 30 days)?

Please don’t:

DELETE from DBA_RECYCLEBIN where droptime<sysdate-30;
PURGE DBA_RECYCLEBIN where droptime<sysdate-30;

A good way to perform that is to execute output from:

# 30 Days
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' 
and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;

# 30 minutes
select 'purge table '||owner||'."'||OBJECT_NAME||'";' 
from dba_recyclebin where type='TABLE' and 
to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-(30/(24*60));

There is some reference here too:
How To Drop / Delete / Purge Recyclebin or DBA_RECYCLBIN For Objects Older Than x Days/minutes or selective purge of objects from recycle bin (Doc ID 1596147.1)

And here you can see an awesome procedure in case you want to automate that in a job or similar:
http://dbspecialists.com/enhanced-purging-of-the-oracle-recyclebin/

Hope it help you!
Cheers!

Oracle Database 9i: Where is my SQL_ID? Which SQL is in lock?

Struggling with that, right?
As you know, in Oracle Database 9i the view V$SESSION doesn’t have SQL_ID column…
So how to map SQLs in my database? And, for example, how to get the SQLs causing a lock?

In the end of the day, the SQL_ID is only a representation of the hash_value of an SQL. You can even make the translation from SQL_ID to Hash Value as you can check on this post by Tanel Poder.

Ok, but I have to map which sql is causing the lock in my 9i database, how can I do that?

Here it goes:

If session status is ACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.SQL_ADDRESS = s1.ADDRESS

If session status is INACTIVE:

SELECT s1.sql_text from v$sqlarea s1,v$session s2 where s2.SID=&sid and s2.prev_sql_addr = s1.ADDRESS

You’re welcome! 😉

See you next week!

Parameter Workarounds: Changing your Entire Database’s Behavior Because of a Query

This post is just a reflection, basically based on my last post about Adaptive Query Optimization/SQL Plan Directives. Several times we find some situations that can be solved by setting a parameter (likely a “_”) and we just proceed with an “alter system” disregarding about all the impact of this.

This is specially important when talking about “optimizer” parameters even for bugs causing ORA-600’s or performance issues… Changing those parameters in system will affect not only the SQL with the error but all SQLs in database, which may cause a really bad effect in some cases.

bad-idea-sign

Ok Matheus, but what can I do?
Let’s to the obvious and generally applied methods:

Continue reading

Adaptive Query Optimization: SQL Plan Directives Causing High CPU after 12c Upgrade

Hello all!

As DBAs we are always being recommended by Oracle and also recommending to clients to update their databases, but we have to be aware about new features and their effects. This is the case of Adaptive Query Optimization and in this particular case on SQL Plan Directives.

SQL Plan Directives are one of the functionalities that compose the Adaptive Query Optimization in Oracle Database 12c. The basic idea is pretty interesting: The SQL Optimizer keeps reviewing bad (“suboptimal”, as they like to say) plans, tipically incorrect cardinality estimations and generates SQL plan directives, like for missing histograms or extended statistics.

In my case, just after the upgrade to 12c (made on Jan 27th), the CPU usage increased for the same report always ran in the database:

12c_upgrade

Ok, how to check it?

Continue reading