Indian Gods

/

Oracle: How to Shrink Tablespaces

Thanks to asktom article

Run the below sql as sys. This will give list of datafiles and possible savings.
You can then run the generated alter database datafile... statements to shrink
the datafiles.

----------- maxshrink.sql ----------------------------------

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/

column cmd format a75 word_wrapped

select 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

----------------------------------------------------------

Oracle: Startup process

Oracle Startup Process

When you start Oracle, it looks for the files in the following order
1) ORACLE_HOME\DATABASE\SPFILE.ora
2) If above file is not found, it look for spfile.ora in the same directory.
3) If the above files are not found, it looks for init.ora

create spfile from pfile ='C:\oracle\product\10.2.0\db_1\database\initorcldb.ora';
create pfile from spfile;
create pfile='C:\oracle\product\10.2.0\db_1\database\mypfile.ora' from spfile

Startup pfile= 'C:\oracle\product\10.2.0\db_1\database\initORCLDB.ora';

You cannot use spfile with startup command

startup spfile --> wont work

however, you can edit init.ora and add a parameter SPFILE='C:\oracle\product\10.2.0\db_1\database\SPFILEORCLDB.ORA'

and then say startup pfile = ...


Startup NOMOUNT

1) Control files are not read
2) Data files are not open
3) Background processes are started, SGA is allocated to Oracle by OS.
4) Instance is running by itself

Startup MOUNT

1) Oracle associates Instance with the database
2) Oracle opens and reads control files and gets names and location of data files and redo log files

Alter database mount
or
Startup Mount

Startup OPEN
1) Last stage of startup process.
2) Oracle reads all the data files and online redo logs, verifies that the database is consistent
3) If the files are not consistent, background process performs media recovery automatically.
2) Now all users can connect to database.

Alter database Open

Startup --> Mounts and Opens the database


Startup Restrict
1) No other users can connect to database other than you.
2) All new logins to the database are prevented
3) Existing logins will work.

Read Only

Startup Mount
Alter database open Read only


Shutdown or Shutdown Normal
1) No new user connections are allowed
2) Oracle waits for all users to exit their sessions, before shutting down the database.
3) No instance recovery is needed, as Oracle will write all redo log buffers to disk and database will be consistent
3) Oracle closes data files, terminates background process and sga is deallocated

Shutdown Transactional
1) Oracle waits for all active transactions to complete
2) Oracle will not wait for all users to log out their sessions.
3) New connections are not permitted, existing users cannot start new transaction
4) As long as no pending transactions, oracle will shutdown
5) No instance recovery is needed

Shutdown Immediate
1) All active transactions are rolled back
2) Disconnects all active users
3) This process is not immediate as it has to rollback all existing transactions
4) No new connections are permitted
5) No instance recovery needed

Shutdown Abort
1) Existing connections are terminated. Doesn't care about rolling back
2) Redo logs buffers are not written to disk
3) Upon restart, Oracle performs instance recovery.

Dropping Database
1) Connect as sys
2) Startup restrict mount
3) select name from v$database --> Just to check
4) Drop Database