Tablespaces

     Tablespace 

      A tablespace is a logical storage unit in an oracle database (its not a physical file), But tablespace inturn consists of atleast one datafile from the database. and that datafile is physicaly located in the file system on the database server , 

Our actual data's (Table,Index) will be segregated on this datafile . 

The above details are very basic understanding of an oracle tablespace and datafile. But i am not intended to write about the datafiles/tablespaces here . I am trying to have some space given here help me/others when we perform some task on the datafile and tablespaces . 

 At first , There are three types of tablespaces in oracle 

• Permanent Tablespaces
• Undo Tablespaces
• Temporary Tablespaces

Tasks that may involve to deal with permenant tablespaces were written below , if i miss anything here pls post back to add them, bcz i am trying to have some site to guide all the tasks that would help others / me . 

At First to check the tablespace usage :

I felt the below query helps to figure out what tablespaces that may require our attention to add more space . 

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4

/

Lets say if we need to see what are the files associated with each tablespace 

Query the below one ,

set lines 100
col file_name format a70
select file_name
,      ceil(bytes / 1024 / 1024) "size MB"
from   dba_data_files
where  tablespace_name like '&TSNAME'

/

Tablespaces that are >=80% full, and how much to add to make them 80% again

set pages 999 lines 100
col "Tablespace" for a50
col "Size MB"  for 999999999
col "%Used"  for 999
col "Add (80%)"  for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from    dba_data_files group by tablespace_name) tsu
,  (select ts.tablespace_name
 ,       nvl(sum(bytes)/1024/1024, 0) free_mb
 from    dba_tablespaces ts, dba_free_space fs
 where   ts.tablespace_name = fs.tablespace_name (+)
 group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/

User quotas on all tablespaces

col quota format a10
select username
,      tablespace_name
,      decode(max_bytes, -1, 'unlimited'
       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  tablespace_name not in ('TEMP')
/

List all objects in a tablespace

 

set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner
,      segment_name
,      segment_type
from   dba_segments
where  lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/

 

 

Show all tablespaces used by a user

select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/

 

Create a temporary tablespace

create temporary tablespace temp
tempfile '' size 500M
/

Alter a databases default temporary tablespace

alter database default temporary tablespace temp
/

Show segments that are approaching max_extents

col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

List the contents of the temporary tablespace(s)

set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid 
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

Alter tablespace: Backups

ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;

Alter tablespace: Data Files and Tempfiles

ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
12 Portable DBA: Oracle
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;

Alter tablespace: Rename

ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;

Alter tablespace: Tablespace Management

ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;

Create tablespace: Permanent Tablespace

CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 10G;

Create tablespace: Temporary Tablespace

CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;
create tablespace: Undo Tablespace
CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;

To drop user objects

set trimspool on
set pagesize 0
set line 1000
set feed off
set verify off

spool genera_dropallbmc_smgr.sql
select 'prompt Conectando como &&OWNER' || chr(10) || 'connect &&OWNER' DROP_OBJECTS
from dual
union all
select 'spool dropall.log' DROP_OBJECTS
from dual
union all
Select
trim(case
when object_type = 'TABLE' then 'drop ' || object_type
|| ' ' || owner || '.' || object_name ||' cascade constraints'
when object_type = 'PACKAGE BODY' then 'prompt PACKAGES BODY'
when object_type = 'INDEX' then 'prompt INDEXES'
when object_type = 'DATABASE LINK' then 'drop ' || object_type
|| ' ' || object_name
else 'drop ' || object_type || ' ' || owner || '.' || object_name
end || ';')
from dba_objects
where owner = '&&OWNER'
and object_type not like '%PARTITION%'
union all
select 'drop public synonym '||synonym_name||';'
from dba_synonyms
where table_owner = '&&OWNER'
and owner = 'PUBLIC'
union all
select 'spool off'
from dual
;
spool off

exit

To grab the index details

set pagesize 300
COLUMN owner FORMAT A10
COLUMN index_name FORMAT A35
COLUMN tablespace_name FORMAT A15

Select owner, index_name, tablespace_name
From dba_indexes
Where owner NOT IN('SYSTEM','DBSNMP', 'ORDSYS', 'OUTLN','SYS')
and table_type = 'TABLE'
Order By owner, index_name, tablespace_name;

Session counts

SELECT name, value
FROM v$parameter
WHERE name = 'sessions'


SELECT COUNT(*)
FROM v$session

SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions'


SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$LICENSE VL

Currently connected users

set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address
-- and b.status = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
-- ACTVE TRANSACTION ON THAT MOMENT */
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
order by c.spid,a.hash_value,a.piece
/
run it across




column username format a20
column osuser format a20
select sid, serial#, username, osuser
from v$session
where audsid=userenv('SESSIONID');



select username,terminal from

select osuser from V$SESSION_CONNECT_INFO


select username,terminal from v$process;

To Check the active sql scripts

select chha.sid,
2 chha.username,
3 chha.osuser,
4 to_char(chha.LOGON_TIME,'MM-DD-YY HH:MI AM') login_time,
5 chhb.sql_text "SQL Statement"
6 from v$session chha, v$sqltext chhb, V$SQL_CURSOR chhc
7 where chha.username is not null
8 AND chhc.parent_handle (+) = chhb.address
9 AND chhB.SQL_TEXT NOT LIKE '%chh%'
10 AND chhB.SQL_TEXT NOT LIKE '%V$SESSION%'
11 AND chhB.SQL_TEXT NOT LIKE '%v$session%'
12 and chha.status = 'ACTIVE'
13 and chha.sql_address = chhb.address
14 --and chha.osuser = 'dwloder'
15* order by 1,2, 3,chhb.piece