Quote Operator

select 'Oracle''s web blog. It''s personal.' str from dual;

By Q - quote operator the above statement can also be represented as any one of the below.


Different ways to Use Quote Operator.

select q'(Oracle's web blog. It's personal.)' str from dual;
select q'[Oracle's web blog. It's personal.]' str from dual;
select q'Oracle's web blog. It's personal.A' str from dual;
select q'/Oracle's web blog. It's personal./' str from dual;
select q'ZOracle's web blog. It's personal.Z' str from dual;
select q'|Oracle's web blog. It's personal.|' str from dual;
select q'+Oracle's web blog. It's personal.+' str from dual;
select q'zOracle's web blog. It's personal.z' str from dual;

Locks , Dummy Table

To get the locks on an object

SELECT oracle_username USERNAME,owner OBJECT_OWNER,object_name, object_type, s.osuser,s.SID SID,s.SERIAL# SERIAL,DECODE(l.block,
0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode) ) MODE_HELD
FROM gv$locked_object v, dba_objects d,gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) and v.session_id = s.sid ORDER BY oracle_username, session_id;

-------------------------------------------------------------
To create our own much faster DUMMY Table


CREATE TABLE MYDUAL(DUMMY VARCHAR2(1) PRIMARY KEY CONSTRAINT ONE_ROW
CHECK(DUMMY='X')) ORGANIZATION INDEX;

Database vs Data warehouse

Similarities in Database and Data warehouse:

* Both database and data warehouse are databases.
* Both database and data warehouse have some tables containing data.
* Both database and data warehouse have indexes, keys, views etc.

Differences

* The Application database is not your Data Warehouse for the simple reason that your application database is never designed to answer queries.

* The database is designed and optimized to record while the data warehouse is designed and optimized to respond to analysis questions that are critical for your business.

* Application databases are On-Line Transaction processing systems where every transition has to be recorded, and super-fast at that.

* A Data Warehouse on the other hand is a database that is designed for facilitating querying and analysis

* A data warehouse is designed as On-Line Analytical processing systems . A data warehouse contains read-only data that can be queried and analyzed far more efficiently as compared to regular OLTP application databases. In this sense an OLAP system is designed to be read-optimized.

* Separation from your application database also ensures that your business intelligence solution is scalable, better documented and managed and can answer questions far more efficiently and frequently.

Data warehouse is better than a database:

The Data Warehouse is the foundation of any analytics initiative. You take data from various data sources in the organization, clean and pre-process it to fit business needs, and then load it into the data warehouse for everyone to use. This process is called ETL which stands for ‘Extract, transform, and load'.

Suppose you are running your reports off the main application database. Now the question is would the solution still work next year with 20% more customers, 50% more business, 70% more users, and 300% more reports? What about the year after next? If you are sure that your solution will run without any changes, great!! However, if you have already budgeted to buy new state-of-the-art hardware and 25 new Oracle licenses with those partition-options and the 33 other cool-sounding features, you might consider calling up Oracle and letting them know. There's a good chance they'd make you their brand ambassador.

Creation of a data warehouse leads to a direct increase in quality of analyses as you can keep only the needed information in simpler tables, standardized, and denormalized to reduce the linkages between tables and the corresponding complexity of queries.

A data warehouse drastically reduces the cost-per-analysis and thus permits more analysis per FTE.


(Data Adopted)

Check Constraint

Check Constraint

CREATE TABLE EMP_DETAILS(
NAME VARCHAR2(20),
MARITAL CHAR(1) CHECK(MARITAL='S' OR MARITAL = 'M'),
CHILDREN NUMBER,
CHECK((MARITAL = 'S' AND CHILDREN=0) OR
(MARITAL = 'M' AND CHILDREN >=0)));

This constraint can only be kept at table level because it accesses more than one field. Now the valid values for CHILDREN field is based on MARITAL field. MARITAL is also one of 'S' or 'M'. If the field MARITAL is 'S' it will only allow 0 as a valid value. If the MARITAL field is 'M' it will allow either 0 or a value greater than 0.

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
--------------------------------