Tyo Sites and Notes

1. Moving all tables in a schema

select ‘alter table ‘|| table_name || ‘ move tablespace users’ from user_tables;

2. Moving all indexes in a schema

select ‘ALTER INDEX ‘ ||Index_name || ‘ REBUILD TABLESPACE INDX’ from user_indexes;

3. Check Tablespaces size in a database

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(lagi…)

Below is step for recreating Oracle temp Tablespace.

 == Step 1 Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  ‘/u01/app/oradata/temp01′ SIZE 10240M AUTOEXTEND OFF,
(lagi…)

A quite sometimes ago my friend asking about how to delete a datafile because he create too many datafiles in his tablespaces. i’m not sure why he want to do that, but because he said that it create a problem with some ORA error when he take the datafiles directly offline and delete it manual, then he restart the pc and then try to open the database via sqlplus. because i’m not in his place, i just assuming that the database state is in mount position but not open. since the oracle is still looking for the datafile that he is manually delete previously. (lagi…)