oracle管理之 tablespace(server.102 b14231)

Using Multiple Tablespaces
Assigning Tablespace Quotas to Users

alter tablespace tt read only;-- read write
alter tablespace tt rename to readonly
SQL> alter tablespace readonly offline;

Tablespace altered

SQL>
SQL> alter tablespace readonly online;

Tablespace altered

DBA_TABLESPACES
USER_TABLESPACES
V$TABLESPACE
DBA_TABLESPACE_GROUPS
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf'
SIZE 50M
TABLESPACE GROUP group1;
ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;


DB_nK_CACHE_SIZE

CREATE TABLESPACE FORCE LOGGING
ALTER TABLESPACE...OFFLINE normal/temporary/immediate

ALTER TABLESPACE flights READ ONLY; //等待之前的事務commit or rollback並阻止新的事務
--檢視等待的事務和sql
SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%';
--刪除tablespace
it is best to take the tablespace offline before dropping it.
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
--sysaux
V$SYSAUX_OCCUPANTS ---顯示sysaux內的components

DBMS_SPACE_ADMIN

V$TRANSPORTABLE_PLATFORM
--檢視是否支援表空間傳輸
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
ALTER TABLESPACE sales_1 READ ONLY;
ALTER TABLESPACE sales_2 READ ONLY;
--轉位元組序
RMAN> CONVERT DATAFILE
2> '/hq/finance/work/tru/tbs_31.f',
3> '/hq/finance/work/tru/tbs_32.f',
4> '/hq/finance/work/tru/tbs_41.f'
5> TO PLATFORM="Solaris[tm] OE (32-bit)"
6> FROM PLATFORM="HP TRu64 UNIX"
7> DB_FILE_NAME_CONVERT=
8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"
9> PARALLELISM=5;

--相關動態效能檢視
select * from V$TABLESPACE;
select * from DBA_TABLESPACES;
select * from USER_TABLESPACES;
select * from DBA_TABLESPACE_GROUPS;
select * from DBA_SEGMENTS;
select * from USER_SEGMENTS;
select * from DBA_EXTENTS;
select * from USER_EXTENTS;
select * from DBA_FREE_SPACE;
select * from USER_FREE_SPACE;
select * from V$DATAFILE;
select * from V$TEMPFILE;
select * from DBA_DATA_FILES;
select * from DBA_TEMP_FILES;
select * from V$TEMP_EXTENT_MAP;
select * from V$TEMP_EXTENT_POOL;
select * from V$TEMP_SPACE_HEADER;
select * from DBA_USERS;
select * from DBA_TS_QUOTAS;
select * from V$SORT_SEGMENT;
select * from V$TEMPSEG_USAGE;[@more@]