admin

The Problem The SPFILE has previously been manually edited when the instance was opened. $ ls -l $ORACLE_HOME/dbs/spfileRel14.ora -rw-rw---- 1 sme dba 907 Mar 19 09:17 /ora9i/dbs/spfileRel14.ora a. Once you attempt to store new parameter values, you get the following error: SQL> alter system set processes=70 scope=spfile; alter system set processes=70 scope=spfile * ERROR at line 1: ORA-27046: file size is not a multiple of logical block size Additional information: 1 b. … [Read more...] about What Happens and What to Do when the SPFILE has been Manually Modified

Question: If you have to set more than one event in the init.ora, how can you specify multiple events on the same line? The syntax to specify multiple events in the init.ora is: event="[event 1]:[event 2]: [event 3]: [event n]" you can also split the events on multiple lines by using the continuation "\" backslash character at the end of each event and continue the next event on the next line. Such As: event="[event 1]:\ [event 2]:\ [event 3]: \ [event n]" For … [Read more...] about How to Set Multiple Events in INIT.ORA file

Question: We need to change a static parameter value in the SPFILE used at instance startup, but we get the following error message: SQL> alter system set audit_trail=DB scope=both; * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified According to the definition of a static parameter, it cannot be modified during instance life, but only in the init or spfile parameter file. SCOPE=BOTH applies the modification at the instance level and in … [Read more...] about How to change static parameters through SPFILE parameter file in Oracle Database

This post summarizes the syntax used to set trace and debug events when the server uses the binary server-side parameter file (SPFILE). You need to set several events in the parameter file. You can perform this task successfully in the text-based parameter file and need to do the same operation for an instance that uses the new Oracle9i binary server-side parameter file, also known as the SPFILE. 1. An example of the text based parameter file syntax is: event="10325 trace name context … [Read more...] about How to set EVENTS in spfile

Question: How to limit the access to the database so that only one user per schema are connected (one concurrent user per schema) 1. Set in init.ora or spfile.ora the parameter: resource_limit = true and restart Database. You can perform this without restarting Database by issuing as SYS: "alter system set resource_limit=true;" 2. Connect to Database as SYSTEM or SYS and create the following Profile: create profile single_user limit sessions_per_user 1; The other parameters … [Read more...] about How to limit access to oracle database so that only 1 user per schema is connected ( 1 Concurrent user per schema)

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