free space in tablespace

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
--AND  df.tablespace_name  LIKE  'UNDO%'
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+)  = df.tablespace_name
--and   df.tablespace_name LIKE  'UNDO%'
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

 ORDER BY 4 DESC;

Oracle, resource-consuming queries

SELECT DISTINCT p.Sql_Id AS Sql_Id,
                CASE
                  WHEN p.Id = 0 THEN
                   Sq.Module
                  ELSE
                   NULL
                END AS Module,
                p.Id,
                Lpad(' ', 4 * Depth) || p.Operation AS Operation,
                p.Options AS "Access",
                p.Object_Name AS "Object",
                p.Cost AS "Cost",
                Trunc(p.Cpu_Cost / 1000000, 2) AS "Cpu_Seconds",
                p.Cardinality,
                p.Io_Cost AS "IO Cost",
                p.TIME,
                p.Filter_Predicates,
                p.Access_Predicates,
                CASE
                  WHEN p.Id = 0 THEN
                   Sq.Sql_Text
                  ELSE
                   NULL
                END AS Sql_Text,
                CASE
                  WHEN p.Id = 0 THEN
                   Sq.Last_Active_Time
                  ELSE
                   NULL
                END AS Last_Active_Time,
                Filtro.Costosum
  FROM V$sql_Plan p
INNER JOIN V$sql Sq
    ON Sq.Sql_Id = p.Sql_Id
INNER JOIN (SELECT Filtro_Rownum.Sql_Id, Filtro_Rownum.Costosum
               FROM (SELECT Tmp.Sql_Id, SUM(Tmp.Cost) AS Costosum
                       FROM (SELECT s.Sql_Text,
                                    p.Sql_Id,
                                    p.Plan_Hash_Value,
                                    p.Cost
                               FROM V$sql s
                              INNER JOIN V$sql_Plan p
                                 ON s.Sql_Id = p.Sql_Id
                              WHERE 1 = 1
                                AND Upper(s.Sql_Text) NOT LIKE
                                    '%EXPLAIN PLAN%'
                                AND s.Last_Active_Time > SYSDATE - 0.3
                                AND upper(s.Module) <> Upper('PL/SQL Developer')
                                AND p.Cost IS NOT NULL
                                AND p.ID = 0
                              ORDER BY p.Cost     DESC,
                                       p.Cpu_Cost DESC,
                                       p.Io_Cost  DESC) Tmp
                     GROUP BY Tmp.Sql_Id
                      order by sum(tmp.cost) desc) Filtro_Rownum
              WHERE Rownum < 200) Filtro
    ON Filtro.Sql_Id = p.Sql_Id
ORDER BY Filtro.Costosum DESC, p.Sql_Id, p.Id

inspect Oracle active sessions

Here come queries useful to see the current running sessions, together with the sql being executed and the associated events

select u.username, ash.session_id, ash.session_serial#, ash.event, count(*), ash.SQL_ID, s.sql_text
      from gv$active_session_history ash, v$sql s, V$SESSION u
      where ash.sql_id=s.sql_id and u.user# = ash.user_id
    group by ash.session_id, ash.session_serial#, u.username, ash.SQL_ID, ash.event, sql_text
    order by 1;

select se.username, se.sid, se.serial#, sq.sql_fulltext
      from V$SESSION se, v$sql sq
      where se.sql_id = sq.sql_id
order by 1

gathering statistics on Oracle DB

Here comes a script to gather statistics on Oracle DB

begin

  dbms_stats.unlock_schema_stats('SCHEMA');
  dbms_stats.delete_schema_stats('SCHEMA');
  dbms_stats.gather_schema_stats(ownname       => 'SCHEMA',
                                estimate_percent => 40.0,
                                granularity      => 'GLOBAL',
                                block_sample     => TRUE,
                                cascade          => TRUE,
                                degree           => DBMS_STATS.DEFAULT_DEGREE,
                                method_opt       => 'for all columns size 1');

  dbms_stats.lock_schema_stats('SCHEMA');

end;



select t.owner, t.table_name, t.last_analyzed from dba_all_tables t
where lower(t.owner) = 'your_schema';

Oracle roles and privileges

The following very useful scripts are taken out from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html (thanks to René Nyffenegger) Users to roles and system privileges This is a script that shows the hierarchical relationship between system privileges, roles and users.

select
  lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
  (
  /* THE USERS */
    select 
      null     grantee, 
      username granted_role
    from 
      dba_users
    where
      username like upper('%&enter_username%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      grantee,
      granted_role
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      grantee,
      privilege
    from
      dba_sys_privs
  )
start with grantee is null
connect by grantee = prior granted_role;

System privileges to roles and users This is also possible the other way round: showing the system privileges in relation to roles that have been granted this privilege and users that have been granted either this privilege or a role:

select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select 
      null   p, 
      name   c
    from 
      system_privilege_map
    where
      name like upper('%&enter_privliege%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select 
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;

Oracle export/import

here comes an example of data export:

the export must be performed on the source db server, the import on the target db server, since a local directory is required for the Oracle db server to export/import data

-- Create datapump directory
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'D:\Backup\Oracle\DataPumpDir';
GRANT READ,WRITE ON DIRECTORY DATA_PUMP_DIR TO myorauser;

-- Check datapump directory
SELECT * FROM ALL_DIRECTORIES;

expdp myorauser/password@SID directory=DATA_PUMP_DIR DUMPFILE=myorauserDump.dmp SCHEMAS=myorauser

impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=myorauserDump.dmp SCHEMAS=myorauser

when you are asked credentials, don't use the SYS users, use another user with powerful privileges (as SYSTEM)
-always check that indexes have been correctly created in the target db
-the option CONTENT=DATA_ONLY imports only data
-in the target db must be created a tablespace with the same name of the tablespace containing the Oracle schema in the source db

Oracle, table size

The following query (to be run as sys user) is useful to calculate the space used by all tables


SELECT OWNER,
OBJECT_NAME,
OBJECT_TYPE,
TABLESPACE_NAME,
NUM_ROWS,
SUM(KBYTES) KBYTES,
TO_CHAR(DECODE(NUM_ROWS, 0, 0, SUM(KBYTES) / (NUM_ROWS)), '9999.99') KB_ROW
FROM (SELECT LO.OWNER,
LO.TABLE_NAME AS OBJECT_NAME,
EX.SEGMENT_TYPE AS OBJECT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS AS NUM_ROWS,
ROUND((SUM(EX.BYTES) / 1024)) AS KBYTES
FROM DBA_EXTENTS EX
JOIN DBA_LOBS LO ON (EX.OWNER = LO.OWNER AND
EX.SEGMENT_NAME = LO.SEGMENT_NAME)
JOIN DBA_TAB_STATISTICS TS ON (LO.OWNER = TS.OWNER AND
LO.TABLE_NAME = TS.TABLE_NAME)
GROUP BY LO.OWNER,
LO.TABLE_NAME,
LO.COLUMN_NAME,
EX.SEGMENT_NAME,
EX.SEGMENT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS
UNION ALL
SELECT EX.OWNER,
EX.SEGMENT_NAME AS OBJECT_NAME,
EX.SEGMENT_TYPE AS OBJECT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS,
ROUND((SUM(EX.BYTES) / 1024)) AS KBYTES
FROM DBA_EXTENTS EX
LEFT JOIN DBA_LOBS LO ON EX.OWNER = LO.OWNER
AND EX.SEGMENT_NAME = LO.SEGMENT_NAME
JOIN DBA_TAB_STATISTICS TS ON EX.OWNER = TS.OWNER
AND EX.SEGMENT_NAME = TS.TABLE_NAME
GROUP BY EX.OWNER,
EX.SEGMENT_NAME,
EX.SEGMENT_TYPE,
EX.TABLESPACE_NAME,
TS.NUM_ROWS)
WHERE OWNER LIKE 'CF%'
GROUP BY OWNER, OBJECT_NAME, OBJECT_TYPE, TABLESPACE_NAME, NUM_ROWS
ORDER BY KBYTES DESC;

Oracle database, Monitoring open cursors

 Another great article of Natalka Roshak
http://www.orafaq.com/node/758
on how to monitor open (and cached) cursors

inspect Oracle db

to get a full list of all the objects in the data dictionary:

select * from dictionary order by table_name










show database version and parameters
select * from v$version
select * from nls_database_parameters

V$ Views
http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

What's blocking my lock?

Here comes an extremely clear and interesting article, written by Natalka Roshalk, about Oracle locks and how to monitor what's going on when a lock is blocking a session

read the article