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 vote153 views1 answer
Java spring boot connect to sql server
SQL
0 vote147 views3 answers
Sql server import json
SQL
12 votes128 views4 answers
SQL Where
SQL
1 vote181 views1 answer
Psql client write to bash variable
SQL
0 vote140 views1 answer
Mod function example
SQL
0 vote122 views1 answer
Database springboot
SQL
0 vote161 views1 answer
Convert LDAP Epoch to Date
SQL
0 vote150 views1 answer
Mysql 5.6 hierarchical recursive query
SQL
5 votes131 views2 answers
Oracle timestamp to date
SQL
0 vote152 views1 answer
Postgresql print variable
SQL