There are many third party tools by which you can copy files between windows and unix machine. Some common tools are winscp, SSHSecureShellClient etc. Instead of using any third party tool, you can use original unix and windows built-in tool to do the task.
The RCP (Remote CoPy) is a standard UNIX tool and a Microsoft Windows 2000 tool that copies files/folder to and from computer running the RCP service.
Before you can use the RCP tool from a Windows-based computer you must do something in your UNIX computer.
you must turn on the RCP service and update the security files to allow the Windows-based computer to connect to your unix machine over this service.
The following steps will help you.
Step 01: Turn on RCP service on UNIX Host
Turn on the RCP service on unix machine by using inetd:
1. Log on as root.
2. Edit the file /etc/Inetd.conf.
3. Uncomment the lines that start with shell and that start with exec.
4. Save the file.
5. Use ps -ef |grep inetd and determine inetd service pid. Send the Host User Profile (HUP) signal to inetd by using kill or by using pkill.
6. Run inetd again.
To turn on the RCP service by using xinetd:
1. Log on as root.
2. Edit the file /etc/Xinetd.d/rsh. You may get another file based on your linux distrinution.
3. Change the line disable to no.
4. Save the file.
5. Use ps -ef |grep inetd and determine inetd service pid. Send the HUP signal to xinetd by using kill or by using pkill.
6. Run xinetd again.
Step 02: Set the security permissions
After turning on RCP services you must set the security permissions to allow the Windows host to connect to your computer.
1. Edit the file /etc/hosts.equiv. Based on your linux distribution this file may vary. You can get like /etc/hosts.allow.
2. In the file add a line that contains the name of your Windows host.
3. Add a second line that contains the name of your Windows host and the name of a user who can access the directory that you want to transfer. Separate the two elements with a tab character.
4. Save the file.
Step 03: Transfer the files or directory
From windows computer you can transfer files or folders using rcp using following syntax.
RCP [-a | -b] [-h] [-r] [host][.user:]source [host][.user:] path\destination
For example, to copy the file index.html from the path /home/oracle on the computer unixhost as the user oracle you must use from windows computer,
rcp unixhost.oracle:/home/oracle/index.html index.html
In order to copy whole directory you need to use -r(for recursive) with rcp command.
To copy test directory under /home/oracle on unix, from windows computer issue,
rcp -r unixhost.oracle:/home/oracle/test test
Related Documents
How to change/configure IP Address on Linux/ Fedora /Ubuntu
Screen -A very useful unix tool to work with remote system
Different ways to take Screenshot on Linux
How to change the hostname in Linux
Memory Usage of Solaris Operating System
Saturday, January 31, 2009
h323-disconnect-cause for CISCO of Radius Server
In case of CISCO router, from the Radius server h323-disconnect-cause with code is returned. Like h323-disconnect-cause=4 or h323-disconnect-cause=7. In the following mini dictionary disconnect cause along description corresponding code is shown which will help instantly to identify disconnect issue if we find any code.
Local-Clear 0
Local-No-Accept 1
Local-Decline 2
Remote-Clear 3
Remote-Refuse 4
Remote-No-Answer 5
Remote-Caller-Abort 6
Transport-Error 7
Transport-Connect-Fail 8
Gatekeeper-Clear 9
Fail-No-User 10
Fail-No-Bandwidth 11
No-Common-Capabilities 12
FACILITY-Forward 13
Fail-Security-Check 14
Local-Busy 15
Local-Congestion 16
Remote-Busy 17
Remote-Congestion 18
Remote-Unreachable 19
Remote-No-Endpoint 20
Remote-Off-Line 21
Remote-Temporary-Error 22
Friday, January 30, 2009
ORA-00600 arguments: [keltnfy-ldmInit], [46], [1]
Problem Description
In oracle 10.2.0.1 while creating database with dbca it fails with message below.
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
If you try to create your database manually then also the command startup nomount fails with above error message.
Cause of the Problem
In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires.
Solution of the Problem
Step 01: Check permission on /etc/hosts
Note that you need read permission of all users.
Step 02: Check the contents of /etc/hosts
Open the contents of /etc/hosts and check the contents inside it.
$ less /etc/hosts
Note that the contents of this files follow following format.
A simple example,
Step 03: Check the hostname and make sure you can ping your hostname.
$ hostname
database
$ ping database
PING database (127.0.0.1) 56(84) bytes of data.
64 bytes from database (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from database (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms
64 bytes from database (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms
If you get the following message,
$ ping database
ping: unknown host database
then possibly you will hit above bug.
And you need to modify /etc/hosts files. In the alias section you can give the name of your machine name. If your machine name is "database" you can give /etc/hosts entry as,
And then ping database again. Make sure you are able to ping your host.
Step 04: Diagnosis DNS problem if you have DNS setup
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.
$nslookup www.google.com
The forward and reverse lookup should succeed.
Step 05: Check nsswitch.conf
Make sure host lookup is also done through the /etc/hosts file and not just dns. The keyword files should come before dns.
Step 06: Check resolv.conf
Make sure nameserver with DNS name is added there.
In oracle 10.2.0.1 while creating database with dbca it fails with message below.
ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []
If you try to create your database manually then also the command startup nomount fails with above error message.
Cause of the Problem
In the error ldmInit indicates that the problem is related while getting host information of oracle during startup. The first argument 46 indicates the exception LDMERR_HOST_NOT_FOUND which is "gethostbyname system call fails". Oracle was unable to get host information from OS and bug fires.
Solution of the Problem
Step 01: Check permission on /etc/hosts
$ ls -l /etc/hosts
-rw-r--r-- 1 root root 153 Nov 24 2007 /etc/hosts
Note that you need read permission of all users.
Step 02: Check the contents of /etc/hosts
Open the contents of /etc/hosts and check the contents inside it.
$ less /etc/hosts
Note that the contents of this files follow following format.
IP Address fully qualified hostname simple or short hostname Alias
A simple example,
$ cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 database localhost.localdomain localhost
Step 03: Check the hostname and make sure you can ping your hostname.
$ hostname
database
$ ping database
PING database (127.0.0.1) 56(84) bytes of data.
64 bytes from database (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from database (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms
64 bytes from database (127.0.0.1): icmp_seq=2 ttl=64 time=0.041 ms
If you get the following message,
$ ping database
ping: unknown host database
then possibly you will hit above bug.
And you need to modify /etc/hosts files. In the alias section you can give the name of your machine name. If your machine name is "database" you can give /etc/hosts entry as,
127.0.0.1 database localhost.localdomain localhost
And then ping database again. Make sure you are able to ping your host.
Step 04: Diagnosis DNS problem if you have DNS setup
If you have DNS setup, ping is not a tool to diagnose DNS problem. A better tool to use is nslookup, dnsquery, or dig.
$nslookup www.google.com
The forward and reverse lookup should succeed.
Step 05: Check nsswitch.conf
$ cat /etc/nsswitch.conf hosts: files dns
Make sure host lookup is also done through the /etc/hosts file and not just dns. The keyword files should come before dns.
Step 06: Check resolv.conf
$ cat /etc/resolv.conf nameserver 4.2.2.2
Make sure nameserver with DNS name is added there.
Related Documents
Thursday, January 29, 2009
Capitalize every words using JavaScript
Save the below text in a text file and save it as html and then open it with any browser where javascript is enabled.
<html>
<head>
<script language="JavaScript" type="text/javascript">
function ConvertToLetterCase(frmObj) {
var i;
var tmpStr;
var tmpChar;
var preString;
var postString;
var strlen;
tmpStr = frmObj.value.toLowerCase();
strLen = tmpStr.length;
if (strLen > 0) {
for (i = 0; i < strLen; i++) {
if (i == 0) {
tmpChar = tmpStr.substring(0,1).toUpperCase();
postString = tmpStr.substring(1,strLen);
tmpStr = tmpChar + postString;
}
else {
tmpChar = tmpStr.substring(i, i+1);
if (tmpChar == " " && i < (strLen-1)) {
tmpChar = tmpStr.substring(i+1, i+2).toUpperCase();
preString = tmpStr.substring(0, i+1);
postString = tmpStr.substring(i+2,strLen);
tmpStr = preString + tmpChar + postString;
}
}
}
}
frmObj.value = tmpStr;
}
</script>
</head>
<body>
<center>
Convert First Letter In Each Word To Capitalize Using Javascript<br/>
<form name="caseConvert">
<textarea name="txt_content" cols="40" rows="5">any LeTTer that you WILL wRiTe heRe will BE CapitaLiZed.
</textarea><br/><br/>
<input type=button value="Initialize Capitalized"onClick="javascript: ConvertToLetterCase(this.form.txt_content);">
</form>
</center>
</body>
</html>
ORA-31655: no data or metadata objects selected for job
Problem Description
You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp.
In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc)
Your parameter file is like below.
And you invoke expdp as
expdp parfile=d:\parfile.txt
from command line. But it fails with below message on my windows PC.
As you expected 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION' will be exported but not a single table is exported and error ORA-31655: no data or metadata objects selected for job returned.
Cause of the Problem
This problem happens because of multiple INCLUDE options was set in expdp. Note that multiple INCLUDE parameter can be given in expdp but I recommend not to do that because if multiple filters(EXCLUDE/INCLUDE) are specified , an implicit AND operation is applied to them.
In this example data pump interprets
as,
TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" AND TABLE:"LIKE 'CV%'"
As AND operation is applied, so all tables is filtered out (because no tables is there that starts with CV and between 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP') and no tables are exported.
A bit clear example I will show you.
Your parameter file is like below.
And you invoke datapump
Note that here only one table is exported.
Because with first INCLUDE parameter,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
first 3 tables are selected
and with second INCLUDE parameter
include =TABLE:"LIKE 'ACC%' "
both output are ANDed and only one table is selected that is ACCOUNT_GROUP (which starts with word ACC.)
Solution of the Problem
It is recommended not to use multiple INCLUDE or EXCLUDE parameter in data pump jobs and read http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html If you are in above scenario then only add one INCLUDE parameter and add all the tables within IN clause like below,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION')"
There is no valid reason to use multiple INCLUDE or EXCLUDE parameter in your data pump operation. With only one INCLUDE or EXCLUDE parameter you can do your job.
You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp.
In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc)
Your parameter file is like below.
userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "
And you invoke expdp as
expdp parfile=d:\parfile.txt
from command line. But it fails with below message on my windows PC.
C:\>expdp parfile=d:\parfile.txt
Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01": parfile=d:\parfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 14:53:50
As you expected 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION' will be exported but not a single table is exported and error ORA-31655: no data or metadata objects selected for job returned.
Cause of the Problem
This problem happens because of multiple INCLUDE options was set in expdp. Note that multiple INCLUDE parameter can be given in expdp but I recommend not to do that because if multiple filters(EXCLUDE/INCLUDE) are specified , an implicit AND operation is applied to them.
In this example data pump interprets
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "
as,
TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" AND TABLE:"LIKE 'CV%'"
As AND operation is applied, so all tables is filtered out (because no tables is there that starts with CV and between 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP') and no tables are exported.
A bit clear example I will show you.
Your parameter file is like below.
userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "
And you invoke datapump
C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:54 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining options Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01": parfile=d:\parfile.txt Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "SMILEBD"."ACCOUNT_GROUP" 11.75 KB 132 rows Master table "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SMILEBD.SYS_EXPORT_SCHEMA_01 is: G:\B.DMP Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:54:38
Note that here only one table is exported.
Because with first INCLUDE parameter,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
first 3 tables are selected
and with second INCLUDE parameter
include =TABLE:"LIKE 'ACC%' "
both output are ANDed and only one table is selected that is ACCOUNT_GROUP (which starts with word ACC.)
Solution of the Problem
It is recommended not to use multiple INCLUDE or EXCLUDE parameter in data pump jobs and read http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html If you are in above scenario then only add one INCLUDE parameter and add all the tables within IN clause like below,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION')"
There is no valid reason to use multiple INCLUDE or EXCLUDE parameter in your data pump operation. With only one INCLUDE or EXCLUDE parameter you can do your job.
Monday, January 26, 2009
Listener Hangs, Child listener process remains persistence
Problem Description
Some days ago in our database server we got a problem regarding listener issue. Our TNS Listener hangs. Below is the problem symtompts.
•The lsnrctl status or lsnrctl stop or lsnrctl reload does not respond. Just like it hangs after displays message connecting to ..... .
•No one from outside can connect to database.
•Local connection without listener was ok.
•Listener process takes high cpu than normal usage.
•Listener process forks. The word fork is an UNIX OS related term and it indicates listener process creates a copy of itself. The copied process is called child process and the original process is called a parent process. Due to load of the listener a child listener process is created and it remains persistent. Whenever we give ps -ef then two tnslsnr is shown as below.
$ ps -ef | grep tnslsnr
oracle 3102 1 0 Jan 01 ? 12:28 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
oracle 5012 3102 0 Jan 25 ? 10:15 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
From the output first one is parent listener process and second line is child listener process. For child listener process parent id is 3102.
Just killing the child process allows new connections to work until the problem reoccurs. So after seeing above and if listener hangs then do,
$kill -9 5012 3102
Cause of the Problem
This problem remains in oracle 10.1.0.3, 10.1.0.4, 10.1.0.4.2, 10.1.0.5, 10.2.0.1 and 10.2.0.2. The listener hangs if the child listener process is not closed i.e after creating child process it persists. Note that, child listener processes are not unusual, depending on traffic as well as when the OS grep snapshot is taken. However, a persistent secondary process (longer than 5 seconds) is not normal and may be a result of this referenced problem.
This listener hanging event can happen on a standalone server or on a RAC server.
Solution of the Problem
1)The issue is fixed in patchset 10.2.0.3 and in 10.2.0.4. So apply patchset.
2)Apply Patch 4518443 which is available in metalink. Download from metalink and apply on your databse server.
3)As a workaround, you can follow the following two steps if you don't like to apply patch now.
Step 01: Add the following entry in your listener.ora file.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
Step 02: Go to directory cd $ORACLE_HOME/opmn/conf , find ons.config and move it to another location. Like,
cd $ORACLE_HOME/opmn/conf
mv ons.config ons.config.bak
After completing above two steps bounce the listener.
lsnrctl stop
lsnrctl start
Alternatively, you can simply issue,
$lsnrctl reload
if database availability is important.
Note that adding the SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name} to listener.ora file on RAC and disabling the ONS file, will mean that FAN (fast application notification) will not be possible. So, if you have a RAC configuration, then apply the patch and do not disable ONS or FAN.
Related Documents
Some days ago in our database server we got a problem regarding listener issue. Our TNS Listener hangs. Below is the problem symtompts.
•The lsnrctl status or lsnrctl stop or lsnrctl reload does not respond. Just like it hangs after displays message connecting to ..... .
•No one from outside can connect to database.
•Local connection without listener was ok.
•Listener process takes high cpu than normal usage.
•Listener process forks. The word fork is an UNIX OS related term and it indicates listener process creates a copy of itself. The copied process is called child process and the original process is called a parent process. Due to load of the listener a child listener process is created and it remains persistent. Whenever we give ps -ef then two tnslsnr is shown as below.
$ ps -ef | grep tnslsnr
oracle 3102 1 0 Jan 01 ? 12:28 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
oracle 5012 3102 0 Jan 25 ? 10:15 /var/opt/oracle/bin/tnslsnr LISTENER -inherit
From the output first one is parent listener process and second line is child listener process. For child listener process parent id is 3102.
Just killing the child process allows new connections to work until the problem reoccurs. So after seeing above and if listener hangs then do,
$kill -9 5012 3102
Cause of the Problem
This problem remains in oracle 10.1.0.3, 10.1.0.4, 10.1.0.4.2, 10.1.0.5, 10.2.0.1 and 10.2.0.2. The listener hangs if the child listener process is not closed i.e after creating child process it persists. Note that, child listener processes are not unusual, depending on traffic as well as when the OS grep snapshot is taken. However, a persistent secondary process (longer than 5 seconds) is not normal and may be a result of this referenced problem.
This listener hanging event can happen on a standalone server or on a RAC server.
Solution of the Problem
1)The issue is fixed in patchset 10.2.0.3 and in 10.2.0.4. So apply patchset.
2)Apply Patch 4518443 which is available in metalink. Download from metalink and apply on your databse server.
3)As a workaround, you can follow the following two steps if you don't like to apply patch now.
Step 01: Add the following entry in your listener.ora file.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
Step 02: Go to directory cd $ORACLE_HOME/opmn/conf , find ons.config and move it to another location. Like,
cd $ORACLE_HOME/opmn/conf
mv ons.config ons.config.bak
After completing above two steps bounce the listener.
lsnrctl stop
lsnrctl start
Alternatively, you can simply issue,
$lsnrctl reload
if database availability is important.
Note that adding the SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name} to listener.ora file on RAC and disabling the ONS file, will mean that FAN (fast application notification) will not be possible. So, if you have a RAC configuration, then apply the patch and do not disable ONS or FAN.
Related Documents
In 10g listener log WARNING: Subscription for node down event still pending
Symptoms of the Problem
In the listener log file you constantly get the following warning message.
WARNING: Subscription for node down event still pending
If you have oracle database greater than 10g or newer version or 11g then in the listener log file you get the warning message.
Cause of the Problem
The warning messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). This subscription to ONS is introduced in Oracle 10g for RAC environment. Listener subscription to ONS is useful to use advanced features like Fast Application Notification events(FAN) , Fast Application Fail over (FAF) and Fast Connection Failover (FCN) in RAC. So in a non-RAC environment subscription to ONS is not needed. So in a standalone system we can disable it and thus avoid warning message.
Solution of the Problem
Disable subscription for listener to ONS. This can be done by setting the following parameter in the listener.ora.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
After that you need to stop and start the listener by,
lsnrctl stop
lsnrctl start
Alternatively you can reload the listener if availability is important to you. Do it just by,
lsnrctl reload
This will prevent the messages from being written to the log file.
This changes also prevent the TNS listener hanging intermittently which will be discussed in another topic.
Note that, setting the above parameter OFF in listener.ora disables a necessary RAC functionality.
In the listener log file you constantly get the following warning message.
WARNING: Subscription for node down event still pending
If you have oracle database greater than 10g or newer version or 11g then in the listener log file you get the warning message.
Cause of the Problem
The warning messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). This subscription to ONS is introduced in Oracle 10g for RAC environment. Listener subscription to ONS is useful to use advanced features like Fast Application Notification events(FAN) , Fast Application Fail over (FAF) and Fast Connection Failover (FCN) in RAC. So in a non-RAC environment subscription to ONS is not needed. So in a standalone system we can disable it and thus avoid warning message.
Solution of the Problem
Disable subscription for listener to ONS. This can be done by setting the following parameter in the listener.ora.
SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF
Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.
Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
After that you need to stop and start the listener by,
lsnrctl stop
lsnrctl start
Alternatively you can reload the listener if availability is important to you. Do it just by,
lsnrctl reload
This will prevent the messages from being written to the log file.
This changes also prevent the TNS listener hanging intermittently which will be discussed in another topic.
Note that, setting the above parameter OFF in listener.ora disables a necessary RAC functionality.
Sunday, January 25, 2009
ORA-12557: TNS:protocol adapter not loadable
Problem Description
In my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example,
With 10.2g sqlplus I can connect to 10g database.
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable.
C:\>d:\app\oracle\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Cause of the Problem
The problem happens because of two ORACLE_HOME are installed on your system. As after 10g you hav e installed 11g so whenever you write sqlplus by default new 11g binaries are selected and raises ORA-12557. But working with old home 10g works fine.
Simply sqlplus does not work but 10g home location sqlplus (e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe ) works.
C:\>sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 22:47:08 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Connecting to old oracle database using new binaries are not supported in oracle and error will return.
Solution of the Problem
Only setting ORACLE_HOME is not sufficient on windows environment. Because the location is taken from windows registry. So either uninstall newer oracle home or explicitly pointing to old oracle binaries will solve the problem.
Here using pointing to old home,
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Alternatively you can follow below.
Step 01: Remove registry entries of new ORACLE_HOME.
To do this,
i)Type regedit on Run.
ii)Press enter and expand HKEY_LOCAL_MACHINE.
iii)Then expand SOFTWARE and then expand ORACLE tab. There you will see two oracle home. Right click on the one that you want to delete and then select delete. If prompting click yes.
Step 02: Remove any environmental variable.
i)Right click on My computer icon. Then select properties.
ii)System properties window will appear. Click on Advanced tab.
iii)Select environmental variables.
iv)Find the variable/system variable path and ORACLE_HOME. Edit or modify them so that it point to you desired sql*plus.
Usually in the PATH system variable you will get both ORACLE_HOME path. Just remove one path. Of course if you have ORACLE_HOME variable settings first delete the key.
In my machine I had oracle 10g home , using sqlplus of 10g I could connect to an Oracle database 10g. Now I have installed a new oracle 11g home, but using sqlplus of 11g I could not connect to Oracle database 10g. Below is an example,
With 10.2g sqlplus I can connect to 10g database.
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
With 11g sqlplus I could not connect to oracle database 10g. It returns error message, ORA-12557: TNS:protocol adapter not loadable.
C:\>d:\app\oracle\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 01:55:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Cause of the Problem
The problem happens because of two ORACLE_HOME are installed on your system. As after 10g you hav e installed 11g so whenever you write sqlplus by default new 11g binaries are selected and raises ORA-12557. But working with old home 10g works fine.
Simply sqlplus does not work but 10g home location sqlplus (e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe ) works.
C:\>sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 26 22:47:08 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
ERROR:
ORA-12557: TNS:protocol adapter not loadable
Connecting to old oracle database using new binaries are not supported in oracle and error will return.
Solution of the Problem
Only setting ORACLE_HOME is not sufficient on windows environment. Because the location is taken from windows registry. So either uninstall newer oracle home or explicitly pointing to old oracle binaries will solve the problem.
Here using pointing to old home,
C:\>e:\oracle\product\10.2.0\db_1\bin\BIN\sqlplus.exe maximsg/a@192.168.100.160/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 26 01:54:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Alternatively you can follow below.
Step 01: Remove registry entries of new ORACLE_HOME.
To do this,
i)Type regedit on Run.
ii)Press enter and expand HKEY_LOCAL_MACHINE.
iii)Then expand SOFTWARE and then expand ORACLE tab. There you will see two oracle home. Right click on the one that you want to delete and then select delete. If prompting click yes.
Step 02: Remove any environmental variable.
i)Right click on My computer icon. Then select properties.
ii)System properties window will appear. Click on Advanced tab.
iii)Select environmental variables.
iv)Find the variable/system variable path and ORACLE_HOME. Edit or modify them so that it point to you desired sql*plus.
Usually in the PATH system variable you will get both ORACLE_HOME path. Just remove one path. Of course if you have ORACLE_HOME variable settings first delete the key.
Toad displays error 'IN' is not a valid integer value
Problem Description
You are using TOAD version that is less than 8.6.1 and is connecting to Oracle database release 2(10.2.0.2 or higher) , then while looking source of the stored procedure toad displays error message
TOAD ERROR:
'IN' is not a valid integer value
in a pop-up window.
This problem remains if you try to connect to oracle database 11g with toad version less than 8.6.1.
Cause of the Problem
The problem happens because Oracle made a change to the ALL_ARGUMENTS view at release 10.2.0.2 and this 'broke' TOAD. Until 10.2.0.1 there is no problem. But starting with 10.2.0.2 oracle has added a new column named SUBPROGRAM_ID in the ALL_ARGUMENTS view and this causes toad unusable.
Have a look at ALL_ARGUMENTS view between two releases.
In 10.2.0.1,
In 11g,
Solution of the Problem
1.With toad version less than 8.6.1 don't connect to oracle version 10.2.0.2 or higher. Not a actual solution.
2.Upgrade to toad version. On version 9.5.0 it works fine.
3.Alter the ALL_ARGUMENTS view and move the SUBPROGRAM_ID column to the end on the database. This is not supported according to oracle. However on your test database it is safer to do it. But it is not recommended to do it on your production database though no side effect I ever get.
In your 11.1g database connect as "sys as sysdba" and create the view as below.
After creating now try to connect to oracle database with your existing toad and problem should go away.
You are using TOAD version that is less than 8.6.1 and is connecting to Oracle database release 2(10.2.0.2 or higher) , then while looking source of the stored procedure toad displays error message
TOAD ERROR:
'IN' is not a valid integer value
in a pop-up window.
This problem remains if you try to connect to oracle database 11g with toad version less than 8.6.1.
Cause of the Problem
The problem happens because Oracle made a change to the ALL_ARGUMENTS view at release 10.2.0.2 and this 'broke' TOAD. Until 10.2.0.1 there is no problem. But starting with 10.2.0.2 oracle has added a new column named SUBPROGRAM_ID in the ALL_ARGUMENTS view and this causes toad unusable.
Have a look at ALL_ARGUMENTS view between two releases.
In 10.2.0.1,
SQL> desc all_arguments;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OVERLOAD VARCHAR2(40)
ARGUMENT_NAME VARCHAR2(30)
POSITION NOT NULL NUMBER
SEQUENCE NOT NULL NUMBER
DATA_LEVEL NOT NULL NUMBER
DATA_TYPE VARCHAR2(30)
DEFAULT_VALUE LONG
DEFAULT_LENGTH NUMBER
IN_OUT VARCHAR2(9)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
RADIX NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
TYPE_OWNER VARCHAR2(30)
TYPE_NAME VARCHAR2(30)
TYPE_SUBNAME VARCHAR2(30)
TYPE_LINK VARCHAR2(128)
PLS_TYPE VARCHAR2(30)
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
In 11g,
SQL> desc all_arguments
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
PACKAGE_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
OVERLOAD VARCHAR2(40)
SUBPROGRAM_ID NUMBER
ARGUMENT_NAME VARCHAR2(30)
POSITION NOT NULL NUMBER
SEQUENCE NOT NULL NUMBER
DATA_LEVEL NOT NULL NUMBER
DATA_TYPE VARCHAR2(30)
DEFAULTED VARCHAR2(1)
DEFAULT_VALUE LONG
DEFAULT_LENGTH NUMBER
IN_OUT VARCHAR2(9)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
RADIX NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
TYPE_OWNER VARCHAR2(30)
TYPE_NAME VARCHAR2(30)
TYPE_SUBNAME VARCHAR2(30)
TYPE_LINK VARCHAR2(128)
PLS_TYPE VARCHAR2(30)
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
Solution of the Problem
1.With toad version less than 8.6.1 don't connect to oracle version 10.2.0.2 or higher. Not a actual solution.
2.Upgrade to toad version. On version 9.5.0 it works fine.
3.Alter the ALL_ARGUMENTS view and move the SUBPROGRAM_ID column to the end on the database. This is not supported according to oracle. However on your test database it is safer to do it. But it is not recommended to do it on your production database though no side effect I ever get.
In your 11.1g database connect as "sys as sysdba" and create the view as below.
CREATE OR REPLACE FORCE VIEW "SYS"."ALL_ARGUMENTS" ("OWNER", "OBJECT_NAME",
"PACKAGE_NAME", "OBJECT_ID", "OVERLOAD", "ARGUMENT_NAME", "POSITION", "SEQUENCE",
"DATA_LEVEL", "DATA_TYPE", "DEFAULT_VALUE", "DEFAULT_LENGTH", "IN_OUT",
"DATA_LENGTH", "DATA_PRECISION", "DATA_SCALE", "RADIX", "CHARACTER_SET_NAME",
"TYPE_OWNER", "TYPE_NAME", "TYPE_SUBNAME", "TYPE_LINK", "PLS_TYPE", "CHAR_LENGTH",
"CHAR_USED") AS
select
u.name, /* OWNER */
nvl(a.procedure$,o.name), /* OBJECT_NAME */
decode(a.procedure$,null,null, o.name), /* PACKAGE_NAME */
o.obj#, /* OBJECT_ID */
decode(a.overload#,0,null,a.overload#), /* OVERLOAD */
a.argument, /* ARGUMENT_NAME */
a.position#, /* POSITION */
a.sequence#, /* SEQUENCE */
a.level#, /* DATA_LEVEL */
decode(a.type#, /* DATA_TYPE */
0, null,
1, decode(a.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(a.scale, -127, 'FLOAT', 'NUMBER'),
3, 'NATIVE INTEGER',
8, 'LONG',
9, decode(a.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
11, 'ROWID',
12, 'DATE',
23, 'RAW',
24, 'LONG RAW',
29, 'BINARY_INTEGER',
69, 'ROWID',
96, decode(a.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
102, 'REF CURSOR',
104, 'UROWID',
105, 'MLSLABEL',
106, 'MLSLABEL',
110, 'REF',
111, 'REF',
112, decode(a.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'OBJECT',
122, 'TABLE',
123, 'VARRAY',
178, 'TIME',
179, 'TIME WITH TIME ZONE',
180, 'TIMESTAMP',
181, 'TIMESTAMP WITH TIME ZONE',
231, 'TIMESTAMP WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR TO MONTH',
183, 'INTERVAL DAY TO SECOND',
250, 'PL/SQL RECORD',
251, 'PL/SQL TABLE',
252, 'PL/SQL BOOLEAN',
'UNDEFINED'),
default$, /* DEFAULT_VALUE */
deflength, /* DEFAULT_LENGTH */
decode(in_out,null,'IN',1,'OUT',2,'IN/OUT','Undefined'), /* IN_OUT */
length, /* DATA_LENGTH */
precision#, /* DATA_PRECISION */
decode(a.type#, 2, scale, 1, null, 96, null, scale), /* DATA_SCALE */
radix, /* RADIX */
decode(a.charsetform, 1, 'CHAR_CS', /* CHARACTER_SET_NAME */
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(a.charsetid),
4, 'ARG:'||a.charsetid),
a.type_owner, /* TYPE_OWNER */
a.type_name, /* TYPE_NAME */
a.type_subname, /* TYPE_SUBNAME */
a.type_linkname, /* TYPE_LINK */
a.pls_type, /* PLS_TYPE */
decode(a.type#, 1, a.scale, 96, a.scale, 0), /* CHAR_LENGTH */
decode(a.type#,
1, decode(bitand(a.properties, 128), 128, 'C', 'B'),
96, decode(bitand(a.properties, 128), 128, 'C', 'B'), 0) /* CHAR_USED */
from obj$ o,argument$ a,user$ u
where o.obj# = a.obj#
and o.owner# = u.user#
and (owner# = userenv('SCHEMAID')
or exists
(select null from v$enabledprivs where priv_number in (-144,-141))
or o.obj# in (select obj# from sys.objauth$ where grantee# in
(select kzsrorol from x$kzsro) and privilege# = 12));
View created.
After creating now try to connect to oracle database with your existing toad and problem should go away.
Changing a DBA user to a normal user in oracle
Many times you have granted a user DBA super role instead of giving individual privilege to a user. Later whenever you want to revoke DBA role you need to care of which privilege you need to give the user.
Before example let's take a overview about some views related to privileges and roles in oracle.
1)DBA_SYS_PRIVS describes system privileges granted to users and roles.
2)USER_SYS_PRIVS describes system privileges granted to the current user.
3)DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
4)DBA_TAB_PRIVS describes all object grants in the database. Note that in the table the column TABLE_NAME does not display only table rather it displays any object, including tables, packages, indexes, sequences, and so on.
In this example, we want to change a DBA user named "OMS" to a normal user.
Let's see the user OMS has the available roles granted.
These roles may contain many privilege. For example the role RESOURCE contains following privileges.
Let's see the privilege assigned to user OMS.
Now let's see which tablespaces contain the objects owned by the user OMS. We need to assign quota on those tablespaces and then revoking DBA role.
The tablespaces contain objects of user OMS.
Let's see if any objects privileges granted to user OMS.
SQL> select * from dba_tab_privs where grantee='OMS';
no rows selected
Now we give privilege and assign quota to user OMS and then revoking DBA role.
Assigning privilege by,
GRANT CREATE SESSION, CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE TO OMS;
Giving quota on the tablespaces by,
ALTER USER OMS QUOTA UNLIMITED on OMS_SPC;
ALTER USER OMS QUOTA UNLIMITED on OMS_INDX_SPC;
Now revoking DBA role by,
REVOKE DBA FROM OMS;
Before example let's take a overview about some views related to privileges and roles in oracle.
1)DBA_SYS_PRIVS describes system privileges granted to users and roles.
SQL> desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
2)USER_SYS_PRIVS describes system privileges granted to the current user.
SQL> desc user_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
3)DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
SQL> desc dba_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
4)DBA_TAB_PRIVS describes all object grants in the database. Note that in the table the column TABLE_NAME does not display only table rather it displays any object, including tables, packages, indexes, sequences, and so on.
SQL> desc dba_tab_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
In this example, we want to change a DBA user named "OMS" to a normal user.
Let's see the user OMS has the available roles granted.
SQL> select * from dba_role_privs where grantee='OMS';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
OMS RESOURCE NO YES
OMS JAVAUSERPRIV NO YES
OMS DBA NO YES
These roles may contain many privilege. For example the role RESOURCE contains following privileges.
SQL> select * from dba_sys_privs where grantee='RESOURCE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO
8 rows selected.
Let's see the privilege assigned to user OMS.
SQL> select * from dba_sys_privs where grantee='OMS';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
OMS UNLIMITED TABLESPACE NO
Now let's see which tablespaces contain the objects owned by the user OMS. We need to assign quota on those tablespaces and then revoking DBA role.
The tablespaces contain objects of user OMS.
SQL> DEFINE owner='OMS'
SQL> select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
from dba_tables where owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_indexes
where owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_tab_partitions
where table_owner='&OWNER'
UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name
||';' from dba_ind_partitions
where index_owner='&OWNER';
old 1: select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';'
new 1: select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';'
old 2: from dba_tables where owner='&OWNER'
new 2: from dba_tables where owner='OMS'
old 3: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_indexes
new 3: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_indexes
old 4: where owner='&OWNER'
new 4: where owner='OMS'
old 5: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
new 5: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_tab_partitions
old 6: where table_owner='&OWNER'
new 6: where table_owner='OMS'
old 7: UNION select distinct 'Alter user &OWNER quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
new 7: UNION select distinct 'Alter user OMS quota unlimited on '|| tablespace_name ||';' from dba_ind_partitions
old 8: where index_owner='&OWNER'
new 8: where index_owner='OMS'
'ALTERUSEROMSQUOTAUNLIMITEDON'||TABLESPACE_NAME||';'
-----------------------------------------------------------------
Alter user OMS quota unlimited on OMS_INDX_SPC;
Alter user OMS quota unlimited on OMS_SPC;
Let's see if any objects privileges granted to user OMS.
SQL> select * from dba_tab_privs where grantee='OMS';
no rows selected
Now we give privilege and assign quota to user OMS and then revoking DBA role.
Assigning privilege by,
GRANT CREATE SESSION, CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE,
CREATE CLUSTER, CREATE OPERATOR, CREATE INDEXTYPE, CREATE TABLE TO OMS;
Giving quota on the tablespaces by,
ALTER USER OMS QUOTA UNLIMITED on OMS_SPC;
ALTER USER OMS QUOTA UNLIMITED on OMS_INDX_SPC;
Now revoking DBA role by,
REVOKE DBA FROM OMS;
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML