[go: up one dir, main page]
Scribd
Upload
45 views2 pages

Add Datafile TEMP

The document provides SQL commands to: 1) Check available space in ASM disk groups and verify autoextend and locations of datafiles. 2) List tablespaces with free percentage by querying V$ views and DBA views. 3) Create a temporary tablespace and add tempfiles to the TEMP tablespace with autoextend settings. 4) Change the size of a tempfile and drop a tempfile from the TEMP tablespace.

Uploaded by

jotav jotav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
45 views2 pages

Add Datafile TEMP

The document provides SQL commands to: 1) Check available space in ASM disk groups and verify autoextend and locations of datafiles. 2) List tablespaces with free percentage by querying V$ views and DBA views. 3) Create a temporary tablespace and add tempfiles to the TEMP tablespace with autoextend settings. 4) Change the size of a tempfile and drop a tempfile from the TEMP tablespace.

Uploaded by

jotav jotav
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

------------------ Verificar espacio en ASM -------------------------------------------

select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;


o
select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB, (round((nvl(FREE_MB, 0) /
TOTAL_MB) * 100,2)-100)*-1 "Use_%" from V$ASM_DISKGROUP;

------------------ Verificar autoextend y ubicación de datafiles -------------------------------------------


SET LINESIZE 1200
COL NAME FOR A60
SELECT FILE# AS FILE_NUMBER
,NAME AS NAME
,CREATION_TIME AS CREATION_TIME
,BLOCK_SIZE AS BLOCK_SIZE
,BYTES/1024/1024/1024 AS "FILE_SIZE(G)"
,CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE(G)"
,STATUS AS STATUS
,ENABLED AS ENABLED
FROM V$TEMPFILE;

COL FILE_NAME FOR A50;


SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME, AUTOEXTENSIBLE,
BYTES/1024/1024/1024 AS "FILE_SIZE(G)",
BLOCKS,
USER_BYTES/1024/1024/1024 AS "FILE_SIZE_USER(G)",
USER_BLOCKS,
BLOCKS -USER_BLOCKS AS SYSTEM_USED
FROM DBA_TEMP_FILES;

------------------ Verificar ubicación de LogFile -------------------------------------------


col MEMBER for A80
select * from V$LOGFILE;

------------------ Listar tablespaces con % libre -------------------------------------


set line 2000
set feedback off;
select h.tablespace_name,
ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576, 2) TOTAL_SPACE_MB,
ROUND (SUM ((h.bytes_free + h.bytes_used) - NVL (h.bytes_used, 0)) / 1048576,2) FREE_SPACE_MB,
ROUND (SUM (NVL (h.bytes_used, 0)) / 1048576, 2) megs_used,
ROUND ( (SUM ( (h.bytes_free + h.bytes_used) - NVL (h.bytes_used, 0))/ SUM (h.bytes_free +
h.bytes_used)) * 100,2) pct_free,
100 - ROUND ( (SUM ( (h.bytes_free + h.bytes_used) - NVL (h.bytes_used, 0))/ SUM (h.bytes_free +
h.bytes_used)) * 100,2) pct_used,
ROUND (f.maxbytes / 1048576,2) MAX
FROM SYS.V_$TEMP_SPACE_HEADER h, sys.v_$temp_extent_pool p, dba_temp_files f
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name (+) = h.tablespace_name
AND f.file_id = h.file_id
AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name, f.maxbytes
order by 5 desc;

------------------Crear datafile en tablespace ------------------------


En ASM el nombre lo asigna automaticamente

CREATE TEMPORARY TABLESPACE TMP TEMPFILE '/u01/gsp/oradata/TMP01.dbf' SIZE 8G


AUTOEXTEND OFF;

ALTER TABLESPACE TEMP ADD TEMPFILE '+TEMP' SIZE 1G AUTOEXTEND ON NEXT 128M MAXSIZE 10G;
ALTER TABLESPACE TEMP ADD TEMPFILE '+TEMP' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;

------------------Cambiar tamaño tempfile ------------------------


ALTER DATABASE TEMPFILE '+TEMP/SDAD/TEMPFILE/temp.349.1098723457' RESIZE 10G;

------------------Borrar tempfile ------------------------


ALTER TABLESPACE TEMP drop tempfile '+TEMP/SDAD/TEMPFILE/temp.349.1098728119';

You might also like