HOW TO FIND WHETHER A STRING IS NUMERIC OR NOT IN ORACLE

REGEXP_LIKE(my_col1, '[[:alnum:]]')
To test a string for numeric characters, you could use a combination of the LENGTH, TRIM, AND TRANSLATE functions built into Oracle.

You can use the following command:

LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))

string1 is the string value that you are testing

This function will return a null value if string1 is numeric.
It will return a value "greater than 0" if string1 contains any non-numeric characters.

For example,

LENGTH(TRIM(TRANSLATE('123b', ' +-.0123456789',' '))); would return 1
LENGTH(TRIM(TRANSLATE('a123b', ' +-.0123456789',' '))); would return 2
LENGTH(TRIM(TRANSLATE('1256.54', ' +-.0123456789',' '))); would return null
LENGTH(TRIM(TRANSLATE ('-56', ' +-.0123456789',' ')));

HOW TO CHECK FOR A TABLE IN A PARTICULAR TABLESPACE BEFORE DROPPING IN ORACLE

select
tab.table_name,tab.tablespace_name,tab.avg_row_len ROWCHN,
obj.created CREATEDON,obj.last_ddl_time DDLCHNGON,tab.last_analyzed ANALYSEDON,obj.timestamp TIMESTAMP
from all_tables tab,all_objects obj
where
tab.tablespace_name = 'REGN_BIG1_DAT_LM'
and
obj.object_type = 'TABLE'
and object_name = tab.table_name

HOW TO ESTIMATE THE SIZE OF A TABLE IN BYTES IN ORACLE

To find the size of bytes allocated to a table:

sql > analyze table emp compute statistics;

sql > select num_rows * avg_row_len "Bytes Used" from dba_tables where table_name = 'TEST';

Bytes Used
-----------
560

sql > select bytes "Bytes Allocated" from dba_segments where segment_name = 'TEST';


Bytes Allocated
----------
524288

The result of the query shows that the emp table is using 560 bytes of the 524,288 bytes allocated to it.

HOW TO FIND ALL GRANTS GIVEN TO A SPECIFIC USER (GRANTEE_USER) IN ORACLE

select * from dba_tab_privs where grantor = 'GRANTOR_USER'
and grantee = 'GRANTEE_USER'

How to DROP multiple columns in Oracle

ALTER TABLE MY_TABLE
DROP (
MY_COL1,
MY_COL2
);

How to search in a CLOB field in Oracle

SELECT * FROM MYTABLE WHERE dbms_lob.INSTR(CLOBDATA, 'C00001880386') >0

How to use CASE in WHERE clause Oracle

SELECT * FROM parts p
WHERE
case when slogic = 'begins' and partnum like sPartStrp||'%'
     then 1
     when slogic = 'contains' and partnum like '%'||spartStrp||'%'
     then 1
     when slogic = 'equals' and partnum = sPartStrp
     then 1
     when partnum like sPartStrp || '%'
     then 1
     else 0
 end = 1
and p.stock_type = 1