Tablespace ORACLE

Oracle list tablespaces

SELECT DISTINCT TABLESPACE_NAME FROM DBA_EXTENTS ORDER BY TABLESPACE_NAME;

SELECT TABLESPACE_NAME,
       sum(BYTES / 1024 / 1024) AS MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

Oracle tablespace tables list

SELECT e.OWNER, e.SEGMENT_NAME, e.TABLESPACE_NAME, sum(e.bytes) / 1048576 AS Megs
FROM dba_extents e
WHERE
  e.OWNER = 'MY_USER' AND
  e.TABLESPACE_NAME = 'MY_TABLESPACE'
GROUP BY e.OWNER, e.SEGMENT_NAME, e.TABLESPACE_NAME
ORDER BY e.TABLESPACE_NAME, e.SEGMENT_NAME;

Oracle tablespace usage

set echo off feedback off verify off pages 75
col tablespace_name format a20 head 'Tablespace Name'
col total format 999,999,999,999 head 'Total(KB)'
col used format 999,999,999,999 head 'Used(KB)'
col free format 999,999,999,999 head 'Free(KB)'
col pct format 999 head 'Percent|Used'
break on report
compute sum of total on report
compute sum of used on report
compute sum of free on report
select    tbs.tablespace_name,
tot.bytes/1024 total,
tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used,
sum(nvl(fre.bytes,0))/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct
from      dba_free_space fre,
(select  tablespace_name, sum(bytes) bytes
from      dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where   tot.tablespace_name    = tbs.tablespace_name
and        fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
union
select    tsh.tablespace_name,
dtf.bytes/1024 total,
sum(nvl(tsh.bytes_used,0))/1024 used,
sum(nvl(tsh.bytes_free,0))/1024 free,
(1-sum(nvl(tsh.bytes_free,0))/dtf.bytes)*100 pct
from             v$temp_space_header tsh,
(select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) dtf
where   dtf.tablespace_name     = tsh.tablespace_name(+)
group by tsh.tablespace_name, dtf.bytes/1024, dtf.bytes
order by 1
/

Source: techgoeasy.com

Tablespace ORACLE

CREATE TABLESPACE
TBS_NOME_TABLESPACE
DATAFILE 'NOME_DATAFILE.dbf' SIZE 40M ONLINE;

-1

Source: devmedia.com.br

Tablespace ORACLE

CREATE TABLE TB_NOME_TABELA
(
CODIGO NUMBER(38)
)TABLESPACE TBS_NOME_TABLESPACE;

-1

Source: devmedia.com.br

Similar codes

0 vote213 views1 answer
Command to give readonly access to a postgres sql user
SQL
1 vote183 views1 answer
Sql select all from one table and one column from another
SQL
1 vote176 views1 answer
Sql delete row with auto increment
SQL
0 vote180 views1 answer
LoadError: cannot load such file -- mysql2/2.7/mysql2
SQL
0 vote205 views1 answer
Oracle execute immediate quotes
SQL
0 vote221 views1 answer
Sql basic commands
SQL
-1 vote228 views1 answer
Sql vs nosql or mysql vs mongodb
SQL
0 vote224 views2 answers
Postgres isnull
SQL
0 vote213 views1 answer
Mysql sleep connections
SQL
2 votes184 views1 answer
How to add where command in update comand with joins
SQL