Error: ORA-1684
Text: max # extents (%s) reached in table %s.%s partition %s
—————————————————————————
Cause: A table tried to extend past maxextents
Action: If maxextents is less than the system maximum, raise it. Otherwise,
you must recreate with larger initial, next or pctincrease params

Session: 1

SQL> select * from dept;
DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON


Lock one row.

SQL> update dept set dname='DEV' where deptno=30;
1 row updated.

Do not commit

Session: 2

Try to lock the same row

SQL> update dept set dname='TESTING' where deptno=30;

Execute this srcipts to gather lock statistic.

SQL> select (select username from v$session where sid=a.sid) BLOCKER,
2 a.sid,
3 'IS BLOCKING',
4 (select username from v$session where sid=b.sid) BLOCKEE,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and b.id2 = b.id2
11 /


BLOCKER                        SID        'ISBLOCKING BLOCKEE                        SID
------------------------------ ---------- ----------- ------------------------------ ----------
SCOTT                          146        IS BLOCKING SCOTT                          151



SQL> col OBJ format a15
SQL> col SS format a15
SQL> set linesize 200


SQL> select owner||'.'||object_name obj
2 ,oracle_username||' ('||s.status||')' oruser
3 ,os_user_name osuser
4 ,l.process unix
5 ,''''||s.sid||','||s.serial#||'''' ss
6 ,r.name rs
7 ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
8 from v$locked_object l
9 ,dba_objects o
10 ,v$session s
11 ,v$transaction t
12 ,v$rollname r
13 where l.object_id = o.object_id
14 and s.sid=l.session_id
15 and s.taddr=t.addr
16 and t.xidusn=r.usn
17 order by osuser, ss, obj
18 /


OBJ             ORUSER            OSUSER                         UNIX         SS RS TIME
--------------- ----------------- ------------------------------ ------------ --------------- ------------------------------ -------------------
SCOTT.DEPT      SCOTT (INACTIVE)  oracle                          17809       '146,188'

_SYSSMU1$ 2009/08/04 06:03:37

here ss means <session_id>,<serial#>

Session: 3

Kill the session to release the lock:

ALTER SYSTEM KILL SESSION ‘<session_id>,<serial#>’;


SQL> alter system kill session '146,188';

System altered.

What the error is telling you is that a connection attempt was made, but the session authentication was not provided before SQLNET.INBOUND_CONNECT_TIMEOUT seconds.

As far as adverse effects in the long run, you have a user or process that is unable to connect to the database. So someone is unhappy about the database/application.
————————————————————————————————
In 10gR2, SQLNET.INBOUND_CONNECT_TIMEOUT the parameters were set to have a default of 60 (seconds).

Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite).
Before setting SQLNET.INBOUND_CONNECT_TIMEOUT, verify that there is not a firewall or Network Address Translation (NAT) between the client and server. Those are common cause for ORA-3136.
————————————————————————————————
The parameter is to be added in sqlnet.ora on the server, so wont require a shutdown.
$ORACLE_HOME/network/admin/sqlnet.ora
and edit it at server side.

Use the following script to find out tablespace name under the mentioned threshold value:

select trunc(s.bytes/f.maxbytes, 2) used_pct, ‘TABLESPACE ‘ || s.tablespace_name || ‘ is ‘ || to_char(trunc(s.bytes/f.maxbytes, 2)*100) || ‘% full’ description from (select tablespace_name, sum(bytes) bytes from dba_segments
group by tablespace_name) s, (select tablespace_name, sum(greatest(nvl(maxbytes, 0), bytes)) maxbytes from dba_data_files group by tablespace_name) f where s.tablespace_name = f.tablespace_name and s.bytes/f.maxbytes >.90;

The threshold value can be changed by changing the last two numeric digits.

Use the following script is to find out the details of a particular tablespace

set lines 999
col file_name format a70;
select file_name,bytes/(1024*1024),maxbytes/(1024*1024),autoextensible from dba_data_files
where tablespace_name=’tablespace-name’
/

1.     Open a terminal window. Login as the root user.

2.      Create the following operating system groups: oinstall, dba, and, oper.

/usr/sbin/groupadd oinstall

/usr/sbin/groupadd dba

/usr/sbin/groupadd oper

3.     Create the operating system user oracle:

/usr/sbin/useradd -g oinstall -G dba,oper -d /home/oracle oracle

4.     Enter the following command to set the password of the oracle user:

/usr/sbin/passwd oracle

5.     With an editor of your choice, edit /home/oracle/.bash_profile to include the following entries:

umask 022

PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin

LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

ORACLE_SID=orcl

$ORACLE_HOME/jdk/jre/lib/i386/server:

$ORACLE_HOME/rdbms/lib:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

PATH=$ORACLE_HOME/bin:$PATH

export PATH LD_LIBRARY_PATH

export ORACLE_BASE ORACLE_HOME ORACLE_SID

6.     Create the directory for the software installation and assign ownership to oracle:oinstall.

mkdir -p /u01/app/oracle

chown -R oracle:oinstall /u01/app

chmod -R 775 /u01/app

7.     Open the /etc/sysctl.conf file in any text editor and add lines similar to the following:

kernel.sem = 250 32000 100 128

kernel.shmall = 2097152

kernel.shmmax = 2147483648

kernel.shmmni = 4096

fs.file-max = 65536

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 262144

net.core.rmem_max = 262144

net.core.wmem_default = 262144

net.core.wmem_max = 262144

8.     Issue the following command to set the kernel parameters:

/sbin/sysctl -p

9.  To see which versions of these packages are installed on your system, run the following command:

rpm -q binutils compat-db control-center gcc gcc-c++ glibc glibc-common \
gnome-libs libstdc++ libstdc++-devel make pdksh sysstat xscreensaver libaio openmotif21

10. To install package:

rpm -Uvh glibc-devel-2.3.4-2.13.i386.rpm

Please Leave your comment…

Datapump

Datapump is a server based bulk data movement infrastructure that supersedes the old import and export utilities. The old export/ import tools are still available, but do not support all Oracle 10g and 11g features. The new utilities are named expdp and impdp. It is ideal for large databases and data warehousing environments, where high-performance data movement offers significant time savings to database administrators.

Create database directories

Execute the following commands to create a database directory. This directory must point to a valid directory on the same server as the database:

SQL> CREATE DIRECTORY dmpdir AS ‘/opt/oracle’;

Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott;

Grant succeeded.

Oracle introduced a default directory from 10g R2, called DATA_PUMP_DIR, that can be used:

SQL> SELECT directory_path FROM dba_directories WHERE directory_name = ‘DATA_PUMP_DIR’;

DIRECTORY_PATH
—————————————————————————–
/app/oracle/product/10.2.0/rdbms/log/

Start using datapump export

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Import into another database

impdp system/oracle DIRECTORY=dmpdir DUMPFILE=scott.dmp

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

The DBA_DATAPUMP_JOBS view can be used to monitor the current jobs:

> select * from dba_datapump_jobs;

To list all command line parameters, type:

expdp help=yes
impdp help=yes

How will you configure the Flash Recovery Area & Archivelog mode of your database through Oracle Enterprise Manager 10g Database Control?

I found the answer in Linda Smith’s blog. She explains the whole thing step by step with snaps. It does prove really helpful to me.

I was working on Windows XP platform. The problem I faced during Specify Host and target Database Credentials is “RemoteOperationException: ERROR: Wrong password for user”.

To solve the above problem follow the steps below:

Go to Control Panel–>Administrative Tools–>Local Security Policy–>Local Policies–>User Rights Assignment–>U will see in the policy as “Log on as a batch Job–>right click–>Properties–>add user or groups–>give your OS username.