Category Archives: Oracle database

SET PASSWORD LIFE TIME TO UNLIMITED

Set the password life time to unlimited for DEFAULT profile.
SQL> SELECT resource_name,limit FROM dba_profiles WHERE profile=’DEFAULT’;

RESOURCE_NAME LIMIT
——————————– —————————–
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME 180

RESOURCE_NAME LIMIT
——————————– —————————–
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Thank You !!

 

Find Plan Hash Values from SQLID

 

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/

 

Thank You !!

DATABASE SCHEMA SIZE from the database

set linesize 150
set pagesize 5000
col owner for a155

select OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;

Thank you !!

 

 

 

ORA-39181: Only partial table data may be exported due to fine grain access control

The below error appeared when trying to export the Schema:

ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKFLOW_ACTION_PARAM”
. . exported “GLOGOWNER”.”WORKFLOW_ACTION_PARAM” 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKING_LIST”
. . exported “GLOGOWNER”.”WORKING_LIST” 0 KB 0 rows
ORA-39181: Only partial table data may be exported due to fine grain access control on “GLOGOWNER”.”WORKING_LIST_DETAIL”

SOLUTION:

Provide the below privilege to the schema which you are trying to export.

grant EXEMPT ACCESS POLICY to <SCHEMA NAME>;

 

Thank you !!

ORA-39097 Data Pump job encountered unexpected error

ERROR:
Export: Release 10.2.0.3.0 – Production on Wednesday, 08 August, 2007 10:12:50
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-01403: no data found
ORA-39097: Data Pump job encountered unexpected error 100
Another possible error received after applying a PSU or CPU patch.
ORA-39006: internal error
ORA-39213: Metadata processing is not available

CAUSE/CHANGE:

Applied a CPU or PSU patch.

SOLUTION:

Some DataPump Metadata stored in the METANAMETRANS$ table was missing. You can verify this by selecting from the table which will likely return no rows.

SQL> connect / as sysdba
SQL> select count(*) from metanametrans$;

COUNT(*)
———-
0

While on a database where this table is correctly populated, should return many rows similar to the following run against a 10.2 database:

SQL> select count(*) from metanametrans$;

COUNT(*)
———-
2499
Run the following scripts connected as SYSDBA:
sqlplus / as sysdba
@$ORACLE_HOME/rdbms/admin/catmet2.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

 

Thank You !!

ORA-01940: cannot drop a user that is currently connected

SQL>drop user MY_USER cascade;
drop user username cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL>select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.username = ‘MY_USER’ and p.addr (+) = s.paddr;
SID           SERIAL#       STATUS           SPID
———   ———-    ——–       ————
579               27        INACTIVE        17849
279                9        INACTIVE        89543

SQL> alter system kill session ‘579,29’;
System alterred.

SQL> alter system kill session ‘279,9’;
System alterred.

Now try to drop USER:

SQL>drop user MY_USER cascade;
User dropped.

Thank You !!

ORA-12547 TNS: Lost Contact

Getting the following error when trying to connect using Sql*Plus

ERROR:
$sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 – Production on Wed Mar 30 11:59:06 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12547: TNS:lost contact

CAUSE:

1) to kernel parameters settings
2) Incorrect permissions on the ORACLE executable
3) Insufficient ulimit setting for stack
4) $ORACLE_HOME/rdbms/lib/config.o is 0 bytes
5) Oracle binaries have not been linked correctly

SOLUTION:

1) Please check the notes below that provide the required settings for kernel parameters
Note 169706.1 Oracle Database on AIX,HP-UX,Linux,MacOSX,Solaris,Tru64

2) Please check permissions by running:

$ cd $ORACLE_HOME/bin
$ ls -l oracle
The output should show the correct permission which is:

-rwsr-s–x 1 oracle dba

If not, then please execute the following to correct the permissions:
$ cd $ORACLE_HOME/bin
$ chmod 6751 oracle
$ ls -l oracle

3) Check the current ulimit setting for stack:

ulimit -a

Check the install guide for your specific platform and version of Oracle and set the stack appropriately.

4) Check to ensure the following two files are not 0 bytes:

$ORACLE_HOME/bin/oracle
$ORACLE_HOME/rdbms/lib/config.o

If yes, rename the following file:

$ cd $ORACLE_HOME/rdbms/lib
$ mv config.o config.o.bad

Then, relink the oracle binary:
$ relink oracle

5) Check the alert log for any errors (ORA-00020: maximum number of processes) and solve.

select * from v$resource_limit and check maximum utilization and limit values.

RESOURCE_NAME      MAX_UTILIZATION LIMIT_VALUE
————-           ————————– ————————–
processes      350                350
sessions      380                380

6) If the above does not resolve I suggest that you shutdown the database and listener and then “relink all”

Please refer the ORACLE SUPPORT DOCUMENT ID 422173.1

 

Thank You !!

Disable Case Sensitive in Oracle Database 11g

Prior to 11g Passwords in Oracle are not case sensitive. Now from 11g Oracle passwords are Case sensitive. Parameter sec_case_sensitive_logon has been included in Oracle 11g.

sql> create user test identified by test;
sql> Grant create session to test;
SQL> show parameter logon
NAME                                       TYPE                              VALUE
—————————-     ———————-   ——————————
sec_case_sensitive_logon     boolean                           TRUE

SQL> conn test/TEST
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

SQL> conn test/test
Connected.

Disable Password case sensitive:

SQL> alter system set sec_case_sensitive_logon=false;
System altered.

SQL> conn test/TEST
Connected.

Thank You !!

Find size of Table,Index and USER/SCHEMA in Oracle

How to find size of Table or Index or User/Schema in Oracle Database.

SIZE OF TABLE:

Select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_name=’TABLE_NAME’ group by segment_name;

OR

select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_name=’TABLE_NAME’ group by owner,segment_name;

SIZE OF INDEX:

select segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from user_segments where segment_name=’INDEX_NAME’ group by segment_name;

OR

select owner,segment_name,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ and segment_name=’INDEX_NAME’ group by owner,segment_name

SIZE OF A USER/SCHEMA:

select owner,sum(bytes)/1024/1024/1024 as “SIZE in GB” from dba_segments where owner=’SCHEMA_NAME’ group by owner;

 

 

Thank You !!

Oracle 11gR2 installation on windows machine

Oracle Database Patch Set 11.2.0.2 (Patch#10098816) for Win32 and Win64 is available in Oracle support for downloading.

download

After downloading patchset, Unzip the patch which will creates folder “database”

Run the setup executable with “Run as administrator”

run

Unchecked the box for not to receive any security updates from Oracle Support

skip

skip1

Click on Skip software updates radio button

skip3

Click on instal database software only, As we are installing only software.

install1

single

english

enterprise

location

summary

progress

success

 

Thank You !!