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 --------------------------------
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 --------------------------------
No comments:
Post a Comment