Some Good Queries

To get size of a table

select segment_name table_name,sum(bytes)/(1024*1024) table_size_meg
from user_extents where segment_type='TABLE'
and segment_name = 'EMP_MAST' group by segment_name

---------------------------------------------------------------
To Select only unlocked rows

select * from emp_MAST for update skip locked;

CHECK THIS USING TWO SQL PLUS AND SESSION BROWSER IN TOAD

UPDATE EMP_MAST SET ENAME='p' WHERE EMPNO=7369 ; (IN FIRST SQLPLUS DONT EXECUTE COMMIT )

select * from emp_MAST for update skip locked; (IN SECOND SQLPLUS)
--------------------------------------------------------
To get numbers of records in all tables in a Schema

select table_name,to_number(extractvalue(xmltype(
dbms_xmlgen.getxml('select count(*) c from '||table_name)
),'/ROWSET/ROW/C')) count from user_tables order by 1

---------------------------------------------------------
To generate a CSV output of a Table

select regexp_replace(column_value,' *<[^>]*>[^>]*>',';')
from table(xmlsequence(cursor(select * from EMP_MAST)));

--------------------------------------------------------------
To get Version and login naem

select OLAPSYS.version,sys.LOGIN_USER from dual
--------------------------------------------------

select APEX_UTIL.get_since(sysdate-10) /* get how many days ago */,APEX_UTIL.url_encode('http://www.oracle4u.com')   from dual;

-------------------------------------------
select round(12.55555E78,2) from dual ---- For numeric overflow  round function will not work

------------------------------------------
How to do pattern search in a subquery using LIKE


with dt as( select distinct  BOTTLETYPE  from  DRINKS WHERE BOTTLETYPE is not null )
select * from  tabled,dt where name like ''||dt.BOTTLETYPE||'%' order by 2

OR

select * from  tabled df,(select distinct BOTTLETYPE from DRINKS WHERE BOTTLETYPE is not null) gh   where name like ''||gh.BOTTLETYPE||'%' order by 2


OR

SELECT  * FROM  tabled df WHERE EXISTS (SELECT distinct BOTTLETYPE  FROM DRINKS gh WHERE BOTTLETYPE is not null and df.name LIKE ''||gh.BOTTLETYPE||'%') ;
----------------
Check for hidden database user
----------------
Run OS Commands via PLSQL
----------------
Run OS Commands via DBMS_SCHEDULER
----------------
Run OS Commands via Create Table
--------------------------------