Script to compile invalid objects and unusable indexes

 select owner,count(*) from dba_objects where status='INVALID' group by owner order by owner;

 select object_type,count(*) from dba_objects where status='INVALID' group by object_type order by object_type;

select owner,object_name,object_type from dba_objects where status='INVALID' order by object_type;


select 'alter PROCEDURE '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='PROCEDURE';

select 'alter VIEW '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='VIEW';

select 'alter TRIGGER '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='trigger';

select 'alter FUNCTION '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='FUNCTION';

select 'alter PACKAGE '||owner||'.'||object_name||' compile ;' from dba_objects where status='INVALID' and object_type='PACKAGE';

select 'alter PACKAGE '||owner||'.'||object_name||' compile body;' from dba_objects where status='INVALID' and object_type='PACKAGE BODY';

select 'alter SYNONYM '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type='SYNONYM';

select 'alter PUBLIC SYNONYM '||object_name||' compile;' from dba_objects where status='INVALID' and object_type='SYNONYM' AND OWNER='PUBLIC';


select 'alter index '||owner||'.'||index_name||' rebuild parallel 4 online;' from dba_indexes where status='UNUSABLE';

How to change service name in oracle 11g database

Before change the service name
SQL> sho parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba
SQL>

TNSENTRY

bash-3.2$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /data1/ora11g/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_DBA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dbname.domain.com)(PORT = 1531))


DBA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbname.domain.com)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dba)
    )
  )

Steps to change service name:

==>change service_name in TNS ENTRY and restart listener====> NOT WORKING.
DBA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dbname.domain.com)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dba.domain.com)
    )
  )

SQL> alter system set service_names='dba.domain.com' scope=spfile;

System altered.

SQL> sho parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2138521600 bytes
Fixed Size                  2161024 bytes
Variable Size            1006634624 bytes
Database Buffers         1107296256 bytes
Redo Buffers               22429696 bytes
Database mounted.
Database opened.

Checks after service_names parameter changed to 'dba.domain.com'

SQL> sho parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      dba.domain.com
SQL>
Now connection is working fine using service name.

Change default listener(name and port) to new listener in oracle 11g

1. create new listener using netca with different name and different port.

2. add the below in tnsname.ora file

listening_endpoint_1531 = (ADDRESS = (PROTOCOL = TCP)(HOST = dbname.domain.com)(PORT = 1531))

3. stop and start the listener.

4. login to database

SQL>conn /as sysdba


NOTE:But how will the instance know where the listener is located? 
You have to tell instance where listener is located manually by setting the LOCAL_LISTENER parameter.

SQL>alter system set LOCAL_LISTENER='listening_endpoint_1531' scope=both;

Now your instance is aware of location of the listener that is running.
But it may take up to 60 seconds for PMON process to carry instance information to the listener.
If you want this to happen immediately you can manually trigger service registration.

SQL> alter system register ;