DBA日常运维
2021-07-08 本文已影响0人
苏水的北
表空间：
1、查询表属于哪个表空间：
SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES;
2、给表空间添加数据文件：
alter tablespace STDF_DATA add datafile '/u02/ora_data/STDFDB/stdftdata210702.dbf' size 2G autoextend on next 1M maxsize 30G;
3、增加表空间数据文件的大小(之前初始化大小2G，自增长可以到30G，现在把初始化大小改为20G)：
alter database datafile '/u02/ora_data/STDFDB/stdftdata210701.dbf' resize 20G；
SQL> create tablespace cat datafile '/ora_data/orcl/cat.dbf' size 10G;
Tablespace created.
SQL> select tablespace_name,block_size,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT SEGMENT_SPAC
------------------------------------------------------------ ---------- -------------------- ------------
SYSTEM 8192 LOCAL MANUAL
SYSAUX 8192 LOCAL AUTO
UNDOTBS1 8192 LOCAL MANUAL
TEMP 8192 LOCAL MANUAL
USERS 8192 LOCAL AUTO
EXAMPLE 8192 LOCAL AUTO
CAT 8192 LOCAL AUTO
SQL> select tablespace_name,block_size,initial_extent,next_extent,extent_management,allocation_type,segment_space_management from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPAC
-------------------- ---------- -------------- ----------- -------------------- ------------------ ------------
SYSTEM 8192 65536 LOCAL SYSTEM MANUAL
SYSAUX 8192 65536 LOCAL SYSTEM AUTO
UNDOTBS1 8192 65536 LOCAL SYSTEM MANUAL
TEMP 8192 1048576 1048576 LOCAL UNIFORM MANUAL
USERS 8192 65536 LOCAL SYSTEM AUTO
EXAMPLE 8192 65536 LOCAL SYSTEM AUTO
CAT 8192 65536 LOCAL SYSTEM AUTO
SQL> create user cat identified by 123456 default tablespace cat;
User created.
SQL> grant dba to cat;
Grant succeeded.
SQL> create table cat.test as select * from dba_objects;
Table created.
SQL> select owner,segment_name,tablespace_name,blocks,extents,bytes from dba_segments where tablespace_name='CAT';
OWNER SEGMENT_NAME TABLESPACE BLOCKS EXTENTS BYTES
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
CAT TEST CAT 1280 25 10485760
SQL> select owner,segment_name,tablespace_name,extent_id,bytes,blocks,block_id from dba_extents where owner='CAT';
OWNER SEGMENT_NAME TABLESPACE EXTENT_ID BYTES BLOCKS BLOCK_ID
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
CAT TEST CAT 0 65536 8 128
CAT TEST CAT 1 65536 8 136
CAT TEST CAT 2 65536 8 144
CAT TEST CAT 3 65536 8 152
CAT TEST CAT 4 65536 8 160
CAT TEST CAT 5 65536 8 168
CAT TEST CAT 6 65536 8 176
CAT TEST CAT 7 65536 8 184
CAT TEST CAT 8 65536 8 192
CAT TEST CAT 9 65536 8 200
CAT TEST CAT 10 65536 8 208
OWNER SEGMENT_NAME TABLESPACE EXTENT_ID BYTES BLOCKS BLOCK_ID
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
CAT TEST CAT 11 65536 8 216
CAT TEST CAT 12 65536 8 224
CAT TEST CAT 13 65536 8 232
CAT TEST CAT 14 65536 8 240
CAT TEST CAT 15 65536 8 248
CAT TEST CAT 16 1048576 128 256
CAT TEST CAT 17 1048576 128 384
CAT TEST CAT 18 1048576 128 512
CAT TEST CAT 19 1048576 128 640
CAT TEST CAT 20 1048576 128 768
CAT TEST CAT 21 1048576 128 896
OWNER SEGMENT_NAME TABLESPACE EXTENT_ID BYTES BLOCKS BLOCK_ID
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ----------
CAT TEST CAT 22 1048576 128 1024
CAT TEST CAT 23 1048576 128 1152
CAT TEST CAT 24 1048576 128 1280
SQL> select 'alter tablespace '||tablespace_name||' begin backup;' from dba_tablespaces where logging='LOGGING';
'ALTERTABLESPACE'||TABLESPACE_NAME||'BEGINBACKUP;'
--------------------------------------------------------------------------------------------------------------------------
alter tablespace SYSTEM begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;
alter tablespace CAT begin backup;
SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE
------------------------------ ----------
/ora_data/orcl/users01.dbf USERS
/ora_data/orcl/undotbs01.dbf UNDOTBS1
/ora_data/orcl/sysaux01.dbf SYSAUX
/ora_data/orcl/system01.dbf SYSTEM
/ora_data/orcl/example01.dbf EXAMPLE
/ora_data/orcl/cat.dbf CAT
