Author: Biju

www.bijoos.com
Posted on

Read Only Tables [Orig Published Apr 2000]


In Oracle (all versions including 10g) you can make a tablespace read only, but not a single table. To make a table read only, you need to have that table in its own tablespace. If the tablespace has many tables, then you can either make all the tables read only or not. For large or critical tables, you can have the tables that need to be made read only grouped together in tablespaces. This is especially important when you’re are partitioning large tables. For example, if you partition a table that loads historical data, you can make the older partitions read only by making their corresponding tablespace read only.
To make the tablespace read only, issue the command:

ALTER TABLESPACE READ ONLY;

To make the tablespace back to read write mode, issue the command:

ALTER TABLESPACE READ WRITE;

FYI, you cannot make the SYSTEM tablespace to READ ONLY. You can drop a table from the read only tablespace, but not truncate, update, delete or insert.
So, what do you do if you need to make just one table read only, not all the tables in the tablespace…. Well, you can use triggers. Create a trigger which fires for all insert, update and delete operations on the table… Here is an example:

SQL> create or replace trigger emp_read_only
2 before insert or update or delete
3 on emp
4 begin
5 raise_application_error (-20001, 'Table EMP is read only, you cannot modify data.');
6 end;
7 /

Trigger created.

SQL> delete from emp;
delete from emp
*
ERROR at line 1:
ORA-20001: Table EMP is read only, you cannot modify data.
ORA-06512: at "BIJU.EMP_READ_ONLY", line 2
ORA-04088: error during execution of trigger 'BIJU.EMP_READ_ONLY'
Update:
As of Oracle11g, a new clause is introduced in the ALTER TABLE statement, that can make a table to read-only.
ALTER TABLE READ ONLY;

If you try to perform insert or update or delete on read-only table, you get an ORA-12081 error. The nice part about this feature is you are not able to do TRUNCATE operation on a read-only table. Remember, even if the tablespace is read-only, you can truncate a table. You can perform operations on index associated with read-only tables.
To resume normal update activity on the table, perform 

ALTER TABLE READ WRITE;
A new column READ_ONLY is added to DBA_TABLES. To list all the read-only tables in the database, you could do
select owner, table_name, tablespace_name
from dba_tables
where read_only = ‘YES’; 
Posted on

SQL*Net Configuration [Orig Published – Apr 1999]


Let’s set up SQL*Net (known stuff for most DBAs, but I get many questions on this subject often, so here I just provide some examples/templates for the configuration files) on the UNIX server and client PC using the TCP/IP communication protocol. In Oracle8 SQL*Net is called Net8. SQL*Net enables client-server and server-server communication across any network. When a client or server makes a connection request, SQL*Net receives the request and, if more than one machine is involved, passes the request to its underlying layer, the transparent network substrate (TNS), to be transmitted over the appropriate communications protocol to the appropriate server. On the server, SQL*Net receives the request from TNS and passes it to the database as a network message with one or more parameters (that is, a SQL statement). For more information on SQL*Net technology, please refer to “Understanding SQL*Net” manual from Oracle corp.

SQL*Net software

SQL*Net and required protocol adapters come with the Oracle RDBMS CD and the Oracle client CD. Install SQL*Net software and TCP/IP protocol adapters along with server installation. SQL*Net on the server would be installed to $ORACLE_HOME/network. The executables are under $ORACLE_HOME/bin. Oracle7 client software would be installed under $ORACLE_HOME/network and Oracle8 Net8 client software would be installed under $ORACLE_HOME/net80. It is possible to access a Oracle7 database using Net8, also to access a Oracle8 database using SQL*Net (Oracle8 specific features may not be available).

Configuration

SQL*Net is mainly configured using 3 ascii files, which contain information about the database, the protocol, the host name and connection port. The default location for the configuration files is $ORACLE_HOME/network/admin. You may specify a different location by specifying the enviornment variable TNS_ADMIN on the server side. For client side, set this variable in the client side Windows Registry (for 32 bit) or in the oracle.ini file (for 16 bit). The following are the files associated.

listener.ora

The listener configuration file. This file is set up in the server. Specify the type of protocol (we discuss TCP/IP), the host name, the database instance name (SID) and oracle home directory for this instance. You can have multiple databases configured under the same listener name. The standard and default and recommended listener name is LISTENER and most of the machines need only one listener. You can have a Oracle7 listener and Oracle8 listener running with the same name but listening to different ports. Here is a sample listener.ora file:

USE_PLUG_AND_PLAY_LISTENER = OFF
USE_CKPFILE_LISTENER = OFF
CONNECT_TIMEOUT_LISTENER = 40
LISTENER=
	(ADDRESS_LIST=
	(ADDRESS=
		(PROTOCOL=IPC)
		(KEY=PRODDB)
	)
	(ADDRESS=
		(COMMUNITY = tcp.world)
		(PROTOCOL=tcp)
		(HOST=prodserver)
		(PORT=1521)
	)
	)
SID_LIST_LISTENER=(SID_LIST=
	(SID_DESC=
	(GLOBAL_DBNAME = proddb1.world)
	(SID_NAME = PRODDB1)
	(ORACLE_HOME = /ora0/app/oracle/product/8.0.5)
	(PRESPAWN_MAX = 500)
	(PRESPAWN_LIST=
		(PRESPAWN_DESC=
		(PROTOCOL=TCP) 
		(POOL_SIZE=10)
		(TIMEOUT = 2)
	)
	)
	(SID_DESC=
	(GLOBAL_DBNAME = proddb2.world)
	(SID_NAME = PRODDB2)
	(ORACLE_HOME = /ora0/app/oracle/product/8.0.5)
	)
	)

Please note that since the listener name is LISTENER, the parameters also end with LISTENER, if your listener name is DALLAS, the parameters would be SID_LIST_DALLAS, CONNECT_TIMEOUT_DALLAS, etc.
There are two instances defined in this listener file. Note that the second entry does not have any PRESPAWN entries (optional). For a description of these parameters and other control parameters, please refer to the manual “Understanding SQL*Net”.

tnsnames.ora

This file is used to identify the destinations (databases). The file has information for the connect string the user specify, such as, the port number, host name, SID name, etc. This file should be available locally from where the connection is made (at the client PC as well as server). You can have different alias names (connect strings) for the same database defined. For each alias name, define an entry in this file. Here is an example:

DALPROD.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = prodserver)
		(PORT = 1521)
		)
	)
	(CONNECT_DATA = (SID = PRODDB1)
	)
	)
PRODDB1.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = prodserver)
		(PORT = 1521)
		)
	)
	(CONNECT_DATA = (SID = PRODDB1)
	)
	)
DALDEV.world =
	(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = devserver)
		(PORT = 1521)
		)
	(ADDRESS =
		(COMMUNITY = tcp.world)
		(PROTOCOL = TCP)
		(HOST = devserver)
		(PORT = 1525)
		)
	)
	(CONNECT_DATA = (SID = TESTDB1)
	)
	)

Make sure you have the HOST, PORT and SID defined correctly. You can also provide the IP address of the HOST instead of name. Notice the entry for DALDEV, there are two port addresses, this is to randomize connections between listners, if there are two listeners on the server (listener load balancing). All the lines are self explanatory, for more information, refer to “Understanding SQL*Net”.

sqlnet.ora

This is a configuration file, which may be present in the server as well as the client. Basically this file is used to cofigure the following:

Dead connections detection
Tracing and logging parameters
Default domain (notice that in the tnsnames.ora file we have .world in the connect string names, you can specify default domain as world in this file, so the users need not specity it while connecting)
Other optional parameters

A sample file would be like this:

AUTOMATIC_IPC = OFF
SQLNET.EXPIRE_TIME = 0
NAMES.DEFAULT_DOMAIN = world
NAME.DEFAULT_ZONE = world
# DAEMON.TRACE_MASK = (106)
# DAEMON.TRACE_DIRECTORY = /ora_dump/network/trace
# DAEMON.TRACE_LEVEL = OFF
# Tracing Client Activity
# TRACE_LEVEL_CLIENT = USER
# TRACE_FILE_CLIENT = sqlnetc.trc
# TRACE_DIRECTORY_CLIENT = /ora_dump/network/trace
# Tracing Server Activity
# TRACE_LEVEL_SERVER = 16
# TRACE_FILE_SERVER = sqlnets.trc
# TRACE_DIRECTORY_SERVER = /ora_dump/network/trace
# Logging Client Activity
# LOG_FILE_CLIENT = sqlnetc.log
# LOG_DIRECTORY_CLIENT = /ora_dump/network/log
# Logging Server Activity
# LOG_FILE_SERVER = sqlnets.log
# LOG_DIRECTORY_SERVER = /ora_dump/network/log

Some parameters are specific to the server and some are specific to the client.

Managing the listener(UNIX):

Make sure you have ORACLE_HOME set, also if the config files are not in ORACLE_HOME/network/admin, set TNS_ADMIN to the directory.
To start the listener use the utility lsnrctl

lsnrctl start

If you have a listener name other than LISTENER then specify that name as

lsnrctl LISTENERNAME start

To stop the listener,

lsnrctl stop

If you have made changes to the listener, but do not want to stop and start, you may do

lsnrctl reload

To see the listener status,

lsnrctl status

You can also specify password for the listener.

Notes:

If you have many clients, it may be a maintenance problem to have tnsnames.ora and sqlnet.ora on each client PC. You can install SQL*Net on the network and everyone can share the same information. For 32 bit SQL*Net, make sure the client PC windows registry points to the network and for 16 bit SQL*Net set up the oracle.ini file with appropriate parameters. The oracle.ini file is mentioned in the win.ini file underORA_CONFIG=Drive:\path\oracle.ini. The parameters you have to look for are ORACLE_HOME, NLS_LANG, TNS_ADMIN, RDBMS73, ORA_NLS32 etc.