{"id":160,"date":"2008-09-28T21:07:00","date_gmt":"2008-09-28T19:07:00","guid":{"rendered":"http:\/\/antynet.pl\/index.php\/2008\/09\/28\/oracle-tablespaces\/"},"modified":"2010-08-24T23:52:25","modified_gmt":"2010-08-24T21:52:25","slug":"oracle-tablespaces","status":"publish","type":"post","link":"https:\/\/www.antynet.pl\/index.php\/2008\/09\/28\/oracle-tablespaces\/","title":{"rendered":"ORACLE TABLESPACES"},"content":{"rendered":"

check space:
<\/span>SELECT tablespace_name,sum(bytes)\/1024\/1024 MB FROM dba_data_files GROUP BY tablespace_name ORDER by 1;<\/span>
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;<\/span>
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;<\/span>
SELECTtablespace_name,sum(bytes)\/1024\/1024 MB FROM dba_free_space GROUP BY tablespace_name;
switch mode:<\/span>
ALTER TABLESPACE [ts] ONLINE|OFFLINE;
prepare files to copy:
<\/span>ALTER TABLESPACE [ts] BEGIN|END BACKUP;
change file name:<\/span>
<\/span><\/p>\n

    \n
  1. ALTER TABLESPACE [ts] OFFLINE;<\/li>\n
  2. skopiowanie pliku przestrzeni do innej lokalizacji,<\/li>\n
  3. ALTER TABLESPACE [ts] RENAME DATAFILE '[old_file]’ TO ’[new_file]’;<\/new_file><\/li>\n
  4. ALTER TABLESPACE [ts] ONLINE;<\/li>\n
  5. usuni\u0119cie starego <\/span>pliku<\/li>\n<\/ol>\n

    change ts name:<\/span>
    ALTER TABLESPACE [old_ts] RENAME TO [new_ts];
    <\/span>add new file to ts:<\/span>
    ALTER TABLESPACE [ts] ADD DATAFILE '[file]’ size [size]M AUTOEXTEND ON NEXT [size]M MAXSIZE UNLIMITED;<\/size>
    TEMP<\/b>
    <\/size>
    check TEMP ts:
    <\/span>SELECT * FROM dba_temp_files;
    <\/span>SELECT * FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE’;
    <\/span>create TEMP ts:<\/span>
    CREATE TEMPORARY TABLESPACE [ts] TEMPFILE '[file]’ SIZE [size]M AUTOEXTEND ON MAXSIZE [size]M;
    add new file to TEMP ts:<\/span>
    ALTER TABLESPACE [ts] ADD TEMPFILE '[file]’ SIZE [size]M
    set TEMP ts:<\/span>
    <\/span>ALTER USER [user] DEFAULT TABLESPACE [ts] TEMPORARY TABLESPACE [ts];<\/span>
    ALTER DATABASE DEFAULT TEMPORARY TABLESPACE [ts];
    remove TEMP ts:<\/span>
    ALTER DATABASE TEMPFILE [nr] OFFLINE;
    ALTER DATABASE TEMPFILE [nr] DROP;
    check load TEMP ts<\/span>:
    SELECT tablespace_name, bytes_used, bytes_free FROM v$temp_space_header;
     <\/span><\/span>
    remove ts<\/span>:
    DROP TABLESPACE [ts] INCLUDING CONTENTS AND DATAFILES;
    check segments from ts<\/span>:
    SELECT segment_name,segment_type FROM dba_segments WHERE TABLESPACE_NAME = '[ts]’;
    move table to another ts:<\/span>
    ALTER TABLE [table] MOVE TABLESPACE [ts];
    rebuild index after above:<\/span>
    ALTER INDEX [index] REBUILD;
    check damage indexes:<\/span>
    SELECT index_name FROM user_indexes WHERE status=’UNUSABLE’;
    ALTER INDEX [index] REBUILD TABLESPACE [ts];
    rebuild damage indexes:<\/span>
    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:<\/span>
    ALTER TABLE [table]
    MOVE LOB([lob_column]) STORE AS (
    TABLESPACE [ts]
    )
    move table with LOB segment to another ts:<\/span>
    ALTER TABLE [table]
    MOVE TABLESPACE LOB ([lob_column]) STORE AS (
    TABLESPACE [ts]
    )
    change ts for user:<\/span>
    ALTER USER [user] DEFAULT TABLESPACE [ts] TEMPORARY TABLESPACE [ts];<\/size><\/span><\/p>\n

    convert ts between OS:<\/span><\/size><\/span><\/div>\n
    CONVERT TABLESPACE '[ts_name]’ TO PLATROFM = 'Linux IA (32-bit)’ DB_FILE_NAME_CONVERT='[file_path]’;<\/span><\/div>\n

    <\/size><\/span><\/div>\n

    <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

    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: ALTER […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1],"tags":[79],"class_list":["post-160","post","type-post","status-publish","format-standard","hentry","category-bez-kategorii","tag-oracle-tablespaces"],"_links":{"self":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/comments?post=160"}],"version-history":[{"count":2,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/160\/revisions"}],"predecessor-version":[{"id":219,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/posts\/160\/revisions\/219"}],"wp:attachment":[{"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/media?parent=160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/categories?post=160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.antynet.pl\/index.php\/wp-json\/wp\/v2\/tags?post=160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}