ALTER DATABASE – Managing Redo logs
Before commiting data into database Oracle changes and stores the changed data in redo log files. Every oracle database needs redo log files. When for some reason some datafile becomes unavailable then these redo log files can be used to rollback changes to the data.
The following command can be used to add a new redo log group to an existing database:
ALTER DATABASE testdb
ADD LOGFILE
GROUP 1 (‘/u01/oradata/testdb/redo1a.log’,'/u02/oradata/testdb/redo1b.log’) SIZE 100M;
This command will add a redo log group (Group 1) to the database testdb. The log file group will have two members which are located on two different disks uo1 and uo2.
In Oracle the Group number must be unique. So before creating the group it is vital that you check the existing group number and then increment that number by 1 to get your new group number.
It is possible to add further log members to redo groups.
The command below will add a member to GROUP 1.
ALTER DATABASE testdb
ADD LOGFILE MEMBER
‘/u03/oradata/testdb/redo1c.log’
TO GROUP 1;
Alter system kill session in Oracle
The command you use to kill an Oracle session is
Alter system kill session ‘SID, SERIAL#’;
So why should I need to kill an Oracle session. There are many reasons why you might want to kill a session. To give you a few exmaple:
How to Install Oracle 11g Client
These instructions apply to Microsoft Windows only. When you install Oracle database software (try installing Enterprise edition), by default it also installs Oracle client. So if you have installed Oracle database software in your machine and want to connect to your local database you simply invoke the Oracle Client and given the necessary login credentials and you are in. You do not have to worry much about setting up your sql*net configuration as the installation process should usually take care of that if you chose to do so.
But for people who want to access an Oracle database that is not local and in a remote machine then you must need to install Oracle client in your machine. Even if You do not want to use SQL*Plus but want to use TOAD or SQL Developer or something like that, you still need Oracle client. Because internally these database access/development tools use Oracle clients software to connect to the target database.
So the most important thing to remember is, you must need Oracle client software in your machine if you want to connect to a remote Oracle database.
Installing Oracle client is not difficult. You just need to be a bit careful. The step by step instructions below should help you to install an Oracle Client in your machine. These instructions assume that you have downloaded the Oracle client software that you desire to install and unzipped the zip file into some folder. Continue reading
Re-Compiling ALL INVALID objects in your Oracle database
This script will re-compile all invalid objects of all schemas in yuor database. This is quite powerfull script as it will try to re-compile everything in the database that is invalid. So you must need to run this script as a highly priviledged user (such as SYS or SYSTEM or a user with DBA role)
The script first builds up the ALTER commands for all invalid objects and put (spool) the list into an operating system file called templist.sql. The ALTER commands will be of the format
ALTER PROCEDURE HR.CALLPROC COMPILE;
ALTER PACKAGE SCOTT.PKG_TEST1 COMPILE BODY;
ALTER PACKAGE SCOTT.PKG_TEST1 COMPILE BODY;
Note above that I am using the schema name before the object name e.g. HR.CALLPROC). This is to make sure that we are re-compile the invalid object in its own schema. The list file templist.sql will be run against the database to run all these commands in one go.
Here is the script:
Re-Compiling INVALID objects in own (users) schema
Here is a very handy script which you can use to re-compile all the invalid objects in your schema.
The script first builds up the ALTER commands for all invalid objects and put (spool) the list into an operating system file called templist.sql. The ALTER commands will be of the format
ALTER PROCEDURE CALL COMPILE;
ALTER PACKAGE PKG_TEST1 COMPILE BODY;
ALTER PACKAGE PKG_TEST1 COMPILE BODY;
Then the list file templist.sql will be run against the database to run all this commands in one go. Which means you just need to run this one script to re-compile all your invalid objects in your schema. Note that I am using here USER_OBJECTS – which is a VIEW owned by SYS user. Continue reading
Recent Comments