Move objects including lobs, xml to new tablespace with dynamic SQL
Here objects are moved from tablespace users to tablespace users1: set pages 9999 lines 132 spool m2.sql select ‘alter table ‘||owner||’.’||table_name||’ move lob(‘||column_name||’) store as ( tablespace users1);’ from dba_lobs where tablespace_name = ‘USERS’ order by 1 / select ‘alter table ‘||owner||’.’||segment_name||’ move tablespace users1;’ from dba_segments where tablespace_name = ‘USERS’ and segment_type = ‘TABLE’ […]
Read More Move objects including lobs, xml to new tablespace with dynamic SQLdu -sk to identify space usage unix
Find out what directories are using up disk space in unix with this command: du -sk * | sort -n I often put it into a function so I can call it quickly: g () { du -sk * | sort -n } df -h (df -k on old machines) shows the filesystem disk utilisation.
Read More du -sk to identify space usage unixschema moves by the magic of partition exchange
Here’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and tables are kept in the same tablespace. Process for doing partition exchange is like this: […]
Read More schema moves by the magic of partition exchangeextent management local autoallocate sizes
Here is the algorithim for calculating next extent sizes with a system managed/autoallocate exten management local tablespace. The dependency is with the current segment size. Segment Size Next Extent Size less than 1m 64k 1m to 63m 1m 64m to 1023m 8m 1g or more 64m That is for 10gR2. Note that the above table […]
Read More extent management local autoallocate sizesAutoextend syntax
Note the order of maxsize and next, has to be specified in that order, a little annoyingly: alter database datafile ‘FILENAME’ autoextend on next 1024m maxsize 3072m ; alter tablespace TSNAME add datafile ‘FILENAME’ size 1024m autoextend on next 1024m maxsize 3072m ; From 10gR1 and above you can specify sizes in g. With 9i […]
Read More Autoextend syntaxTablespace space with sm$ views + database level space
The sm$ views are an easy way of seeing tablespace space usage. There is also an sm$ts_free view. set pages 9999 col tot_mb form 999,999 col use_mb form 999,999 col pct_used form 999 select t.tablespace_name, t.bytes/1024/1024 tot_mb, u.bytes/1024/1024 use_mb, 100*u.bytes/t.bytes pct_used from sys.sm$ts_avail t, sys.sm$ts_used u where t.tablespace_name = u.tablespace_name(+) order by 4 desc / […]
Read More Tablespace space with sm$ views + database level space