Advertisement
thesuhu

Oracle Tablespace

Dec 27th, 2020 (edited)
1,597
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # CREATE a NEW TABLESPACE named tbs1 WITH SIZE 1MB
  2. # DATAFILE bisa ditambah PATH pada FILE dbf: /opt/oracle/product/12.2.0.1/dbhome_1/dbs
  3. # bisa diganti /opt/oracle/oradata/ORCLCDB/ORCLPDB1/
  4. CREATE TABLESPACE tbs1
  5.    DATAFILE 'tbs1_data.dbf'
  6.    SIZE 1m;
  7.  
  8. # find its information BY querying data FROM the dba_data_files VIEW
  9. SELECT
  10.    tablespace_name,
  11.    file_name,
  12.    bytes / 1024/ 1024  MB
  13. FROM
  14.    dba_data_files;
  15.  
  16. # resize the TABLESPACE
  17. ALTER DATABASE
  18.    DATAFILE 'tbs1.dbf'
  19.    RESIZE 10m;
  20.  
  21. # ADD datafile IN TABLESPACE
  22. ALTER TABLESPACE
  23.    users
  24. ADD DATAFILE
  25.    '/u01/app/oracle/oradata/EE/tbs1.dbf'
  26. SIZE 100m
  27.  
  28. # The SECOND way TO avoid this issue, WHEN creating a NEW TABLESPACE
  29. CREATE TABLESPACE tbs1
  30.    DATAFILE 'tbs1.dbf'
  31.    SIZE 1m
  32.    AUTOEXTEND 20m;
  33.  
  34. # hapus TABLESPACE kosongan
  35. DROP TABLESPACE tbs1;
  36.  
  37. # hapus tabelspace yang ada isi
  38. DROP TABLESPACE tbs2
  39.     INCLUDING CONTENTS;
  40. # DROP sekaligus datafilenya
  41. DROP TABLESPACE tbs2 including contents AND datafiles;
  42. # jika udah hapus TABLESPACE lupa hapus datafile, bisa dengan cara CREATE TABLE SPACE datafile existing, kemudian DROP sekaligus datafilenya
  43.  
  44. # lihat free SIZE TABLESPACE
  45. SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024/1024 "FREE SPACE(GB)"
  46. FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
  47.  
  48. # alternatif lihat free SIZE TABLESPACE
  49. --Size of All Table Space
  50.  
  51. --1. Used Space
  52. SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "USED SPACE(IN GB)" FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME;
  53. --2. Free Space
  54. SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS "FREE SPACE(IN GB)" FROM   USER_FREE_SPACE GROUP BY TABLESPACE_NAME;
  55.  
  56. --3. Both Free & Used
  57. SELECT USED.TABLESPACE_NAME, USED.USED_BYTES AS "USED SPACE(IN GB)",  FREE.FREE_BYTES AS "FREE SPACE(IN GB)"
  58. FROM
  59. (SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS USED_BYTES FROM USER_SEGMENTS GROUP BY TABLESPACE_NAME) USED
  60. INNER JOIN
  61. (SELECT TABLESPACE_NAME,TO_CHAR(SUM(NVL(BYTES,0))/1024/1024/1024, '99,999,990.99') AS FREE_BYTES FROM  USER_FREE_SPACE GROUP BY TABLESPACE_NAME) FREE
  62. ON (USED.TABLESPACE_NAME = FREE.TABLESPACE_NAME);
  63.  
  64. # lihat datafile dalam tabelspace
  65.   SELECT SUBSTR (df.tablespace_name, 1, 20)
  66.              "Tablespace Name",
  67.          SUBSTR (df.file_name, 1, 80)
  68.              "File Name",
  69.          ROUND (df.bytes / 1024 / 1024, 0)
  70.              "Size (M)",
  71.          DECODE (e.used_bytes, NULL, 0, ROUND (e.used_bytes / 1024 / 1024, 0))
  72.              "Used (M)",
  73.          DECODE (f.free_bytes, NULL, 0, ROUND (f.free_bytes / 1024 / 1024, 0))
  74.              "Free (M)",
  75.          DECODE (e.used_bytes,
  76.                  NULL, 0,
  77.                  ROUND ((e.used_bytes / df.bytes) * 100, 0))
  78.              "% Used"
  79.     FROM dba_data_files df,
  80.          (  SELECT file_id, SUM (bytes) used_bytes
  81.               FROM dba_extents
  82.           GROUP BY file_id) E,
  83.          (  SELECT MAX (bytes) free_bytes, file_id
  84.               FROM dba_free_space
  85.           GROUP BY file_id) f
  86.    WHERE e.file_id(+) = df.file_id AND df.file_id = f.file_id(+)
  87. ORDER BY df.tablespace_name, df.file_name;
  88.  
  89.  
Advertisement
RAW Paste Data Copied
Advertisement