10 principales consultas que debe de aprender un DBA:

1--Versión de Oracle:
select value
from v$system_parameter
where name = 'compatible'

select * from V$VERSION

select version from v$instance

2--Diccionario de datos
select * from dictionary

select table_name from dictionary


3--Roles y privilegios por roles:
select * from role_sys_privs

4--Obtener los roles existentes en Oracle Database:
select * from DBA_ROLES

5--Reglas de integridad y columna a la que afectan:
select constraint_name, column_name
from sys.all_cons_columns

6--Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,...):
Select  * FROM dba_users

7--Tablas del usuario actual:
select * from user_tables

8--Tablas de las que es propietario un usuario, en este caso "HR":
SELECT table_owner, table_name
from sys.all_synonyms
where table_owner like 'HR'

9--Vista que muestra todos los objetos de la base de datos
select * from all_objects

10----Ver los comentatios del esquema actual
select * from all_tab_comments

Script de la vista EMP_DETAILS_VIEW para el esquema HR de Oracle

-- EMP_DETAILS_VIEW  (View)
--

CREATE OR REPLACE FORCE VIEW EMP_DETAILS_VIEW
(
   EMPLOYEE_ID,
   JOB_ID,
   MANAGER_ID,
   DEPARTMENT_ID,
   LOCATION_ID,
   COUNTRY_ID,
   FIRST_NAME,
   LAST_NAME,
   SALARY,
   COMMISSION_PCT,
   DEPARTMENT_NAME,
   JOB_TITLE,
   CITY,
   STATE_PROVINCE,
   COUNTRY_NAME,
   REGION_NAME
)
AS
   SELECT e.employee_id,
          e.job_id,
          e.manager_id,
          e.department_id,
          d.location_id,
          l.country_id,
          e.first_name,
          e.last_name,
          e.salary,
          e.commission_pct,
          d.department_name,
          j.job_title,
          l.city,
          l.state_province,
          c.country_name,
          r.region_name
     FROM employees e,
          departments d,
          jobs j,
          locations l,
          countries c,
          regions r
    WHERE     e.department_id = d.department_id
          AND d.location_id = l.location_id
          AND l.country_id = c.country_id
          AND c.region_id = r.region_id
          AND j.job_id = e.job_id
   WITH READ ONLY;

& como parámetro en Oracle

SELECT dname FROM dept
WHERE deptno = &deptno --& sirve como parametro

Lo que hay que saber sobre SQLX

Ejemplos de XML ORACLE:

--      SYS_XMLGEN:

SELECT SYS_XMLGEN(email)        

    FROM employees

    WHERE employee_id = 205;

--      SYS_XMLAGG:

SELECT SYS_XMLAGG(SYS_XMLGEN(last_name)) XMLAGG

   FROM employees

   WHERE last_name LIKE 'R%'

   ORDER BY xmlagg;

--      XMLELEMENT:

SELECT XMLELEMENT("Emp", XMLELEMENT("Name", 

   e.job_id||' '||e.last_name),

   XMLELEMENT("Hiredate", e.hire_date)) as "Result"

   FROM employees e WHERE employee_id > 200;

--      XMLATTRIBUTES:

SELECT XMLELEMENT("Emp", XMLATTRIBUTES(e.employee_id, e.last_name),

   XMLELEMENT("Dept", XMLATTRIBUTES(e.department_id,

   (SELECT d.department_name FROM departments d

   WHERE d.department_id = e.department_id) as "Dept_name")),

   XMLELEMENT("salary", e.salary),

   XMLELEMENT("Hiredate", e.hire_date)) AS "Emp Element"

   FROM employees e

   WHERE employee_id = 205;

--      XMLFOREST:

SELECT XMLELEMENT("Emp", 

   XMLFOREST(e.employee_id, e.last_name, e.salary))

   "Emp Element"

   FROM employees e WHERE employee_id = 204;

--      XMLCONCAT:

SELECT XMLCONCAT(XMLELEMENT("First", e.first_name),

   XMLELEMENT("Last", e.last_name)) AS "Result"

   FROM employees e

   WHERE e.employee_id > 202;

--      XMLAGG:

SELECT XMLELEMENT("Department",

   XMLAGG(XMLELEMENT("Employee", e.job_id||' '||e.last_name)

   ORDER BY last_name))

   as "Dept_list"     

   FROM employees e

   WHERE e.department_id = 30;

--      XMLPI:

SELECT XMLPI(NAME "Order analysisComp", 'imported, reconfigured, disassembled')

   AS "XMLPI" FROM DUAL;

--      XMLCOMMENT:

SELECT XMLCOMMENT('OrderAnalysisComp imported, reconfigured, disassembled')

   AS "XMLCOMMENT" FROM DUAL;

--   XMLROOT:

SELECT XMLROOT ( XMLType('143598'), VERSION '1.0', STANDALONE YES)

   AS "XMLROOT" FROM DUAL;

--   XMLSERIALIZE:

SELECT XMLSERIALIZE(CONTENT XMLTYPE('Grandco'))

   FROM DUAL;

--   XMLCDATA:

SELECT XMLELEMENT("PurchaseOrder",

   XMLAttributes(dummy as "pono"),

   XMLCdata('

   ]>')) "XMLCData" FROM DUAL;