Oracle Database check tablespace usage and size.

Many production oracle database instances do not have auto incrementing tablespaces. You would need to know beforehand before users start getting ORA-01688 unable to extend tablespace errors.

You would probably have some sort of monitoring on the tablespaces already , OEM shows alerts about tablespaces readily as well. But its good to have a query handy to quickly check the tablespace usages you have in your system.

select df.tablespace_name ,totalusedspace  "totalusedspace_mb" ,
(df.totalspace - tu.totalusedspace) "Free_MB",
df.totalspace "Total_MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Oracle String quote escape tool to help you generate the escaped pl sql string

Oracle string quote escape tool : There will be a times when you are writing dynamic sql and that sql  has lot of quote constants in where clause or in your select list , escaping these quotes can be quite cumbersome if you don't have the right tool.
If you have a large query or string which has quotes in it  and you do not want to bother checking the escapes and want to use the escaped query / string readily in your plsql.

Below tool will help you generate escaped string from Oracle  plsql standpoint , just copy and paste it in your code
It's not ours to take.

Oracle Database : Quickly test if the directory you are writing to is writable and configured in utl_file or Oracle directory object.

There are times when writing a code when we want to write files with Oracle utl_file package, but we do not have access to v$parameter tables or do not know if the directory object exists in Oracle or not.

There are also many times cases when you are tracing error in multi hundres of lines of code and do not know if the file writing is causing error or its some other code in your plsql.

One can quickly test file writing is happening or not with the help below small code.

DECLARE  ---------- Oracle utl file write test.
   L_HANDLER   UTL_FILE.FILE_TYPE;
BEGIN
   L_HANDLER := UTL_FILE.FOPEN ('/xfer/data', 'utl_test.txt', 'W');
   UTL_FILE.PUTF (L_HANDLER, 'UTL_FILE Test Success');
   UTL_FILE.FCLOSE (L_HANDLER);
END;

Oracle Database find duplicate records on certain column in a table.

This is a Beginner level article but can be referred readily by guys using Oracle Database moderately for a refernence.

You can identify the duplicate records in Oracle Table by using a group by clause. You need to group by column / columns from which you are trying to identify the duplicates , use count() function to identify the duplicates : expression is self exploratory having count () > 1 .


Ex 1 : Identify duplicates first names from employees table.
 Note in this context : Employee records is not necessarily duplicate we are only trying to find employees with same first name.

select empl_first_name  , count(empl_first_name)
from employees
group by empl_first_name 
having count(empl_first_name) > 1

Ex 2 : Identify duplicates or repeats based on 2 or more columns.

select empl_first_name  , empl_last_name  ,  count(empl_first_name)
from employees 
group by empl_first_name , empl_last_name
having count(1) > 1

Identify duplicate / repeats based on 2 or more columns.

Ex 3. Use of Analytic function to identify duplicates.

select * from 
(select count(1) over (partition by empl_first_name ) fname_count , e.*   
from employees e)
where fname_count > 1

Advantage in this example is you were able to select all columns in the query resultset unlike the pure group by queries above.

Ex 4. Delete duplicate rows : (Be careful and wary of what you are doing)

delete from     employees where rowid in
 (select rowid from 
   (select 
     rowid,
     row_number()
    over 
     (partition by empl_first_name order by empl_first_name) dup
    from employees)
  where dup > 1)

Oracle database use of sequences for incrementing values.

We always find a need to use incremental sequence for our table data columns like primary key or other unique keys. Examples of such fields could be id fields like employee id or order sequences for order processing in OLTP databases.

Oracle provides very efficient non blocking way of getting and generating these new sequences with a database object called sequence.

Sequence should be created and used as follows.

CREATE SEQUENCE employee_id_seq
 START WITH     10000   -- Start your sequence with 10000
 INCREMENT BY   1       -- Sequence would be incremented by 1 when nextvalue is fetched from sequence
  NOCYCLE;              -- Oracle sequence would not restart 
                        -- If you want sequence to start over after N then specify maxvalue N clause in the sequence

Full syntax of sequence statement

Once we are done creating sequence object in database next thing is pretty straightforward.
You can use your sequence immediately in insert statement.

INSERT INTO EMPLOYEES (EMPLOYEE_ID , FIRST_NAME,LAST_NAME ) 
VALUES
(employee_id_seq.NEXTVAL , 'PATRICK' , 'HARRIS');

If you are using Oracle Forms or some other tool that generates Inserts for you and if you do not have direct control then consider writing a BEFORE INSERT Trigger that would call the sequnce.nextvalue and provide the next sequence to your insert.
Ex.

CREATE OR REPLACE TRIGGER employees_pre_ins
BEFORE INSERT ON employees
FOR EACH ROW
begin
  SELECT employee_id_seq.NEXTVAL
  INTO   :new.employee_id
  FROM   dual;
END;
/

Oracle Connect By Clause to Perform Hierarchical Queries.

 If we want to perform hierarchical queries we need to use connect by clause in our sql query.

Hierarchical query is any query wherin you want to display parent child relationship with the case that a parent may have multiple children and those have their own creating a tree like structure.

One classic scenario highlighted on almost all sites is employee table wherein employee's manager is stored in the employee row the manager id is again one of the employee in the employee table.

But we are not going to take it as you can reference it almost on any site that hosts oracle based content.

We are going to do a query based on DBA_DEPEDENCIES or ALL_DEPENDANCIES if you do not have access to former.

In DBA_DEPENDANCY table we have hierarchy of REFERENCED_NAME(parent) and the referring objects in column NAME(child ) that have dependency on the parent.

with this in mind we would need to set our connect by clause as prior NAME= REFERENCED_NAME

This confirms that REFRENCED_NAME is the parent and we are going to base this hierarchy on this relation.

Next addendum to connect by clause we are going to make is nocycle attribute. In Oracle there is a possibility of cyclical dependency in this case we would not have clear tree. Oracle throws an error when there is such cyclical data in your query. If you know this is the possibility its better to use nocycle clause. 

So here's the code that would print all the dependencies of table 'ORDER_HEADER_TABLE'.

select CONNECT_BY_ROOT REFERENCED_NAME  ||   sys_connect_by_path( name , '->' ),
d.* from dba_dependencies d
where owner = 'HR' 
connect by nocycle prior NAME= REFERENCED_NAME 
start with REFERENCED_NAME = 'ORDER_HEADER_TABLE' 
order by 1

Demo of DBA_DEPENDENCIES HIERARCHY

SYS_CONNECT_BY_PATH gives you bread crumb like navigation from root node (in this case ORDER_HEADER_TABLE) to its child leaf nodes or intermediate nodes. So basically you have printed the hierarchy and path tracing from parent to child nodes. Hope you found this useful.

Oracle Analytical Functions : Tutorial Part 2 Covers sum avg lag lead.

In this tutorial video we have demonstrated how to use oracle analytical functions like lag , lead , sum and avg etc.

Example 1.
Use of oracle function sum to display running totals with the use of unbounded preceding.

select sum(Salary) over (order by salary rows unbounded preceding) running_total 
, salary , e.*   from employees e

Identify gap in the contiguous sequences with the lag function.
Ex. In Employees table we have contiguous sequence of employees but due to some anomaly we found that there is now gap in the sequences. Ex Employee Id 210 comes after 206 which is not contiguous.

We can write a query as given below to identify such sequence gaps.

Oracle Lag Function

 select  *  from ( select lag (e.employee_id , 1) over (order by e.employee_id) as prev_emp,   e.* from employees e ) tmp
where (tmp.employee_id - tmp.prev_emp) > 1

How to DEBUG ORA-06502 Numeric or value error.

If you have 3000 lines of code and a custom exception handler like when others. The ORA 06502 can be  notoriously elusive to find.

This error is caused by .

• Trying to assign a higher length/precision value to variable whose size / precision is smaller than the value being assigned.
• Or trying to assign a non numeric value to a numeric variable.

Ex .

This is easy to see in 5 line code but if you have 3000+ lines of code and you did not handle the exception correctly. (plenty of times we write a generic when others handler which does not give us the line number but only a petty error message which says Numeric or valuer error.) 

This would be the case wherein you would need to debug or trace where the error is occurring.

Here's how you can debug such failure in this case.

1. If you are on your development or test enviornment , strip off all your excption handler in your package , procedure , function whatever it is that's throwing this error. When you run the code next time the line number of code throwing this error will be revealed.

2. If you are on production environment , if possible create a plsql block of the procedure / function throwing this error , again strip off all exception handlers. when you run this plsql block line number of code throwing this error will be reveled. 

3. If option 2 is not feasible due to huge package or package delarations with types or due to some other DML constraints on your production instance. Then do this.

• Find out all the lines of code where assignment is happening (search for := )
• Find out all lines with select into variable statements.
• Isolate numeric value assignments from above lines (gotten in step 1 & 2) 
• Check with help of queries and surrounding code which statement could go wrong.
• At this juncture you may have isolated the 2-3 lines which could be the culprit.
• Compare and root out the statements by running against valid data not throwing this error.

So this was how you could possibly check for the ora 06502. Its of course manual effort and there is no magic wand. 

One notable option is to use dbms_debug through sqldeveloper or toad.  But I never found it useful. If I can run the package in debug mode I might as well change it and remove exception handlers instead.