Configure Listener.ora and tnsnames.ora in orcle
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
Copyright (c) 1997, 2011, Oracle. All rights reserved.
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
as
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
as
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.