{"id":1214,"date":"2016-01-03T10:41:34","date_gmt":"2016-01-03T10:41:34","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1214"},"modified":"2016-01-03T10:41:34","modified_gmt":"2016-01-03T10:41:34","slug":"db-12c-tablespace-%ea%b4%80%eb%a6%ac-alter-database-%ea%b5%ac%eb%ac%b8-%ea%b4%80%eb%a0%a8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1214","title":{"rendered":"DB 12c \u2013 Tablespace \uad00\ub9ac, Alter Database \uad6c\ubb38 \uad00\ub828"},"content":{"rendered":"

\ubc30\uacbd : \ud604\uc7ac 12c \uc124\uce58\ub418\uc5b4 \uc788\uace0 PDB 2\uac1c \uc124\uce58 \ub418\uc5b4 \uc788\ub294 \uc0c1\ud0dc \uc785\ub2c8\ub2e4.
\n<\/span><\/p>\n

\u00a0\u00a0
\n\u00a0<\/p>\n

# Tablespace \uad00\ub9ac<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

    \n
  1. MANAGING TABLESPACES IN A CDB
    \n<\/strong><\/span><\/li>\n<\/ol>\n
      \n
    • Tablespace\ub294<\/span>
      \n\t\t\t\ub2e8<\/span>
      \n\t\t\t\ud558\ub098\uc758<\/span>
      \n\t\t\t\ucee8\ud14c\uc774\ub108<\/span>(Root or PDB)\uc5d0\ub9cc<\/span>
      \n\t\t\t\uc18d\ud55c\ub2e4<\/span>. CDB\uc758<\/span>
      \n\t\t\t\uac01<\/span>
      \n\t\t\t\ucee8\ud14c\uc774\ub108\ub294<\/span>
      \n\t\t\t\uc790\uc2e0\uc758<\/span>
      \n\t\t\t\uae30\ubcf8<\/span> Tablespace\ub97c<\/span>
      \n\t\t\t\uac00\uc9c4\ub2e4<\/span>.\n<\/li>\n
    • \n
      \uae30\ubcf8<\/span> Tablespace\ub294<\/span>
      \n\t\t\t\t\ucee8\ud14c\uc774\ub108<\/span>
      \n\t\t\t\t\uac04\uc5d0<\/span>
      \n\t\t\t\t\uacf5\uc720<\/span>
      \n\t\t\t\t\ub420<\/span>
      \n\t\t\t\t\uc218<\/span>
      \n\t\t\t\t\uc5c6\ub2e4<\/span>.\n<\/div>\n

      \u00a0
      \n\u00a0<\/p>\n

      SQL> col file_name for a40\n<\/p>\n

      SQL> SELECT TABLESPACE_NAME, CON_ID\n<\/p>\n

      FROM CDB_TABLESPACES\n<\/p>\n

      ORDER BY CON_ID;\n<\/p>\n

      SELECT FILE_NAME, CON_ID FROM CDB_DATA_FILES;\n<\/p>\n

      TABLESPACE_NAME CON_ID\n<\/p>\n

      ———————————————————— ———-\n<\/p>\n

      SYSTEM 1\n<\/p>\n

      USERS 1\n<\/p>\n

      SYSAUX 1\n<\/p>\n

      UNDOTBS1 1\n<\/p>\n

      TEMP 1\n<\/p>\n

      SYSTEM 2\n<\/p>\n

      TEMP 2\n<\/p>\n

      SYSAUX 2\n<\/p>\n

      SYSTEM 3\n<\/p>\n

      TEMP 3\n<\/p>\n

      SYSAUX 3\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      TABLESPACE_NAME CON_ID\n<\/p>\n

      ———————————————————— ———-\n<\/p>\n

      SYSTEM 4\n<\/p>\n

      TEMP 4\n<\/p>\n

      SYSAUX 4\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      14 rows selected.\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      SQL> CREATE TABLESPACE CDB1_USERS datafile ‘\/oradata\/cdb1\/cdb1_users01.dbf’ size 100m;\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      Tablespace created.\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      SQL> SELECT TABLESPACE_NAME, CON_ID\n<\/p>\n

      FROM CDB_TABLESPACES\n<\/p>\n

      WHERE TABLESPACE_NAME = ‘CDB1_USERS’\n<\/p>\n

      ORDER BY CON_ID;\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      TABLESPACE_NAME CON_ID\n<\/p>\n

      ———————————————————— ———-\n<\/p>\n

      CDB1_USERS 1\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      SQL> SELECT FILE_NAME, CON_ID FROM CDB_DATA_FILES\n<\/p>\n

      WHERE TABLESPACE_NAME = ‘CDB1_USERS’;\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      FILE_NAME CON_ID\n<\/p>\n

      —————————————- ———-\n<\/p>\n

      \/oradata\/cdb1\/cdb1_users01.dbf 1\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      SQL> CONNECT SYS\/oracle@PDB11 AS SYSDBA;\n<\/p>\n

      Connected.\n<\/p>\n

      SQL> CREATE TABLESPACE PDB11_USERS datafile ‘\/oradata\/cdb1\/PDB11\/pdb11_users01.dbf’ size 100m;\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      Tablespace created.\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      SQL> SELECT TABLESPACE_NAME, CON_ID\n<\/p>\n

      FROM CDB_TABLESPACES\n<\/p>\n

      WHERE TABLESPACE_NAME = ‘PDB11_USERS’\n<\/p>\n

      ORDER BY CON_ID;\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      TABLESPACE_NAME CON_ID\n<\/p>\n

      ———————————————————— ———-\n<\/p>\n

      PDB11_USERS 3\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      SQL> SELECT FILE_NAME, CON_ID FROM CDB_DATA_FILES\n<\/p>\n

      WHERE TABLESPACE_NAME = ‘PDB11_USERS’;\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n

      FILE_NAME CON_ID\n<\/p>\n

      —————————————- ———-\n<\/p>\n

      \/oradata\/cdb1\/PDB11\/pdb11_users01.dbf 3\n<\/p>\n

      \u00a0
      \n\u00a0<\/p>\n<\/li>\n<\/ul>\n

        \n
      1. MANAGING TEMPORARY TABLESPACES
        \n<\/strong><\/span><\/li>\n<\/ol>\n
          \n
        • \n
          CDB \uc804\uccb4\uc5d0<\/span>
          \n\t\t\t\t\ud558\ub098\uc758<\/span> default temporary tablespace\uac00 \uc788\ub2e4. Temp\uc5d0 \uad00\ud574\uc11c\ub294 root \ucee8\ud14c\uc774\ub108\uc5d0\uc11c \uc791\uc5c5\uc774 \ud544\uc694\ud558\uba70<\/span>\n\t\t\t<\/div>\n

          \ud2b9\uc815<\/span>
          \n\t\t\t\t\uc720\uc800\uc5d0\uac8c<\/span>
          \n\t\t\t\t\ud560\ub2f9\ud560<\/span>
          \n\t\t\t\t\uc218<\/span>
          \n\t\t\t\t\uc788\ub2e4<\/span>.\n<\/p>\n<\/li>\n

        • \n
          PDB\ub97c<\/span>
          \n\t\t\t\t\uc704\ud55c<\/span> default temporary tablespace\ub97c<\/span>
          \n\t\t\t\t\ub9cc\ub4e4<\/span>
          \n\t\t\t\t\uc218<\/span>
          \n\t\t\t\t\uc788\ub2e4<\/span>. \uac1c\ubcc4<\/span> PDB\uc5d0<\/span>
          \n\t\t\t\t\ub300\ud574<\/span>
          \n\t\t\t\t\ucd94\uac00\ud560<\/span>
          \n\t\t\t\t\uc218<\/span>
          \n\t\t\t\t\uc788\uc73c\uba70<\/span>, PDB\uc758<\/span>
          \n\t\t\t\t\ud2b9\uc815\uc720\uc800\uc5d0\uac8c\ub3c4<\/span>
          \n\t\t\t\t\ucd94\uac00<\/span>\n\t\t\t<\/div>\n

          \ud560<\/span>
          \n\t\t\t\t\uc218<\/span>
          \n\t\t\t\t\uc788\ub2e4<\/span>. CDB\uc5d0\uc11c<\/span> PDB\ub97c<\/span> Unplug \ud560<\/span>
          \n\t\t\t\t\ub54c<\/span> Temp\ub3c4<\/span>
          \n\t\t\t\t\uac19\uc774<\/span> Unplug \ub41c\ub2e4<\/span>.\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          SQL> CREATE TEMPORARY TABLESPACE CDB1_TEMP tempfile ‘\/oradata\/cdb1\/cdb_temp01.dbf’ size 100m;\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          Tablespace created.\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          SQL> SELECT TABLESPACE_NAME, CON_ID\n<\/p>\n

          FROM CDB_TABLESPACES\n<\/p>\n

          WHERE TABLESPACE_NAME = ‘CDB1_TEMP’\n<\/p>\n

          AND CONTENTS = ‘TEMPORARY’;\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          TABLESPACE_NAME CON_ID\n<\/p>\n

          ———————————————————— ———-\n<\/p>\n

          CDB1_TEMP 1\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          \n\u00a0<\/p>\n

          SQL> col file_name for a45\n<\/p>\n

          SQL> SELECT FILE_NAME, CON_ID FROM CDB_TEMP_FILES\n<\/p>\n

          WHERE TABLESPACE_NAME = ‘CDB1_TEMP’;\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          FILE_NAME CON_ID\n<\/p>\n

          ——————————————— ———-\n<\/p>\n

          \/oradata\/cdb1\/cdb_temp01.dbf 1\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          SQL> CONNECT SYS\/oracle@PDB11 AS SYSDBA;\n<\/p>\n

          Connected.\n<\/p>\n

          SQL> CREATE TEMPORARY TABLESPACE PDB11_TEMP tempfile ‘\/oradata\/cdb1\/PDB11\/pdb11_temp01.dbf’ size 100m;\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          Tablespace created.\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          SQL> SELECT TABLESPACE_NAME, CON_ID\n<\/p>\n

          FROM CDB_TABLESPACES\n<\/p>\n

          WHERE TABLESPACE_NAME = ‘PDB11_TEMP’\n<\/p>\n

          ORDER BY CON_ID;\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          TABLESPACE_NAME CON_ID\n<\/p>\n

          ———————————————————— ———-\n<\/p>\n

          PDB11_TEMP 3\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          SQL> SELECT FILE_NAME, CON_ID FROM CDB_TEMP_FILES\n<\/p>\n

          WHERE TABLESPACE_NAME = ‘PDB11_TEMP’;\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n

          FILE_NAME CON_ID\n<\/p>\n

          ——————————————— ———-\n<\/p>\n

          \/oradata\/cdb1\/PDB11\/pdb11_temp01.dbf 3\n<\/p>\n

          \u00a0
          \n\u00a0<\/p>\n<\/li>\n<\/ul>\n

          # Alter Database \uad6c\ubb38<\/span>
          \n\t\t\t\t\uad00\ub828<\/span>
          \n\t\t\t<\/strong><\/span><\/p>\n

            \n
          1. USING ALTER DATABASE STATEMENT IN A CDB
            \n<\/strong><\/span><\/li>\n<\/ol>\n
              \n
            • \n
              \ud604\uc7ac<\/span>
              \n\t\t\t\t\uc811\uc18d\ud55c<\/span>
              \n\t\t\t\t\ucee8\ud14c\uc774\ub108\uac00<\/span> Root\ub77c\uba74<\/span> alter database \uad6c\ubb38\uc740<\/span> CDB, non-CDB\uc5d0\uc11c<\/span>
              \n\t\t\t\t\ub611\uac19\uc774<\/span>
              \n\t\t\t\t\ub3d9\uc791\ud55c\ub2e4<\/span>. \ub300\ubd80\ubd84\uc758<\/span> alter database \uba85\ub839\uc5b4\ub294<\/span> CDB \uc804\uccb4\uc5d0<\/span>\n\t\t\t<\/div>\n

              \uc601\ud5a5\uc744<\/span>
              \n\t\t\t\t\uc900\ub2e4<\/span>. \uc811\uc18d\ud55c<\/span>
              \n\t\t\t\t\ucee8\ud14c\uc774\ub108\uac00<\/span> PDB\ub77c\uba74<\/span> alter database\uc640<\/span> alter pluggable database \uba85\ub839\uc5b4\ub294<\/span>
              \n\t\t\t\t\ud604\uc7ac<\/span> PDB\ub9cc\uc744<\/span>
              \n\t\t\t\t\ubcc0\uacbd\ud55c\ub2e4<\/span>.\n<\/p>\n

              SQL> CONNECT SYS\/oracle@CDB1 AS SYSDBA;\n<\/p>\n

              Connected.\n<\/p>\n

              SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE;\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              GROUP#\n<\/p>\n

              ———-\n<\/p>\n

              MEMBER\n<\/p>\n

              ——————————————————————————–\n<\/p>\n

              1\n<\/p>\n

              \/oradata\/cdb1\/redo01.log\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              2\n<\/p>\n

              \/oradata\/cdb1\/redo02.log\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              3\n<\/p>\n

              \/oradata\/cdb1\/redo03.log\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              SQL> ALTER DATABASE CDB1 ADD LOGFILE GROUP 4 ‘\/oradata\/cdb1\/redo04.log’ size 50m;\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              Database altered.\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              SQL> SELECT GROUP#, MEMBER FROM V$LOGFILE;\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              GROUP#\n<\/p>\n

              ———-\n<\/p>\n

              MEMBER\n<\/p>\n

              ——————————————————————————–\n<\/p>\n

              1\n<\/p>\n

              \/oradata\/cdb1\/redo01.log\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              2\n<\/p>\n

              \/oradata\/cdb1\/redo02.log\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              3\n<\/p>\n

              \/oradata\/cdb1\/redo03.log\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              GROUP#\n<\/p>\n

              ———-\n<\/p>\n

              MEMBER\n<\/p>\n

              ——————————————————————————–\n<\/p>\n

              4\n<\/p>\n

              \/oradata\/cdb1\/redo04.log\n<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n

              \u00a0
              \n\u00a0<\/p>\n<\/li>\n<\/ul>\n

                \n
              1. \n
                ALTER DATABASE TO MODIFY ONLY THE ROOT
                \n<\/strong><\/span><\/div>\n

                \ud604\uc7ac<\/span>
                \n\t\t\t\t\uc811\uc18d\ud55c<\/span> container \uac00<\/span> Root \ub77c\uba74<\/span> ALTER DATABASE \uba85\ub839\uc5b4\ub294<\/span> PDBs \uc758 \ubcc0\uacbd \uc5c6\uc774<\/span> root \ub97c \ubcc0\uacbd\ud560<\/span>\n\t\t\t<\/p>\n

                \uc218<\/span>
                \n\t\t\t\t\uc788\ub2e4<\/span>. \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4\uac00 \uba85\uc2dc\uc801\uc73c\ub85c \uc9c0\uc815\ub418\uc9c0 \uc54a\uc740 \ud604\uc7ac \uc811\uc18d\ud55c<\/span> container \uac00<\/span> Root \uc778 \uc720\uc800\ub294<\/span>\n\t\t\t<\/p>\n

                root \uc758<\/span>
                \n\t\t\t\t\uae30\ubcf8 \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4\ub97c \uc0ac\uc6a9\ud55c\ub2e4<\/span>. ALTER DATABASE \ubb38\uc5d0 \uc9c0\uc815\ub41c \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4\ub294<\/span> root \uc5d0<\/span>\n\t\t\t<\/p>\n

                \uc874\uc7ac\ud574\uc57c<\/span>
                \n\t\t\t\t\ud55c\ub2e4<\/span>.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> CONNECT SYS\/oracle@CDB1 AS SYSDBA;\n<\/p>\n

                Connected.\n<\/p>\n

                SQL> COLUMN PROPERTY_NAME FORMAT A30;\n<\/p>\n

                SQL> COLUMN PROPERTY_VALUE FORMAT A30;\n<\/p>\n

                SQL> SELECT CON_ID, PROPERTY_NAME, PROPERTY_VALUE\n<\/p>\n

                FROM CDB_PROPERTIES\n<\/p>\n

                WHERE PROPERTY_NAME LIKE ‘%TABLESPACE’ order by con_id;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                CON_ID PROPERTY_NAME PROPERTY_VALUE\n<\/p>\n

                ———- —————————— ——————————\n<\/p>\n

                1 DEFAULT_PERMANENT_TABLESPACE USERS\n<\/p>\n

                1 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                2 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                2 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                3 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                3 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                4 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                4 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                8 rows selected.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> ALTER DATABASE DEFAULT TABLESPACE CDB1_USERS;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                Database altered.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE CDB1_TEMP;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                Database altered.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> COLUMN PROPERTY_NAME FORMAT A30;\n<\/p>\n

                SQL> COLUMN PROPERTY_VALUE FORMAT A30;\n<\/p>\n

                SQL> SELECT CON_ID, PROPERTY_NAME, PROPERTY_VALUE\n<\/p>\n

                FROM CDB_PROPERTIES\n<\/p>\n

                WHERE PROPERTY_NAME LIKE ‘%TABLESPACE’ order by con_id;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                CON_ID PROPERTY_NAME PROPERTY_VALUE\n<\/p>\n

                ———- —————————— ——————————\n<\/p>\n

                1 DEFAULT_PERMANENT_TABLESPACE CDB1_USERS\n<\/p>\n

                1 DEFAULT_TEMP_TABLESPACE CDB1_TEMP\n<\/p>\n

                2 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                2 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                3 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                3 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                4 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                4 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                8 rows selected.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n<\/li>\n

              2. \n
                ALTER DATABASE TO MODIFY THE PDB.
                \n<\/strong><\/span><\/div>\n

                ALTER DATABASE \uad6c\ubb38\uc5d0<\/span>
                \n\t\t\t\t\uc9c0\uc815\ub41c<\/span> tablespace \ub610\ub294<\/span> tablespace group \uc740<\/span> PDB \uc5d0 \uc874\uc7ac\ud574\uc57c \ud55c\ub2e4<\/span>.\n<\/p>\n

                \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4\uac00<\/span>
                \n\t\t\t\t\uba85\uc2dc\uc801\uc73c\ub85c \uc9c0\uc815\ub418\uc9c0 \uc54a\uc740 \ud604\uc7ac \uc811\uc18d\ud55c<\/span> container \uac00<\/span> PDB \uc778 \uc720\uc800\ub294<\/span> PDB \uc758 \uae30\ubcf8<\/span>\n\t\t\t<\/p>\n

                \ud14c\uc774\ube14\uc2a4\ud398\uc774\uc2a4\ub97c<\/span>
                \n\t\t\t\t\uc0ac\uc6a9\ud55c\ub2e4<\/span>.\n<\/p>\n

                ALTER DATABASE \uc640<\/span> ALTER PLUGGABLE DATABASE \uba85\ub839\uc5b4\ub294<\/span> PDB \uc5d0\uc11c \ub3d9\uc77c\ud558\uac8c \ub3d9\uc791\ud55c\ub2e4<\/span>\n\t\t\t<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> CONNECT SYS\/oracle@PDB11 AS SYSDBA;\n<\/p>\n

                Connected.\n<\/p>\n

                SQL> ALTER DATABASE DEFAULT TABLESPACE PDB11_USERS;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                Database altered.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PDB11_TEMP;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                Database altered.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> COLUMN PROPERTY_NAME FORMAT A30;\n<\/p>\n

                SQL> COLUMN PROPERTY_VALUE FORMAT A30;\n<\/p>\n

                SQL> SELECT CON_ID, PROPERTY_NAME, PROPERTY_VALUE\n<\/p>\n

                FROM CDB_PROPERTIES\n<\/p>\n

                WHERE PROPERTY_NAME LIKE ‘%TABLESPACE’ order by con_id;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                CON_ID PROPERTY_NAME PROPERTY_VALUE\n<\/p>\n

                ———- —————————— ——————————\n<\/p>\n

                3 DEFAULT_TEMP_TABLESPACE PDB11_TEMP\n<\/p>\n

                3 DEFAULT_PERMANENT_TABLESPACE PDB11_USERS\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                SQL> CONNECT SYS\/oracle@CDB1 AS SYSDBA;\n<\/p>\n

                Connected.\n<\/p>\n

                SQL> COLUMN PROPERTY_NAME FORMAT A30;\n<\/p>\n

                SQL> COLUMN PROPERTY_VALUE FORMAT A30;\n<\/p>\n

                SQL> SELECT CON_ID, PROPERTY_NAME, PROPERTY_VALUE\n<\/p>\n

                FROM CDB_PROPERTIES\n<\/p>\n

                WHERE PROPERTY_NAME LIKE ‘%TABLESPACE’ order by con_id;\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                CON_ID PROPERTY_NAME PROPERTY_VALUE\n<\/p>\n

                ———- —————————— ——————————\n<\/p>\n

                1 DEFAULT_PERMANENT_TABLESPACE CDB1_USERS\n<\/p>\n

                1 DEFAULT_TEMP_TABLESPACE CDB1_TEMP\n<\/p>\n

                2 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                2 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                3 DEFAULT_PERMANENT_TABLESPACE PDB11_USERS\n<\/p>\n

                3 DEFAULT_TEMP_TABLESPACE PDB11_TEMP\n<\/p>\n

                4 DEFAULT_PERMANENT_TABLESPACE SYSTEM\n<\/p>\n

                4 DEFAULT_TEMP_TABLESPACE TEMP\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n

                8 rows selected.\n<\/p>\n

                \u00a0
                \n\u00a0<\/p>\n<\/li>\n

              3. USING ALTER SYSTEM STATEMENT IN A CDB
                \n<\/strong><\/span><\/li>\n<\/ol>\n
                  \n
                • \n
                  CDB \ub294<\/span> inheritance model(\uc0c1\uc18d \ubc29\uc2dd<\/span>)\uc744 \uc0ac\uc6a9\ud55c\ub2e4<\/span>.\n<\/div>\n

                  root \uc758<\/span>
                  \n\t\t\t\t\ucd08\uae30\ud654 \ud30c\ub77c\ubbf8\ud130 \uac12\uc744 \uc0c1\uc18d\ud558\uc5ec<\/span> PDBs \uc758 \ucd08\uae30\ud654 \ud30c\ub77c\ubbf8\ud130\ub97c \uc124\uc815\ud55c\ub2e4<\/span>.\n<\/p>\n<\/li>\n

                • \n
                  \uc774\ub7ec\ud55c<\/span>
                  \n\t\t\t\t\ud30c\ub77c\ubbf8\ud130\ub4e4\uc5d0 \ub300\ud55c<\/span> inheritance property \ub294<\/span> true \ub610\ub294<\/span> false \uc774\ub2e4<\/span>.\n<\/div>\n

                  \ud30c\ub77c\ubbf8\ud130\uc758<\/span>
                  \n\t\t\t\t\uc0c1\uc18d \uc18d\uc131\uc774<\/span> false \uc77c \ub54c\ub294<\/span> PDB \ub294<\/span> root \uc758 \ud30c\ub77c\ubbf8\ud130 \uac12\uc744 \uc0c1\uc18d\ud558\uc9c0 \uc54a\ub294\ub2e4<\/span>.\n<\/p>\n<\/li>\n

                • \n
                  V$SYSTEM_PARAMETER \uc758<\/span> ISPDB_MODIFIABLE \uac12\uc774<\/span> TRUE \uc774\uba74<\/span> ALTER SYSTEM SET \ubb38\uc7a5\uc744<\/span>\n\t\t\t<\/div>\n

                  \uc0ac\uc6a9\ud558\uc5ec<\/span>
                  \n\t\t\t\t\uc0c1\uc18d \uc18d\uc131\uc744<\/span> false \ub85c \ubcc0\uacbd \ud560 \uc218 \uc788\ub2e4<\/span>.\n<\/p>\n

                  \u00a0
                  \n\u00a0<\/p>\n<\/li>\n<\/ul>\n

                  \ud604\uc7ac<\/span> container \uac00<\/span> root \uc774\uba74<\/span> ALTER SYSTEM SET \ubb38\uc7a5\uc5d0<\/span> CONTAINER \uc808\uc744 \uc0ac\uc6a9\ud558\uc5ec<\/span> PDB \uc5d0 \uc0c1\uc18d<\/span>\n\t<\/p>\n

                  \ud30c\ub77c\ubbf8\ud130<\/span>
                  \n\t\t\uac12\uc744 \uc124\uc815\ud55c\ub2e4<\/span>.\n<\/p>\n

                  CONTAINER \uc808<\/span>
                  \n\t\t\ubb38\ubc95<\/span> : CONTAINER = {CURRENT | ALL}\n<\/p>\n

                    \n
                  • CURRENT(default) : \ud604\uc7ac<\/span>
                    \n\t\t\t\ucee8\ud14c\uc774\ub108\uc5d0\ub9cc \ud30c\ub77c\ubbf8\ud130 \uc124\uc815 \uac12 \uc801\uc6a9<\/span>.\n<\/li>\n
                  • ALL : CDB \uc5d0<\/span>
                    \n\t\t\t\uc18d\ud55c \ubaa8\ub4e0 \ucee8\ud14c\uc774\ub108<\/span>(root and all of the PDBs)\uc5d0 \ud30c\ub77c\ubbf8\ud130 \uc124\uc815 \uac12 \uc801\uc6a9<\/span>.\n<\/li>\n<\/ul>\n

                    \u00a0
                    \n\u00a0<\/p>\n

                    SQL> CONNECT SYS\/oracle@CDB1 AS SYSDBA;\n<\/p>\n

                    Connected.\n<\/p>\n

                    SQL> SHOW CON_NAME;\n<\/p>\n

                    \u00a0
                    \n\u00a0<\/p>\n

                    CON_NAME\n<\/p>\n

                    ——————————\n<\/p>\n

                    CDB$ROOT\n<\/p>\n

                    SQL> SELECT NAME, VALUE, ISPDB_MODIFIABLE\n<\/p>\n

                    FROM V$PARAMETER\n<\/p>\n

                    WHERE NAME = ‘open_cursors’;\n<\/p>\n

                    \u00a0
                    \n\u00a0<\/p>\n

                    NAME\n<\/p>\n

                    ——————————————————————————–\n<\/p>\n

                    VALUE\n<\/p>\n

                    ——————————————————————————–\n<\/p>\n

                    ISPDB_MODI\n<\/p>\n

                    ———-\n<\/p>\n

                    open_cursors\n<\/p>\n

                    300\n<\/p>\n

                    TRUE\n<\/p>\n

                    \u00a0
                    \n\u00a0<\/p>\n

                    SQL> ALTER SYSTEM SET OPEN_CURSORS = 200 CONTAINER = ALL;\n<\/p>\n

                    \u00a0
                    \n\u00a0<\/p>\n

                    SQL> ALTER SYSTEM SET OPEN_CURSORS = 400 CONTAINER = CURRENT;\n<\/p>\n

                    \u00a0
                    \n\u00a0<\/p>\n","protected":false},"excerpt":{"rendered":"

                    \ubc30\uacbd : \ud604\uc7ac 12c \uc124\uce58\ub418\uc5b4 \uc788\uace0 PDB 2\uac1c \uc124\uce58 \ub418\uc5b4 \uc788\ub294 \uc0c1\ud0dc \uc785\ub2c8\ub2e4. \u00a0\u00a0 \u00a0 # Tablespace \uad00\ub9ac MANAGING TABLESPACES IN A CDB Tablespace\ub294 \ub2e8 \ud558\ub098\uc758 \ucee8\ud14c\uc774\ub108(Root or PDB)\uc5d0\ub9cc \uc18d\ud55c\ub2e4. CDB\uc758 \uac01 \ucee8\ud14c\uc774\ub108\ub294 \uc790\uc2e0\uc758 \uae30\ubcf8 Tablespace\ub97c \uac00\uc9c4\ub2e4. \uae30\ubcf8 Tablespace\ub294 \ucee8\ud14c\uc774\ub108 \uac04\uc5d0 \uacf5\uc720 \ub420 \uc218 \uc5c6\ub2e4. \u00a0 \u00a0 SQL> col file_name for a40 SQL> SELECT TABLESPACE_NAME, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[20],"tags":[],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1214"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1214"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1214\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1214"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}