●PDB$SEEDの表領域変更よくつかうに書く$ORACLE_HOME/bin/oerr ora 02142HELP ORA-02142
ALTER SESSION SET CONTAINER=PDB$SEED;SHOW CON_NAME;SET LINESIZE 200COLUMN TABLESPACE_NAME FORMAT A25COLUMN STATUS FORMAT A10
SELECT tablespace_name, contents, status, bigfileFROM dba_tablespacesORDER BY tablespace_name;
COLUMN TABLESPACE_NAME FORMAT A25COLUMN FILE_NAME FORMAT A100
SELECT tablespace_name, file_id, file_nameFROM dba_data_filesORDER BY tablespace_name, file_id;
SELECT tablespace_name, file_id, file_nameFROM dba_temp_filesORDER BY tablespace_name, file_id;
SELECT tablespace_name, status, contentsFROM dba_tablespacesWHERE contents = 'UNDO';
SHOW PARAMETER undo_tablespace;
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;
show pdbs
ALTER SESSION SET CONTAINER=PDB$SEED;
SHOW CON_NAME;
COLUMN tablespace_name FORMAT A20COLUMN extent_management FORMAT A15COLUMN allocation_type FORMAT A15COLUMN segment_space_management FORMAT A15COLUMN retention FORMAT A15
SELECT tablespace_name, contents, extent_management, allocation_type, segment_space_management, retentionFROM dba_tablespacesORDER BY tablespace_name;
TABLESPACE_NAME CONTENTS EXTENT_MANAGEME ALLOCATION_TYPE SEGMENT_SPACE_M RETENTION-------------------- ---------- --------------- --------------- --------------- ---------------HUB3S01_TEMP01 TEMPORARY LOCAL UNIFORM MANUAL NOT APPLYHUB3S01_UNDO001 UNDO LOCAL SYSTEM MANUAL NOGUARANTEESYSAUX PERMANENT LOCAL SYSTEM AUTO NOT APPLYSYSTEM PERMANENT LOCAL SYSTEM MANUAL NOT APPLY
COLUMN tablespace_name FORMAT A15COLUMN file_name FORMAT A80COLUMN autoextensible FORMAT A12
SELECT tablespace_name, file_name, ROUND(bytes / 1024 / 1024, 1) AS size_mb, autoextensible, ROUND(maxbytes / 1024 / 1024, 1) AS max_mbFROM dba_temp_filesORDER BY tablespace_name, file_name;
CREATE BIGFILE TEMPORARY TABLESPACE HUB3S01_TEMP01 TEMPFILE SIZE 1024M;
COLUMN tablespace_name FORMAT A20COLUMN status FORMAT A10COLUMN contents FORMAT A10COLUMN logging FORMAT A10COLUMN extent_management FORMAT A12COLUMN allocation_type FORMAT A12COLUMN segment_space_management FORMAT A10COLUMN autoextend FORMAT A10
SELECT t.tablespace_name, t.status, t.contents, t.logging, t.extent_management, t.allocation_type, t.segment_space_management, ROUND(SUM(df.bytes) / 1024 / 1024, 1) AS size_mb, ROUND(SUM(df.maxbytes) / 1024 / 1024, 1) AS max_mb, MAX(df.autoextensible) AS autoextendFROM dba_tablespaces t JOIN dba_data_files df ON t.tablespace_name = df.tablespace_nameGROUP BY t.tablespace_name, t.status, t.contents, t.logging, t.extent_management, t.allocation_type, t.segment_space_managementORDER BY t.tablespace_name;
CREATE BIGFILE UNDO TABLESPACE HUB3S01_UNDO001 DATAFILE SIZE 1024M;
SELECT tablespace_name, file_id, file_nameFROM dba_data_filesORDER BY tablespace_name, file_id;
SELECT tablespace_name, file_id, file_nameFROM dba_temp_filesORDER BY tablespace_name, file_id;
SELECT tablespace_name, statusFROM dba_tablespacesWHERE contents = 'UNDO';
SHOW PARAMETER undo_tablespace;
ALTER SYSTEM SET undo_tablespace = HUB3S01_UNDO001 SCOPE=BOTH;
SELECT tablespace_name, statusFROM dba_tablespacesWHERE contents = 'UNDO';
SHOW PARAMETER undo_tablespace;
ここでPDB再起動ALTER SESSION SET CONTAINER = CDB$ROOT;show pdbs;ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;show pdbs;ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ WRITE FORCE;show pdbs;
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
SELECT tablespace_name, statusFROM dba_tablespacesWHERE contents = 'UNDO';
SHOW PARAMETER undo_tablespace;
SELECT property_name, property_valueFROM database_propertiesWHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE HUB3S01_TEMP01;
SELECT property_name, property_valueFROM database_propertiesWHERE property_name = 'DEFAULT_TEMP_TABLESPACE';
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE SYSTEM AUTOEXTEND ON MAXSIZE 1024M;ALTER TABLESPACE SYSAUX AUTOEXTEND ON MAXSIZE 1024M;
ここでPDB再起動ALTER SESSION SET CONTAINER = CDB$ROOT;show pdbs;ALTER PLUGGABLE DATABASE PDB$SEED CLOSE IMMEDIATE;show pdbs;ALTER PLUGGABLE DATABASE PDB$SEED OPEN READ ONLY;show pdbs;