Useful Tips and Queries for Oracle DBA.

Below are some useful tips regarding 'TEMPORARY TABLESPACE' :

Steps for dropping the Default temporary tablespace::
-----------------------------------------------
Step 1: create one drop1temp tablespace as below make it default.
-------

create tablespace drop1temp tempfile '/star/oradata/STARDEV/droptemp.ora' size 1000m;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE drop1temp;


step2:Drop the original default temporary tablespace now(eg:STA01T):
-----

DROP TABLESPACE STA01T INCLUDING CONTENTS AND DATAFILES;

* SOME THING A BIT TRICKY*

Command for creating New temporary tablespace:
-----------------------------------------------------

CREATE TEMPORARY TABLESPACE STA01T TEMPFILE '/stage/oradata/STARTST/STA01.dbf' SIZE 6000M;

Increasing the size of the temporary tablespace:
-------------------------------------------------------

ORA-01652: unable to extend temp segment by 128 in tablespace STA01T


STEPS FOR ADDING TEMP FILE IN A TEMPORARY TABLESPACE:
---------------------------------------------

Step 1:Add the temporary file.
--------
ALTER TABLESPACE STA01T ADD TEMPFILE '/stage/oradata/STARTST/star01t.dbf' SIZE 5000M;

Step 2:Shut normal and start the Database for changes to take effect.
--------
SQL>Shut immediate;
SQL>startup

Note:Restart the database for changes to take effect...

Step 3:Check the filesystem and space now:
----------------------------------------------
-- The below query can be used for checking datafile size and tempfile sizes.

select file_name||' '||tablespace_name||' '||(bytes)/1024/1024 from dba_data_files;

select file_name||' '||tablespace_name||' '||(bytes)/1024/1024 from dba_temp_files;

We can see a tempfile of size 5000M in our Database.

Below are some useful Queries for Oracle DBA:
-------------------------------------------------------------


BELOW QUERY YOU CAN USE IN ORACLE 11g ony to know all temporay tablespaces details:
-------------------------------------
select tablespace_name,
tablespace_size/1024/1024 "Total Space",
allocated_space/1024/1024 "Alloc Space",
free_space/1024/1024 "Free Space"
from dba_temp_free_space;

SELECT tablespace_name, SUM(bytes_used)/1024/1024, SUM(bytes_free)/1024/1024
FROM V$temp_space_header
GROUP BY tablespace_name;


To know Default temporary tablespace:
---------------------------------------
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

-- The below query can be used to check Default Tablespace and Temporary Tablespace
For Database.

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';

To know Default tablespace & temporary tablespace for user:
------------------------------------------------------------

select username,temporary_tablespace,default_tablespace from dba_users where username='STARREP';

To know Tablespace size Query the below:
-----------------------------------------

SQL>set linesize 1000

SELECT tablespace_name, ROUND(SUM(total_mb)-SUM(free_mb)) CUR_USE_MB, ROUND(SUM(total_mb)) CUR_SZ_MB,
ROUND((SUM(total_mb)-SUM(free_mb))/SUM(total_mb)*100) CUR_PCT_FULL, ROUND(SUM(max_mb) - (SUM(total_mb)-SUM(free_mb))) FREE_SPACE_MB,
ROUND(SUM(max_mb)) MAX_SZ_MB, ROUND((SUM(total_mb)-SUM(free_mb))/SUM(max_mb)*100) PCT_FULL
FROM (
SELECT tablespace_name, SUM(bytes)/1024/1024 FREE_MB,
0 TOTAL_MB, 0 MAX_MB
FROM dba_free_space
GROUP BY tablespace_name
UNION
SELECT tablespace_name, 0 CURRENT_MB,
SUM(bytes)/1024/1024 TOTAL_MB,
SUM(DECODE(maxbytes,0,bytes, maxbytes))/1024/1024 MAX_MB
FROM dba_data_files
GROUP BY tablespace_name)
GROUP BY tablespace_name;

tablespace usage:
=================

SELECT F.TABLESPACE_NAME,TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USEDMB",
TO_CHAR (F.FREE_SPACE, '999,999') "FREEMB",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTALMB",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' FREE
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V$PARAMETER
WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME ) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME ) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

Resizing Tablespace without adding datafile:
--------------------

ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

Checking autoextend on/off for Tablespaces:
select substr(file_name,1,50), AUTOEXTENSIBLE from dba_data_files
(OR)
SQL> select tablespace_name,AUTOEXTENSIBLE from dba_data_files;


Adding datafile to a tablespace:
alter tablespace star02D add datafile '/work/oradata/STARTST/sta05d.dbf' size 1000M autoextend off;

Increasing Datafile size:
-------------------------

Alter Database datafile '/u01/app/Test1_data_01.dbf' resize 2G;


Important:
----------------

Checking the default tablespace and default temp tablespace for ALL userS:

---------------------------------------------------------------------------

SQL>set linesize 1000

SQL> select default_tablespace,temporary_tablespace,username from dba_users;


undo usage details:
===========

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

SID_SERIAL ORAUSER PROGRAM UNDOSEG Undo
---------- ---------- ------------------------------ --------------- -------
260,7 SCOTT sqlplus@localhost.localdomain _SYSSMU4$ 8K
(TNS V1-V3)


who is using a TEMP Segment:
============================

SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;


PGA USAGE:
==========
select st.sid "SID", sn.name "TYPE",
ceil(st.value / 1024 / 1024/1024) "GB"
from v$sesstat st, v$statname sn
where st.statistic# = sn.statistic#
and sid in
(select sid from v$session where username like UPPER('&user'))
and upper(sn.name) like '%PGA%'
order by st.sid, st.value desc


long job:
=========

col username for a20
col message for a50
col remaining for 9999
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc


CPU usage of the USER:
======================

select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
order by VALUE desc;

Running Jobs:
=============

select owner, job_name from DBA_SCHEDULER_RUNNING_JOBS;

select sid, job,instance from dba_jobs_running;

select sid, serial#,machine, status, osuser,username from v$session where username!='NULL';

How to find the Actual size of a Database?
select sum(bytes)/1024/1024/1024 as GB from dba_data_files;

Eg:
SQL> select sum(bytes)/1024/1024/1024 as GB from dba_data_files;

GB
----------
30.9667969

How to find the size occupied by Data in a Database or Database usage details?
select sum(bytes)/1024/1024/1024 as GB from dba_segments;
Eg:
SQL> select sum(bytes)/1024/1024/1024 as GB from dba_segments;

GB
----------
10.0961914
So,In the Above example Database size:30 GB,Database usage:10 GB.

How to find the size of the SCHEMA/USER?
We can find the size of the schema/user by query:

select sum(bytes/1024/1024)"size" from dba_segments where
owner='&owner';

Eg:SQL> select sum(bytes/1024/1024)"size" from dba_segments
where owner='TEST_DEV';

size
----------
2.8125
So,In the above example size occupied by user 'TEST_DEV' is 2.8 MB.

How can a USER be granted privilege for executing a particular PACKAGE?

SQL> grant execute on DBMS_CRYPTO to TEST_USER;

Grant succeeded.

SQL> grant execute on DBMS_RANDOM to TEST_USER;

Grant succeeded.

SQL> grant execute on DBMS_UTILITY to TEST_USER;

Grant succeeded.

Here DBMS_CRYPTO,DBMS_RANDOM,DBMS_UTILITY
Note:For security reason DBA should not give execute privileges on all the packages.


How to see the parameters set in our Oracle Database?

SQL> select sid,name,value from v$spparameter where isspecified='TRUE';


Happy Oracle DBA learning...

Best regards,

Rafi.