Materialized view refresh error(ORA-00942: table or view does not exist,ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821)

SQL>EXEC DBMS_MVIEW.REFRESH('TEST_MV','C','',FALSE,FALSE,0,0,0,TRUE);
BEGIN DBMS_MVIEW.REFRESH('DM_SALES_GROSS_NET_MV','C','',FALSE,FALSE,0,0,0,TRUE); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3017
ORA-06512: at line 1

SQL>

SOLUTION:

SQL>ALTER MATERIALIZED VIEW TEST_MV COMPILE;

Materialized view altered.

SQL>exec DBMS_MVIEW.REFRESH('DM_SALES_GROSS_NET_MV','C','',FALSE,FALSE,0,0,0,TRUE);

PL/SQL procedure successfully completed.

SQL>

Restore table from Recyclebin 11g/10g

I am Sharing my experience in recovery table from recyclebin

SQL> select object_name, original_name, type,droptime from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME    TYPE    DROPTIME
————————————-                   ————————–       ————    ————————
BIN$RVdHjbBfXI3gUwIAAH/nrg==$0    T1           TABLE    2016-09-05:01:32:05
BIN$RVVsfQkeTzfgUwIAAH8GCw==$0    T1           TABLE    2016-09-05:01:35:36


Here we can use two ways to restore the table

STEP 1:

This will restore the latest drop table to restore.

SQL> flashback table t1 to before drop;

Flashback complete.

STEP 2:

The below command to restore the selected recyclebin object to restore.

SQL> flashback table “BIN$RVdHjbBfXI3gUwIAAH/nrg==$0" to before drop;
Flashback complete.

SQL> 

Find database total, used and free size.

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select    round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

,               round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

                round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

,               round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from    (select    bytes

                from      v$datafile

                union    all

                select    bytes

                from      v$tempfile

                union    all

                select    bytes

                from      v$log) used

,               (select sum(bytes) as p

                from dba_free_space) free

group by free.p

/

Database Size        Used space           Free space

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

424 GB               213 GB               211 GB

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 ;