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

28 votes305 views6 answers
Sql update
SQL
0 vote265 views2 answers
MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found
SQL
0 vote265 views1 answer
Mysql replication change database name
SQL
0 vote288 views1 answer
How to find table lock and row lock in mysql
SQL
1 vote288 views1 answer
Create new index takes long time postgresql
SQL
0 vote278 views1 answer
Subquery aggregation
SQL
1 vote261 views2 answers
Sql where is not number
SQL
0 vote289 views1 answer
Sql pivot table
SQL
1 vote287 views1 answer
How to select the nth row in a SQL database table?
SQL
0 vote321 views1 answer
SQL SERVER Add Identity Column to existing table
SQL