Resize tablespace (Shrink tablespace)

By using this it will generate a command to resize after calculating the old tables space to new table space.

select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+2)||' '||'m'||';' from (
select /*+ rule */
   a.tablespace_name,
    a.file_name,
   a.bytes/1024/1024 file_size_MB,
    (b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a        ,
     (select file_id,max(block_id) maximum        
      from dba_extents        
      group by file_id) b,
      dba_extents c,
     (select value db_block_size        
      from v$parameter        
      where name='db_block_size') d
where a.file_id=  b.file_id
and   c.file_id  = b.file_id
and   c.block_id = b.maximum
order by a.tablespace_name,a.file_name);


Example output -:

'ALTERDATABASEDATAFILE'||''''||FILE_NAME||''''||'RESIZE'||ROUND(HIGHWATER+2)||''
--------------------------------------------------------------------------------
alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_cachetbs_807kzmyb_.dbf' resize 18 m;







alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_cmstbs_807kxdw4_.dbf' resize 7 m;

alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_dbaudit__807kwjwy_.dbf' resize 246m;

alter database datafile '/data/oracle/oradata/CLIPDB/datafile/o1_mf_dbaudit__807kwy3f_.dbf' resize 154m;

ORA-01653: unable to extend table * by 128 in tablespace

Error occurs because given table space size is not enough to grow. This can use to increase or decrease the table spaces 

log in to the database as sysdba

[oracle@ovmclipdb datafile clipdb]$ sqlplus / as sysdba

Alter database with a new size

SQL> alter database datafile 'datafile_name' RESIZE new_size;

 SQL>commit;


To select the datafile location use below command.

SQL>  select NAME from v$datafile;

Change scheema password in oracle

Log in to the database as sys user.

oracle$ sqlplus / as sysdba

 give a new username.

SQL> alter user username identified by newpassword;

SQL>commit

ORA-03113: end-of-file on communication channel

Getting error while starting a database.

SQL> startup;
ORACLE instance started.

Total System Global Area 1.0055E+10 bytes
Fixed Size                  2237008 bytes
Variable Size            3992980912 bytes
Database Buffers         6039797760 bytes
Redo Buffers               19767296 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 24621
Session ID: 1345 Serial number: 3

Check the alert log.

Alert log path is ( Ex alert log -: alert_cgemimo.log)

SQL>  show parameter background

NAME                                                      TYPE        VALUE
------------------------------------                   -----------   ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /oracle/app/oracle/diag/rdbms/PROD/PROD/trace




If it shows something like below

ORA-19815: WARNING: db_recovery_file_dest_size of 21474836480 bytes is 100.00% used, and has 0 remaining bytes available.


Then it means  db_recovery_file_dest_size id full.




 
There is three ways to solve this issue...........:)

1. One solution is log as rman and delete archive logs.

$rman target /
RMAN> delete archivelog all;



2. If the oracle is not available then

start the database as nomount.

SQL> startup nomount;




Get the db_recovery_file_dest_size
 
SQL> show parameter recovery_file_dest;

NAME                                                      TYPE                     VALUE
------------------------------------                  -----------                  ------------------------------
db_recovery_file_dest                       string                   /var/app/oracle/fast_recovery_area
db_recovery_file_dest_size              big integer        20G


 Increase the  DB_RECOVERY_FILE_DEST_SIZE

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 25G SCOPE=BOTH;


Shutdown the database

SQL> shutdown immediate;

Then startup and it all will be fine.




3. If nothing success then delete manually from the db_recovery_file_dest

Log to the database as nomount (mentioned above) and find the db_recovery_file_dest



SQL> show parameter db_recovery_file_dest ;

NAME                                                  TYPE                 VALUE
------------------------------------               -----------             ------------------------------
db_recovery_file_dest                   string                  /var/app/oracle/fast_recovery_ area
db_recovery_file_dest_size         big integer         25G



Go to the above mentioned location and delete unwanted files.

0020: maximum number of processes (1000) exceeded

This error will not allow to login to the database. So firstly shut down any applications that connect to the particular database.

Then it will allow to log in to  the database.

SQL> select count(*) from v$process;

  COUNT(*)
----------
    27

 SQL> show parameter processes

 processes                 integer     1000

Now parameter process are set to 1000

To increase the parameters 

SQL> alter system set processes=3000 scope=spfile;

Shutdown the database.

 

SQL> shutdown immediate

Database closed. 

Database dismounted. 

ORACLE instance shut down. 

Then start the database

 

 SQL> startup ORACLE instance started. 

Verify the changes

 SQL> show parameter processes 

NAME                                   TYPE           VALUE

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

processes                             integer      3000