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;
SELECT tablespace_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. copy file to other localization
3. ALTER TABLESPACE [ts] RENAME DATAFILE '[old_file]' TO '[new_file]';
4. ALTER TABLESPACE [ts] ONLINE;
5. remove old file

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;

TEMPORARY tablespace

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 a.tablespace_name,b.file_name,a.bytes_used/1024/1024 MB_used, a.bytes_free/1024/1024 MB_free FROM v$temp_space_header a,dba_temp_files b where a.file_id=b.file_id;

DATA tablespace

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 move table:

ALTER INDEX [index] REBUILD;

check damage indexes:

SELECT index_name FROM user_indexes WHERE status='UNUSABLE'; ALTER INDEX [index] REBUILD TABLESPACE [ts];

rebuild all 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])

convert ts between OS:

CONVERT TABLESPACE '[ts_name]' TO PLATROFM = 'Linux IA (32-bit)' DB_FILE_NAME_CONVERT='[file_path]';