Oracle sql statements to get quick information about database state and get daily things done.

oracle_survival_tips.md

Oracle Survival Tips

SQL statements to get quick information about database state and get daily things done. Statements were tested on 12c Release 1 and 2 environments, but most of them also apply to 11g.

Summary

Database management

• Startup database
• Turn off database
• Locate Oracle Home

Tablespaces

• Move table from tablespace
• Set default and temporary user tablespace
• Check default and temporary tablespace for user
• Drop tablespace and datafiles
• Add datafile in tablespace
• List datafiles and tablespaces
• List tables from tablespace

Indexes

• Move index to another tablespace
• Check all indexes in tablespace
• Rebuild index
• Get DDL statement from index
• Dinamically move all indexes to a new tablespace

Dealing with dates

• Check last day of current month
• Check first day of current month
• Add months
• Subtract months
• Extract day of date
• Extract month of date
• Extract year of date
• Function to identify wrong date conversion

Dealing with numbers

• Function to identify wrong number conversion

Database Link

• Copy table from one database to another

Data modification

• Last DML modification on table

Users and sessions

• Unlock user setting a new password
• Select active sessions
• Check sessions running on background
• Check locked objects with command to kill session
• Check query locked
• Check event and state session
• Verify if client is 32b or 64b
• Change NLS parameters for all schemas after logon
• Change NLS parameters for ingle schema after logon

Memory management

• Check memory used by each proccess

CPU management

• Check CPU usage

Disk space management

• Check space used by table
• Check total space, spaced used and free space in database
• Check total space, spaced used and free space per tablespace
• Check free space in temporary tablespace
• Shrink space in temporary tablespace

REDO management

• Check REDO usage

Database management

• Startup database (step-by-step)

startup nomount;
alter database mount;
alter database open;

• Turn off database

shutdown immediate;

• Locate Oracle Home

select SYS_CONTEXT ('USERENV','ORACLE_HOME') from dual;

Tablespaces

• Move table from tablespace

ALTER TABLE <table_name> MOVE TABLESPACE <tablespace_name>;

• Set default and temporary user tabkespace

ALTER USER <username> 
DEFAULT TABLESPACE <tablespace_name> 
TEMPORARY TABLESPACE <temp_tablespace_name> 
QUOTA UNLIMITED ON <tablespace_name>;

• Check default and temporary tablespace for user

select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
from DBA_USERS
where USERNAME='<username>';

• Drop tablespace and datafiles

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;

• Add datafile in tablespace

ALTER TABLESPACE <tablespace_name> ADD DATAFILE '<path\datafile.dbf>' SIZE <tamanho em MB>M ;

• List datafiles and tablespaces

select
substr(file_name,1,40) as File_Name,
substr(tablespace_name,1,10) as Tablespace_Name,
bytes/1024/1024 as Size_Mb
from dba_data_files
order by tablespace_name, file_name;

• List tables from tablespace

SELECT * FROM ALL_ALL_TABLES WHERE OWNER = <schema_name> AND TABLESPACE_NAME = <tablespace_name> ORDER BY TABLE_NAME;

Indexes

• Move index to another tablespace

Alter index <index_name> rebuild tablespace <tablespace_name>;

• Check all indexes in tablespace

select INDEX_NAME, tablespace_name from all_indexes where owner = <schema_name> AND TABLESPACE_NAME = <tablespace_name>;

• Rebuild index

alter index <owner>.<index_name> rebuild;

• Get DDL statement from index

SELECT dbms_metadata.get_ddl('INDEX','<Iindex_name>','<owner>') FROM DUAL;

• Dinamically move all indexes to a new tablespace

DECLARE
  l_sql_stmt VARCHAR2(1000);
BEGIN
  FOR i IN (SELECT owner,
                   index_name
              FROM all_indexes
             WHERE tablespace_name = 'tablespace_name')
  LOOP
    l_sql_stmt := 'ALTER INDEX ' || i.owner || '.' || i.index_name ||
                  '  REBUILD TABLESPACE new_tablespace_name';
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;

Dealing with dates

• Check last day of current month

select last_day(sysdate) FROM DUAL;

• Check first day of current month

select trunc(sysdate, 'mm') FROM DUAL;

• Add months

select add_months(sysdate,<quantidade>) from dual;

• Subtract months

select add_months(sysdate,-<quantidade>) from dual;

• Extract day of date

select extract (day from sysdate) from dual;

• Extract month of date

select extract (month from sysdate) from dual;

• Extract year of date

select extract (year from sysdate) from dual;

• Function to identify wrong date conversion

CREATE OR REPLACE FUNCTION INVALID_DATE(v_date IN VARCHAR2) RETURN VARCHAR2 IS
v_date1 DATE;
BEGIN
select to_date(v_date, 'DD/MM/RRRR') into v_date1 from dual;
RETURN 'VALID DATE FORMAT';
Exception WHEN Others THEN
--returns only values that couldn't be converted into date format
RETURN v_date;
END;

Dealing with numbers

• Function to identify wrong number conversion

create or replace FUNCTION INVALID_NUMBER(v_num IN VARCHAR2) RETURN VARCHAR2 IS
  v_n1 varchar2(20);
BEGIN
    select to_number(v_num)into v_n1 from dual;
        RETURN 'VALID NUMBER';
    Exception WHEN Others THEN
    --returns only values that couldn't be converted into number
        RETURN v_num;
END;

Database Link

• Copy table from one database to another

COPY FROM <username>/<pasword>@<dblink> TO <username_destiny>/<pasword_destiny>@<dblink_destiny> CREATE <tablename> USING SELECT * FROM <tablename>;

Data modification

• Last DML modification on table

select max(ora_rowscn), scn_to_timestamp(max(ora_rowscn)) from <tablename>;

Users and sessions

• Unlock user setting a new password

alter user <user_name> identified by <new_password> account unlock;

• Select active sessions

SELECT s.inst_id, s.sid, s.SERIAL#, s.STATUS, s.USERNAME, s.MACHINE FROM gv$session s WHERE USERNAME = USER;

• Check sessions running on background

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';

• Check locked objects with command to kill session

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name ,
'alter system kill session '''|| C.sid||', '||C.serial#||'''' ||';'
FROM V$Locked_Object A, All_Objects B , v$session C
WHERE A.Object_ID = B.Object_ID
and A.session_id = C.sid;

• Check query locked

SELECT a.sid, a.serial#, b.sql_text FROM v$session a, v$sqlarea b WHERE a.sql_address=b.address;

• Check event and state session

select sid, seq#, EVENT,  WAIT_CLASS,  SECONDS_IN_WAIT from v$session_wait where sid in (
SELECT C.sid
FROM V$Locked_Object A, All_Objects B , v$session C
WHERE A.Object_ID = B.Object_ID
and A.session_id = C.sid);

• Verify if client is 32b or 64b if address returned is 16 characters long client is 64b, if 8 characters are obtained in return it is 32b

select distinct address from v$sql where rownum<2;

• Change NLS parameters for all schemas after logon

--Execute with sys user
CREATE OR REPLACE TRIGGER trg_after_logon AFTER LOGON ON DATABASE
BEGIN
   execute immediate 'alter session set NLS_DATE_FORMAT=''DD/MM/RRRR''';
   execute immediate 'alter session set NLS_NUMERIC_CHARACTER=''. ''';
END;
/
AUDIT SESSION;

• Change NLS parameters for ingle schema after logon

--Execute with sys user
CREATE OR REPLACE TRIGGER <username>.trg_nls_format
   AFTER LOGON ON DATABASE WHEN (USER = '<username>')
BEGIN
   execute immediate 'alter session set NLS_DATE_FORMAT=''DD/MM/YYYY''';
   execute immediate 'alter session set NLS_NUMERIC_CHARACTERS=''. ''';
END;
/
AUDIT SESSION BY <username>

Memory management

• Check memory used by each proccess

SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+);

CPU management

• Check CPU usage

SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE t.STATISTIC# = n.STATISTIC#
AND NAME like '%CPU used by this session%'
AND t.SID = s.SID
AND s.status='ACTIVE'
AND s.username is not null
GROUP BY username,t.sid,s.serial#

Disk space management

• Check space used by table

select sum(bytes/1024/1024/1024) "Total Space (GB)"
from dba_segments where segment_name = "<table_name>";

• Check total space, spaced used and free space in database

SELECT ROUND(SUM(used.bytes) / 1024 / 1024 / 1024 )
|| ' GB' "Database Size" ,
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024 ) - ROUND(free.p / 1024 / 1024 / 1024)
|| ' GB' "Used space" ,
ROUND(free.p / 1024 / 1024 / 1024)
|| ' GB' "Free space"
FROM
(SELECT bytes FROM v$datafile
UNION ALL
SELECT bytes FROM v$tempfile
UNION ALL
SELECT bytes FROM v$log
) used ,
(SELECT SUM(bytes) AS p FROM dba_free_space
) free
GROUP BY free.p;

• Check total space, spaced used and free space per tablespace

select df.tablespace_name "Tablespace",
totalusedspace / 1024/ 1024 / 1024 "Used GB",
(df.totalspace - tu.totalusedspace)/ 1024/ 1024/ 1024"Free GB",
df.totalspace / 1024/ 1024/ 1024 "Total GB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
from (select tablespace_name,
round(sum(bytes) ) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)) totalusedspace,
tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and df.totalspace <> 0;

• Check free space in temporary tablespace

SELECT
tablespace_name,
tablespace_size / 1024 / 1024 / 1024 "TBS Size(GB)",
allocated_space / 1024 / 1024 / 1024 "Used Size(GB)",
free_space / 1024 / 1024 / 1024 " Free Size(GB)"
FROM
dba_temp_free_space;

• Shrink space in temporary tablespace

ALTER TABLESPACE temp SHRINK SPACE KEEP 1G;

REDO management

• Check REDO usage

SELECT
 a.GROUP#,
 a.THREAD#,
 a.SEQUENCE#,
 a.ARCHIVED,
 a.STATUS,
 b.MEMBER AS REDOLOG_FILE_NAME,
 (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ;