Scribd Logo
Upload

Welcome to Scribd!

  • 25 views

    Add Datafile TEMP

    Uploaded by

    jotav jotav
    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.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Add Datafile TEMP

    Uploaded by

    jotav jotav
    25 views2 pages
    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.

    Original Description:

    agregar datafile TMP

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    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.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    25 views2 pages

    Add Datafile TEMP

    Uploaded by

    jotav jotav
    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.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 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