oracle

Question: After an ORA-20 "maximum number of processes (%s) exceeded" occurs ... no new connections (even SYSDBA or SYSOPER) may be made to the instance. How to connect Oracle Database in that case? An ORA-20 "maximum number of processes (%s) exceeded" occurs when the number of OS processes for the instance exceeds the PROCESSES parameter WORKAROUNDS UNIX: kill -9 one or more of the client connections. WINDOWS: using ORAKILL ... kill one or more of the client connections. Use … [Read more...] about How to Connect After ORA-20 has Occurred

Dead Connection Detection (DCD) These are previously valid connections with the database but the connection between the client and server processes has terminated abnormally. Examples of a dead connection: - A user reboots/turns-off their machine without logging off or disconnecting from the database. - A network problem prevents communication between the client and the server. In these cases, the shadow process running on the server and the session in the database may not … [Read more...] about Oracle Database : Understanding Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes

Question: What are the roles that can be set as DEFAULT for a user starting with 11.1.0.7 and 10.2.0.4? The DEFAULT clause in the query: sql> alter user [username] default roles [role list]; specifies the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to enable: Roles not granted to … [Read more...] about What roles can be set as default for a user in Oracle Database

This post seeks to give a general overview of the roles and privileges functionality for the Oracle RDBMS. It delineates the advantages and limitations of administering privileges through roles. The effects of granting and revoking roles are analyzed and important authorization and security issues are discussed. Privileges The ability to selectively grant the right to manipulate and access information through privileges is integral to assure the security of the database. Privileges have been … [Read more...] about Roles and Privileges Administration and Restrictions in Oracle Database

Sometimes it may be necessary to recreate user java objects within the database, for example following recreation of the JVM or when copying a users schema. These require user and application Java Classes and Java Resources to be reloaded which is not always a simple task if there are a large number of Java objects. The following method explains how to backup all Java Classes, Resources, and their corresponding Java Grants and Policies, avoiding a full database restoration in some … [Read more...] about How to Backup and Restore Java Classes and Privileges only in Oracle Database

These steps will allow you to change the passwords for SYS, SYSTEM, and the oracle user at the operating system level. 1. Log into Oracle as internal or sysdba $ svrmgrl command="connect internal" - or - $ sqlplus "/ as sysdba" 2. Type the following commands: SQL> ALTER USER SYS IDENTIFIED BY [password]; SQL> ALTER USER SYSTEM IDENTIFIED BY [password]; 3. Log off of Oracle and reconnect as SYS or SYSTEM and test the new password Note that any OS user who is a member of the … [Read more...] about How to Change SYS and SYSTEM Passwords in Oracle Database

Purpose This script reports information about the current user's session. It should not be used when connected as user SYS. This is because audsid for user SYS as well as background processes is always 0 and, therefore, the script will return multiple entries in this case. Configuration Access Privileges Requires SELECT privilege on v$process and v$session or SELECT_CATALOG_ROLE(Oracle8 and higher). Usage Copy the script to a file named tfssgrep.sql and execute it from SQL*Plus … [Read more...] about Oracle SQL Script: To Obtain Session Information

The Problem After creating a new profile or altering an old one to limit the following profile resources there is no change: SESSIONS_PER_USER CPU_PER_SESSION CPU_PER_CALL CONNECT_TIME IDLE_TIME LOGICAL_READS_PER_SESSION COMPOSITE_LIMIT PRIVATE_SGA The resource usage limits are not enforced and the users that are assigned the profile continue to use resources beyond profile's limits. The Solution The initialization parameter RESOURCE_LIMIT is set to FALSE (default). Set … [Read more...] about Oracle Database: Profile Limits (Resource Parameter(s)) Are Not Enforced / Do Not Work