增大system表空间
1.查看表空间大小及数据文件位置
SQL> set line 132
SQL> col file_name for a50
SQL> col tablespace_name for a10
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;
TABLESPACE FILE_NAME BYTES/1024/1024
SYSTEM /u01/app/oracle/oradata/ZZW/pdb/system01.dbf 290（要先知道现在的system表空间大小）
SYSAUX /u01/app/oracle/oradata/ZZW/pdb/sysaux01.dbf 350
UNDOTBS1 /u01/app/oracle/oradata/ZZW/pdb/undotbs01.dbf 100
USERS /u01/app/oracle/oradata/ZZW/pdb/users01.dbf 5
JOJO /u01/app/oracle/oradata/ZZW/jojo01.dbf 10
2.扩大system表空间加10m
SQL>alter database datafile ‘/u01/app/oracle/oradata/ZZW/pdb/system01.dbf’ resize 300m;
（文件地址一定要写对）
3.再次查看
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;
TABLESPACE FILE_NAME BYTES/1024/1024
SYSTEM /u01/app/oracle/oradata/ZZW/pdb/system01.dbf 300 （大小已经发生改变）
SYSAUX /u01/app/oracle/oradata/ZZW/pdb/sysaux01.dbf 350
UNDOTBS1 /u01/app/oracle/oradata/ZZW/pdb/undotbs01.dbf 100
USERS /u01/app/oracle/oradata/ZZW/pdb/users01.dbf 5
JOJO /u01/app/oracle/oradata/ZZW/jojo01.dbf 10
undo表空间增加一个10M数据文件并自动扩展
1.查看表空间大小及数据文件位置
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;
TABLESPACE FILE_NAME BYTES/1024/1024
SYSTEM /u01/app/oracle/oradata/ZZW/pdb/system01.dbf 300
SYSAUX /u01/app/oracle/oradata/ZZW/pdb/sysaux01.dbf 350
UNDOTBS1 /u01/app/oracle/oradata/ZZW/pdb/undotbs01.dbf 100
USERS /u01/app/oracle/oradata/ZZW/pdb/users01.dbf 5
JOJO /u01/app/oracle/oradata/ZZW/jojo01.dbf 10
2.undo表空间增加一个5M数据文件并自动扩展
SQL> alter tablespace undotbs1 add datafile ‘/u01/app/oracle/oradata/ZZW/pdb/undotbs02.dbf’ size 5m autoextend on;
Tablespace altered.
3.再次查看
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;
TABLESPACE FILE_NAME BYTES/1024/1024
SYSTEM /u01/app/oracle/oradata/ZZW/pdb/system01.dbf 290
SYSAUX /u01/app/oracle/oradata/ZZW/pdb/sysaux01.dbf 350
UNDOTBS1 /u01/app/oracle/oradata/ZZW/pdb/undotbs01.dbf 100
UNDOTBS1 /u01/app/oracle/oradata/ZZW/pdb/undotbs02.dbf 5
USERS /u01/app/oracle/oradata/ZZW/pdb/users01.dbf 5
JOJO /u01/app/oracle/oradata/ZZW/jojo01.dbf 10
4.user表空间增加一个5M数据文件并自动扩展
SQL> alter tablespace users add datafile ‘/u01/app/oracle/oradata/ZZW/pdb/users02.dbf’ size 5m autoextend on;
Tablespace altered.
5.再次查看
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;
TABLESPACE FILE_NAME BYTES/1024/1024
SYSTEM /u01/app/oracle/oradata/ZZW/pdb/system01.dbf 290
SYSAUX /u01/app/oracle/oradata/ZZW/pdb/sysaux01.dbf 350
UNDOTBS1 /u01/app/oracle/oradata/ZZW/pdb/undotbs01.dbf 100
UNDOTBS1 /u01/app/oracle/oradata/ZZW/pdb/undotbs02.dbf 5
USERS /u01/app/oracle/oradata/ZZW/pdb/users01.dbf 5
USERS /u01/app/oracle/oradata/ZZW/pdb/users02.dbf 5
JOJO /u01/app/oracle/oradata/ZZW/jojo01.dbf 10
创建一个新的temp表空间
1.查看temp临时表空间位置信息
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;
TABLESPACE FILE_NAME BYTES/1024/1024
TEMP /u01/app/oracle/oradata/ZZW/pdb/temp01.dbf 36
2.查看临时表空间
SQL> select * from props$ ;
rows will be truncated
NAME VALUE$
DICT.BASE 2
DEFAULT_TEMP_TABLESPACE TEMP （默认临时表空间为TEMP）
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_EDITION ORA$BASE
Flashback Timestamp TimeZone GMT
TDE_MASTER_KEY_ID
DEFAULT_TBS_TYPE SMALLFILE
DICTIONARY_ENDIAN_TYPE LITTLE
GLOBAL_DB_NAME PDB
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CHARACTERSET AL16UTF16
NAME VALUE$
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NAME VALUE$
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_NUMERIC_CHARACTERS .,
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 32
DST_UPGRADE_STATE NONE
MAX_STRING_SIZE STANDARD
NAME VALUE$
EXPORT_VIEWS_VERSION 8
WORKLOAD_CAPTURE_MODE
WORKLOAD_REPLAY_MODE
NO_USERID_VERIFIER_SALT B1C8F128459F84EA6068906C6368712F
OLS_OID_STATUS 0
MAX_PDB_SNAPSHOTS 8
DBTIMEZONE +00:00
40 rows selected.
3.创建新的临时表空间
SQL> create temporary tablespace temp2 tempfile ‘/u01/app/oracle/oradata/ZZW/temp102.dbf’ size 10M;
Tablespace created.
4.查看是否创建完成
SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_temp_files;
TABLESPACE FILE_NAME BYTES/1024/1024
TEMP /u01/app/oracle/oradata/ZZW/pdb/temp01.dbf 36
TEMP2 /u01/app/oracle/oradata/ZZW/temp102.dbf 10
5.更改临时表空间为TEMP2
SQL> alter database default temporary tablespace temp2;
Database altered.
6.再次查看
SQL> select * from database_properties;
rows will be truncated
PROPERTY_NAME PROPERTY_VALUE
NO_USERID_VERIFIER_SALT_COPY B1C8F128459F84EA6068906C6368712F
MAX_PDB_SNAPSHOTS 8
DICTIONARY_ENDIAN_TYPE LITTLE
LOCAL_UNDO_ENABLED TRUE
OLS_OID_STATUS 0
GLOBAL_DB_NAME PDB
MAX_STRING_SIZE STANDARD
MAX_SHARED_TEMP_SIZE UNLIMITED
MAX_PDB_STORAGE UNLIMITED
NO_USERID_VERIFIER_SALT B1C8F128459F84EA6068906C6368712F
WORKLOAD_REPLAY_MODE
PROPERTY_NAME PROPERTY_VALUE
WORKLOAD_CAPTURE_MODE
NLS_RDBMS_VERSION 19.0.0.0.0
NLS_NCHAR_CONV_EXCP FALSE
NLS_LENGTH_SEMANTICS BYTE
NLS_COMP BINARY
NLS_DUAL_CURRENCY $
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_SORT BINARY
PROPERTY_NAME PROPERTY_VALUE
NLS_DATE_LANGUAGE AMERICAN
NLS_DATE_FORMAT DD-MON-RR
NLS_CALENDAR GREGORIAN
NLS_NUMERIC_CHARACTERS .,
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_CHARACTERSET AL32UTF8
NLS_ISO_CURRENCY AMERICA
NLS_CURRENCY $
NLS_TERRITORY AMERICA
NLS_LANGUAGE AMERICAN
DEFAULT_TBS_TYPE SMALLFILE
PROPERTY_NAME PROPERTY_VALUE
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 32
DST_UPGRADE_STATE NONE
DBTIMEZONE +00:00
TDE_MASTER_KEY_ID
EXPORT_VIEWS_VERSION 8
Flashback Timestamp TimeZone GMT
DEFAULT_EDITION ORA$BASE
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TEMP_TABLESPACE TEMP2
DICT.BASE 2
————————————————
版权声明：本文为CSDN博主「冥净」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_45833902/article/details/105221604
