sintaxes:
http://ss64.com/ora/tablespace_c.html
http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7003.htm#SQLRF01403
Sintaxe criação
create TABLESPACE nome_tablespace
DATAFILE 'CAMINHO_DATAFILE\NOME_DATAFILE_1.DBF' size 1G (m|g|t) --EXEMPLO 'C:\ORADATA\GLTABS\_01.DBF'
'CAMINHO_DATAFILE\NOME_DATAFILE_2.DBF' size 1G
extent management local uniform size 5120k;
----------------------
COM FLASHBACK
----------------------
CREATE TABLESPACE APPL_DATA
DATAFILE ‘/disk3/oradata/DB01/appl_data01.dbf’
SIZE 100M
DEFAULT STORAGE COMPRESS
BLOCKSIZE 16K
LOGGING
ONLINE
FORCE LOGGING
FLASHBACK ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE ts_myapp DATAFILE
'/data/ts_myapp01.dbf' SIZE 200M,
'/data/ts_myapp02.dbf' SIZE 500M
logging
autoextend off
extent management local;
----------------------
UNDO TABLESPACE
----------------------
-> CREATE UNDO TABLESPACE nome_tablespace_undo01
datafile '/data/ts_undo01.dbf' SIZE 50000M REUSE
autoextend on
RETENTION NOGUARANTEE | RETENTION GUARANTEE;
-> CREATE UNDO TABLESPACE ts_undo01 DATAFILE
'/data/ts_undo01.dbf' SIZE 50000M REUSE
autoextend on
RETENTION NOGUARANTEE;
------------------------------------------------------------
CRIANDO Bigfile and Smallfile Tablespaces
CREATE BIGFILE TABLESPACE PO_ARCHIVE
DATAFILE ‘/u02/oradata/11GR11/po_archive.dbf’ size 25G;
CREATE SMALLFILE TABLESPACE PO_DETAILS
DATAFILE ‘/u02/oradata/11GR11/po_details.dbf’ size 2G;
----------------------------------------------------------------------------------
TRABALHANDO COM OMF
ALTER SYSTEM SET
db_create_file_dest = ‘/u02/oradata/’ SCOPE=BOTH;
When creating a tablespace using the OMF feature, you simply omit the filename:
CREATE TABLESPACE hr_data;
exemplo:
alter system set DB_CREATE_FILE_DEST ='/home/db11g/oradata';
ou alterar nos parametros:
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_1
DB_CREATE_ONLINE_LOG_DEST_2
DB_CREATE_ONLINE_LOG_DEST_3
DB_CREATE_ONLINE_LOG_DEST_4
DB_CREATE_ONLINE_LOG_DEST_5
DB_RECOVERY_FILE_DEST
********************************************************************************************
ALTERAÇÃO - Sequencia
1 -INVESTIGAR O CAMINHO - Usar as views
todas - V$TABLESPACE
views - temp -> v$tempfile e dba_temp_files
datafiles ->v$datafile e dba_data_files , DBA_SEGMENTS
DBA_DATA_FILES
DBA_TABLESPACES
USER_TABLESPACES
DBA_TEMP_FILES
DBA_TS_QUOTAS
USER_TS_QUOTAS
Steps
1 - > criar a tablespace
create smallfile tablespace newts
datafile 'C:\oracle\app\oradata\orcl\newsts01.dbf'
size 100m autoextend on next 10m maxsize 200m
logging
extent management local
segment space management auto
default nocompress;
2 - -> renomear a tablespace
ALTER TABLESPACE tablespaceOLDname RENAME TO tablespaceNEWname;
ALTER TABLESPACE newts RENAME TO newts02;
3 - -> colocar a nova tablespace offline
ALTER TABLESPACE newts02 OFFLINE;
4 - -> renomear os datafiles no SO
host rename CAMINHO_OLD_DATAFILE\NOME_OLD_DATAFILE_.DBF NOME_NEW_DATAFILE.DBF
host rename C:\oracle\app\oradata\orcl\newsts01.dbf newsts02.dbf;
5 - -> ALTERAR no banco o antigo datafile
ALTER DATABASE rename file 'C:\oracle\app\oradata\orcl\newsts01.dbf'
TO 'C:\oracle\app\oradata\orcl\newsts02.dbf';
6 - -> Colocar a tablespace online
ALTER TABLESPACE tablespaceNEWname ONLINE;
ALTER TABLESPACE newts02 ONLINE;
7 - Checando
obs: @dba_tablespaces
ou usar a query
select substr(a.tablespace_name,1,20) "Tablespaces",
(b.BYTES/1048576) as "TotalMB",
(b.BYTES/1048576)-(c.BYTES/1048576) as "UsedMB",
(c.BYTES/1048576) as "FreeMB"
from dba_tablespaces a,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_data_files
group by tablespace_name ) b,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_free_space
group by tablespace_name) c
where
a.tablespace_name = b.tablespace_name(+)
and b.tablespace_name = c.tablespace_name(+)
order by a.tablespace_name;
Dropando
drop tablespace newts02 including contents and datafiles;
****************************************************************************
MARCAR COMO LEITURA- GRAVAÇÃO OU SOMENTE LEITURA
para somente LEITURA -> ALTER TABLESPACE NOME_TABLESPACE read only
para ler e gravar -> ALTER TABLESPACE NOME_TABLESPACE read write
PARA REDIMENSIONAR O DATAFILE
sintaxe - ALTER DATABASE TIPO_DO_DATAFILE NOME_DO_DATAFILE RESIZE M|g|t
-> ALTER DATABASE DATAFILE 'C:\oracle\app\oradata\orcl\newsts02.dbf' resize 10m;
ALTER DATABASE DATAFILE 'C:\oracle\app\oradata\orcl\newsts02.dbf' resize 10m;
autoextend on next 100m maxsize 4G ; ---- Não esquecer de botar o limite máximo, neste exemplo é 4G
---------------------------------------------------------------------------------
PARA ADICIONAR DATAFILE
ALTER TABLESPACE NOME_TABLESPACE
add datafile 'caminho_e_nome.dbf' size M|g|t ;
exemplo ->
ALTER TABLESPACE gl_large_tabs
add datafile 'C:\oracle\app\oradata\orcl\newsts02.dbf' size 2g ;
no Linux
alter tablespace nome_tablespace add datafile
'/dev/vx/rdsk/oracledg/db_name_data02.dbf' size 6001m
---------------------------------------------------------------------------------
GERENCIANDO ESPAÇOS
OBS: PARA AUMENTAR TABLESPACE - SE ADICIONA DATAFILES - ADD DATAFILE
exemplo ->
ALTER TABLESPACE gl_large_tabs
add datafile 'C:\oracle\app\oradata\orcl\newsts02.dbf' size 2g ;
PARA AUMENTAR SEGMENTOS ( DENTRO DA TABLESPACE ) - SE ALOCA EXTENSÕES - allocate extent
-- > alter table newtabs allocate extent;
E DENTRO DOS SEGMENTOS, são aumentadas as linhas
-----------------------------------------------------------------------
CONVERTENDO GERENCIAMENTO POR DICIONÁRIO PARA LOCAL (O IDEAL):
OBS: para criar uma com gerenciamento manual (não e´o ideal - pag 275)
-> create tablespace NOME_TABLESPACE SEGMENT SPACE MANAGEMENT LOCAL
1 - verificar pelas querys se há tablespace com gerenciamento local:
select tablespace_name, extent management from dba_tablespaces;
select tablespace_name, segment_space_management from dba_tablespaces;
Checar pelo nome da tablespace:
-> select segment_space_management from dba_tablespaces where tablespace_name='NOME_TABLESPACE'
2 - para converter para auto:
execute dbms_space_admin.tablespace_migrate_to local('tablespace_name')
Para alterar o TRESHOLD NO EM: tablespace / edit tablespace /TRESHOLD
**********************************************************************************************
PARA MOVER SEGMENTOS:
ALTER tipo_de segmento nome_do_segmento MOVE TABLESPACE nome_TABLESPACE_ANTIGA
tipo_de segmento nome_do_segmento MOVE nome_TABLESPACE_NOVA;
->
ALTER table mantab MOVE TABLESPACE autosalter table mantab MOVE TABLESPACE autosegs;
Se tiver indices, fazer o rebuild
alter index mantabi rebuild ONLINE TABLESPACE autosegs;s;
CONFIRMAR SE ESTÃO NA TABLESPACE correta
-> select tablespace_name from DBA_SEGMENTS WHERE SEGMENT_NAME LIKE 'MANTAB%'
-----------------------------------------------------------------------------------------------------------
Querys importantes:
Aumento de Tablespace
select SUM(bytes/1024/1024/1024) from dba_free_space where tablespace_name = 'APPS_TS_TX_DATA';
---> verificar tamanho de uma tablespace
select file_name, bytes/1024/1024/1024 from dba_data_files where tablespace_name = 'USERS' order by file_name;
---> verificar qual o ultimo dbf para adicionar
Informações das tablespaces - Oracle
select substr(a.tablespace_name,1,20) "Tablespaces",
(b.BYTES/1048576) as "TotalMB",
(b.BYTES/1048576)-(c.BYTES/1048576) as "UsedMB",
(c.BYTES/1048576) as "FreeMB"
from dba_tablespaces a,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_data_files
group by tablespace_name ) b,
(select tablespace_name,sum(bytes) as "BYTES"
from dba_free_space
group by tablespace_name) c
where
a.tablespace_name = b.tablespace_name(+)
and b.tablespace_name = c.tablespace_name(+)
order by a.tablespace_name;
Ver tablespaces com menos de 20% livre
COL tbs FORMAT a25
COL total(mb) FORMAT 999,990.00
COL livre(mb) FORMAT 999,990.00
COL livre(%) FORMAT 990.00
select instance_name,host_name,to_char(sysdate,'dd/mm/yy hh24:mi') from v$instance;
SELECT DISTINCT d.tablespace_name "NOME DA TABLESPACE",t.total "TOTAL(MB)", NVL(f.livre,0) "LIVRE(MB)", (NVL(f.livre,0)*100/t.total) "LIVRE(%)"
FROM dba_data_files d,
(SELECT tablespace_name ,sum(bytes)/1024/1024 total FROM dba_data_files GROUP BY tablespace_name) t,
(SELECT tablespace_name ,sum(bytes)/1024/1024 livre FROM dba_free_space GROUP BY tablespace_name) f
WHERE d.tablespace_name =t.tablespace_name
AND d.tablespace_name = f.tablespace_name(+)
AND (NVL(f.livre,0)*100/t.total) <= 20
ORDER BY 4 DESC;
Listar os datafiles das tablespaces
COL tablespace_name FORMAT a15
COL file_name FORMAT a45
COL size_mb FORMAT 999,990.00
COL autoextensible FORMAT a5
select df.tablespace_name, df.file_name, df.bytes/1024/1024 size_mb, df.status, df.autoextensible
from dba_data_files df
where df.tablespace_name='&tablespace_name';
Adicionar espaço na tablespace
Resize: alter database datafile '/oracle/oradata/nome_datafile.dbf' resize 8192 m
Create: alter tablespace nome_tablespace add datafile '/dev/vx/rdsk/oracledg/db_name_data02.dbf' size 6001m
Script para adição de um novo data file em dada tablespace.
SQL> alter tablespace nome_tablespace
add datafile '/oracle/SID/sapdata2/btabi_10/btabi.data10' size 1024064K
autoextend on
next 1024064K
maxsize 16777280K;
#tablespace de undo#
CREATE UNDO TABLESPACE UNDOTBS DATAFILE
'c:\oracle9i\treino9i\UNDOTBS.dbf' SIZE 350M
#Criar tablespace temp#
CREATE TEMPORARY TABLESPACE teste DATAFILE
'/proj/ebsdsgev/oradata/teste.dbf' SIZE 1024M
DEFAULT STORAGE (INITIAL 1024 K NEXT 1024 K
MAXEXTENTS unlimited PCTINCREASE 0);
#Criar tablespace TMP#
CREATE TEMPORARY TABLESPACE TEMP3
TEMPFILE '/proj/ebsdsgev/oradata/temp03.dbf'
SIZE 1024M AUTOEXTEND ON;
#apagar tablespace#
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
#Ver qual tablespace esta como temp#
select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TABLESPACE%';
Obrigado ao meu amigo Bruno Pelegrini pelas dicas
Nenhum comentário:
Postar um comentário