Tip#1 run of the mill

How to print a REF CURSOR returned by an Stored Procedure?

var out_cur refcursor;
exec your_sp(param1,param2, :out_cur);
print out_cur;

How to compile invalid sys-objects?

cd $ORACLE_HOME/rdbms/admin
sqlplus sys/xxx as sysdba
start utlrp.sql

How to display time taken to refresh the materialized view ?

SELECT owner, mview_name, last_refresh_date refresh_start_time,
last_refresh_date + (fullrefreshtim / (60 * 60 * 24)) refresh_end_time,
refresh_method, fullrefreshtim refresh_time_seconds
FROM dba_mview_analysis
ORDER BY 3, 4

How to find tables which are having a same/similar Column name?

SELECT owner, table_name object_name, column_name, data_type, data_length
FROM all_tab_cols
WHERE column_name LIKE '%PART_NUMBER%'

How to find object dependencies?

SELECT NAME, TYPE, referenced_name, referenced_type
FROM SYS.all_dependencies
WHERE owner = 'owner_name'
AND referenced_name = 'any object name'

How to find all invalid objects for an user?

SELECT object_name, object_type, created, status, last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
AND owner = 'user_name'

How to find rows with non- numeric character in a column ?

Sometimes I need to check if the ID column of the table is only numbers and no characters are allowed (unfortunately can not change the data type to NUMBER ) So following query gives me all IDs with non-numeric data in it,

SELECT adm_id
FROM address_master
WHERE NOT (LENGTH (adm_id) - LENGTH (TRANSLATE (adm_id, CHR (1) || TRANSLATE (adm_id, CHR (1) || '1234567890', CHR (1)), CHR (1))) = 0 )

How to insert special character in a table?

You need to know the ascii code for the char and then you can insert it using CHR() e.g. you can insert ‘&’ whose ASCII code is 38 as below,

insert into test1 (c1) values ('xx' || Chr(38) || 'xx')

How to remove duplicate rows from a table?

If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:

DELETE FROM tablename
WHERE rowid not in (SELECT MIN(rowid)
FROM tablename
GROUP BY column1, column2, column3...);

Here column1, column2, column3 constitute the identifying key for each record.

If the keys cannot be identified for the table, you may create a temporary table using the query

CREATE TABLE temptablename
AS SELECT DISTINCT *
FROM tablename;

Then drop the original table and rename the temp table to original tablename