ORACLE TABLESPACES

check space:
SELECT tablespace_name,sum(bytes)/1024/1024 MB FROM dba_data_files GROUP BY tablespace_name ORDER by 1;
SELECT tablespace_name,sum(bytes)/1024/1024 MB,sum(maxbytes)/1024/1024 maxbytes_MB FROM dba_data_files GROUP BY tablespace_name,maxbytes ORDER by 1;
SELECT tablespace_name,file_name,bytes/1024/1024 use_MB,maxbytes/1024/1024 max_MB,(maxbytes-bytes)/1024/1024 left_MB FROM dba_data_files WHERE maxbytes 0  ORDER by 1;
SELECTtablespace_name,sum(bytes)/1024/1024 MB FROM dba_free_space GROUP BY tablespace_name;
switch mode:
ALTER TABLESPACE [ts] ONLINE|OFFLINE;
prepare files to copy:
ALTER TABLESPACE [ts] BEGIN|END BACKUP;
change file name:

1. ALTER TABLESPACE [ts] OFFLINE;
2. skopiowanie pliku przestrzeni do innej lokalizacji,
3. ALTER TABLESPACE [ts] RENAME DATAFILE '[old_file]’ TO ’[new_file]’;
4. ALTER TABLESPACE [ts] ONLINE;
5. usunięcie starego pliku

change ts name:
ALTER TABLESPACE [old_ts] RENAME TO [new_ts];
add new file to ts:
ALTER TABLESPACE [ts] ADD DATAFILE '[file]’ size [size]M AUTOEXTEND ON NEXT [size]M MAXSIZE UNLIMITED;
TEMP

check TEMP ts:
SELECT * FROM dba_temp_files;
SELECT * FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE’;
create TEMP ts:
CREATE TEMPORARY TABLESPACE [ts] TEMPFILE '[file]’ SIZE [size]M AUTOEXTEND ON MAXSIZE [size]M;
add new file to TEMP ts:
ALTER TABLESPACE [ts] ADD TEMPFILE '[file]’ SIZE [size]M
set TEMP ts:
ALTER USER [user] DEFAULT TABLESPACE [ts] TEMPORARY TABLESPACE [ts];
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [ts];
remove TEMP ts:
ALTER DATABASE TEMPFILE [nr] OFFLINE;
ALTER DATABASE TEMPFILE [nr] DROP;
check load TEMP ts:
SELECT tablespace_name, bytes_used, bytes_free FROM v$temp_space_header;
 
remove ts:
DROP TABLESPACE [ts] INCLUDING CONTENTS AND DATAFILES;
check segments from ts:
SELECT segment_name,segment_type FROM dba_segments WHERE TABLESPACE_NAME = '[ts]’;
move table to another ts:
ALTER TABLE [table] MOVE TABLESPACE [ts];
rebuild index after above:
ALTER INDEX [index] REBUILD;
check damage indexes:
SELECT index_name FROM user_indexes WHERE status=’UNUSABLE’;
ALTER INDEX [index] REBUILD TABLESPACE [ts];
rebuild damage indexes:
BEGIN
FOR rec in (SELECT index_name FROM dba_indexes WHERE status=’UNUSABLE’ and owner='[user]’)
LOOP
DBMS_UTILITY.EXEC_DDL_STATEMENT(’ ALTER INDEX [user].’||rec.index_name||’ REBUILD’);
END LOOP;
END;
move LOB segment to another ts:
ALTER TABLE [table]
MOVE LOB([lob_column]) STORE AS (
TABLESPACE [ts]
)
move table with LOB segment to another ts:
ALTER TABLE [table]
MOVE TABLESPACE LOB ([lob_column]) STORE AS (
TABLESPACE [ts]
)
change ts for user:
ALTER USER [user] DEFAULT TABLESPACE [ts] TEMPORARY TABLESPACE [ts];