In this post, we are going to learn how to configure listener.ora, tnsnames.ora and how to create a materialized views

Configure Oracle Networking

Configure listener.ora

A listener is configured with one or more listening protocol addresses, information about supported services, and parameters that control its run-time behavior. The listener configuration is stored in a configuration file namedlistener.ora

Example listener.ora file

SID_LIST_LISTENER =

(SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = TEST)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_2)

      (SID_NAME = TEST)

    )

)

LISTENER =

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.170)(PORT = 1521))

)

2. Configure tnsnames.ora

This tnsnames.ora file is a configuration file that contains net service names mapped to connect descriptors for the local naming method, or net service names mapped to listener protocol addresses.

Example tnsnames.ora file

TEST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.118)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TEST)

)

)

Testing tns connectivity using tnsping

[oracle@stagdb admin]$ tnsping TEST

TNS Ping Utility for Linux: Version 11.2.0.3.0 – Production on 27-MAR-2018 19:12:22

Used parameter files:

/u01/app/oracle/product/11.2.0/db_2/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.118)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST)))

OK (0 msec)

[oracle@stagdb admin]$

How to create a materialized view in Oracle 11g

The following example creates a materialized view with the name MVIEWTEST.

create materialized view MVIEWTEST

using no index

refresh force on demand

as

select * from ABC where name=’sd’;

AUDIT_TEST> select * from ABC;

NAME RNO

———- ———-

ABC 10

sdjk 234

sd 23

34dsf 23

AUDIT_TEST> create materialized view MVIEWTEST

using no index

refresh force on demand

select * from ABC;

2 3 4 5

Materialized view created.

AUDIT_TEST>

AUDIT_TEST> drop materialized view MVIEWTEST;

Materialized view dropped.

AUDIT_TEST> create materialized view MVIEWTEST

using no index

refresh force on demand

select * from ABC where name=’sd’; 2 3 4 5

Materialized view created.

AUDIT_TEST> select * from MVIEWTEST;

NAME RNO

———- ———-

sd 23

How to refresh a materialized view in Oracle 11g

AUDIT_TEST> exec dbms_mview.REFRESH (‘MVIEWTEST’, ‘C’, PARALLELISM => 4);

PL/SQL procedure successfully completed.

AUDIT_TEST>

=> Here ‘C’ means complete.

IMMEDIATE : The materialized view is populated immediately.

DEFERRED : The materialized view is populated on the first requested refresh.

The following refresh types are available.

FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.

COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.

FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A refresh can be triggered in one of two ways.

ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.

ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

DATA DICTIONARY VIEWS:

DBA_MVIEWS

DBA_MVIEW_LOGS

DBA_MVIEW_REFRESH_TIMES

Words from dbapath

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.