Author Archives: SandeepSingh DBA

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

EXPDP & IMPDP use parallel option for faster in Oracle

EXPDP & IMPDP use parallel option for faster in Oracle

Dumpfile parameter you need to specify the name with %U for generating the dumpfile with sequence number 01 to 99.

expdp scott/tiger@orcl schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=expdpSCOTT.log

impdp scott/tiger@orcl schemas=SCOTT directory=TEST_DIR parallel=4 dumpfile=SCOTT_%U.dmp logfile=impdpSCOTT.log

From Oracle 12.2 additional thing is used
%U is specify from 01 to 99. It is used as old version it is supported.
%L,%l is used from 01-99 also extend up to 2147483646.

Substitute parameter also included

%d, %D : The current day of the month in DD format.
%m, %M : The current month in MM format.
%y, %Y : The current year in YYYY format.
%t, %T : The current date in YYYYMMDD format.

If you used this substitute value in older version then you get the following error:
ORA-39124: dump file name “SCOTT_%T.dmp” contains an invalid substitution variable

Example:

expdp scott/tiger@xe schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT_%T.dmp logfile=expdpSCOTT.log
Export: Release 11.2.0.2.0 - Production on Sat Dec 8 15:46:29 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39124: dump file name "SCOTT_%T.dmp" contains an invalid substitution variable

Advertisements

Find SQL ID and Hash value from SQL Statement in Oracle

Find SQL ID and Hash value from SQL Statement


SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

Example:

Query: Select * from hr.emp where employee_id = 10;

Find SQL ID for the query as
SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text
FROM v$sql
WHERE sql_text like 'SELECT * from hr.emp%'

If SQL is not available then found from snapshot id.

select t.sql_id,
t.sql_text,
s.executions_total,
s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where s.snap_id between 333 and 350 and t.sql_text like '%&An_Identifiable_String%';

Find SQL from history table if not able to find in V$SQL or V$SQLAREA view.

select * from dba_hist_sqltext where sql_text like '%some_text_from_query%';

Method 2(without executing SQL Query)

1. Fetch explain plan as below

-- USE EXPLAIN PLAN STATEMENT
EXPLAIN PLAN FOR
SELECT * FROM HR.EMP WHERE EMPLOYEE_NO - 100;

@?\RDBMS\ADMIN\utlxpls.sql;

--USE OF SET AUTOTRACE ON EXPLAIN STATEMENT
SET AUTOTRACE ON EXPLAIN
SELECT * FROM HR.EMP WHERE EMPLOYEE_NO - 100;

2. Find plan hash id in first line of SQL Plan you got from above methods.

PLAN_TABLE_OUTPUT
-------------------------------------
Plan hash value: 1578910895

3. Find the SQL_ID from V$SQLAREA by using plan_hash_value.

SELECT SQL_ID,SQL_TEXT FROM V$SQLAREA WHERE PLAN_HASH_VALUE='1578910895' AND SQL_TEXT LIKE '%SELECT * FROM HR.EMP%';

CONNECT_TIME and IDLE_TIME in Oracle profile

CONNECT_TIME and IDLE_TIME in Oracle profile

CONNECT_TIME: Parameter will disconnect a session whose connect time exceed the value for connect_time.The connect_time is expressed in minutes.

IDLE_TIME: Permitted periods of continuous inactive time during a session(minutes).
We specify 10 minutes as the parameter value, the user session expires after 11 minutes or 12 minutes.
PMON background process periodically checks the user sessions for idle time out.

DBA_PROFILES view provides information on all the profiles and the resource limits for each profile.
Each user can find information on his resources and limits in the USER_RESOURCE_LIMITS view.

Check the idle time and connect time value for a profile

SELECT * FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME IN ('IDLE_TIME','CONNECT_TIME');

Alter value of idle time

ALTER PROFILE DEFAULT LIMIT IDLE_TIME UNLIMITED;

Alter value of connect time

ALTER PROFILE DEFAULT LIMIT CONNECT_TIME UNLIMITED;

For using resource limit parameter should be true

SHOW PARAMETER RESOURCE_LIMIT;

--for alter resource limit parameter
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE SCOPE=BOTH;

Default is true.

Managed Password parameter in profile for User in Oracle

Managed Password parameter in profile for User in Oracle

In user profile manage the password parameter include the following parameters:

Password_parameters

[FAILED_LOGIN_ATTEMPTS      expr|UNLIMITED|DEFAULT]
[PASSWORD_LIFE_TIME         expr|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_TIME        expr|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_MAX         expr|UNLIMITED|DEFAULT]
[PASSWORD_LOCK_TIME         expr|UNLIMITED|DEFAULT]
[PASSWORD_GRACE_TIME        expr|UNLIMITED|DEFAULT]
[PASSWORD_VERIFY_FUNCTION   function_name|NULL|DEFAULT]

Check the user belong to which profile

select profile from dba_users where username = 'TEST';

Profile
------------
DEFAULT

Check resource_name and limit defined for the profile.
It include both resource parameter and password parameter. We are working on password parameter in this blog.

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 UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED

Meaning of password resources
FAILED_LOGIN_ATTEMPTS : Attempts made for login exceed this limit will lock the account.
PASSWORD_LIFE_TIME : Number of days password can be used for authentication. It also depend upon PASSWORD_GRACE_TIME is set then password will work until grace period defined after it will not allow to connect. It will give warning only if PASSWORD_GRACE_TIME is unlimited value.
PASSWORD_GRACE_TIME : Warning is issue after number of days value of PASSWORD_LIFE_TIME exceed upto value of PASSWORD_GRACE_TIME defined after that password is expired.
PASSWORD_REUSE_TIME defined in days
PASSWORD_REUSE_MAX defined in days
Both use PASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 15, then the user can reuse the password after 30 days if the password has already been changed 15 times.
PASSWORD_LOCK_TIME : Number of days an account will be locked after the specified number of consecutive failed login attempts.
PASSWORD_VERIFY_FUNCTION : PL/SQL password complexity verification script be passed as an argument to the CREATE PROFILE statement.

Create the password profile

CREATE PROFILE app_user_profile LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_VERIFY_FUNCTION verify_function
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10;

Alter the password parameter in profile

---defined Password must be changed after 30 days
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 30;

---Extra 5 day grace period for change password
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 5;

Testing
for testing you can specify minutes (n/1440) or even seconds (n/86400)
Example:

-- Change the profile setting to 1 minuter for password expire
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME 1/1440;

--Set grace period after that 2 days
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME 2;
Profile altered.

--on login with user find the following message:
C:\Users\e3019447>sqlplus test@xe
SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 5 14:56:15 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-28002: the password will expire within 2 days
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL>

--rollback to unlimited.
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;

Refer:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

Managed resources parameter in profile for User in Oracle

Managed resources parameter in profile for User in Oracle

In user profile Resource parameter include the cpu resource, cpu call, logical read, private Sga, idle time, connect time parameter.

Resource_parameters in Profile:

[SESSIONS_PER_USER          n|UNLIMITED|DEFAULT]
[CPU_PER_SESSION            n|UNLIMITED|DEFAULT]     
[CPU_PER_CALL               n|UNLIMITED|DEFAULT]            
[CONNECT_TIME               n|UNLIMITED|DEFAULT]
[IDLE_TIME                  n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION  n|UNLIMITED|DEFAULT]  
[LOGICAL_READS_PER_CALL     n|UNLIMITED|DEFAULT]
[COMPOSITE_LIMIT            n|UNLIMITED|DEFAULT]
[PRIVATE_SGA                n [K|M]|UNLIMITED|DEFAULT]

Check the user belong to which profile

select profile from dba_users where username = 'TEST';

Profile
------------
DEFAULT

Check resource_name and limit defined for the profile.
It include both resource parameter and password parameter. We are working on resource parameter in this blog.

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 UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED

Meaning of Resource Name
COMPOSITE_LIMIT: Specify the total resource cost for a session, expressed in service units. The total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
SESSIONS_PER_USER: Number of concurrent sessions for the user.
CPU_PER_SESSION: CPU time limit for a session(hundredth of seconds)
CPU_PER_CALL: CPU time limit for a call (a parse, execute, or fetch)(hundredths of seconds)
CONNECT_TIME: Total elapsed time limit for a session(minutes)
IDLE_TIME: Permitted periods of continuous inactive time during a session(minutes).
LOGICAL_READS_PER_SESSION: Permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL: Permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA: Amount of private space a session can allocate in the shared pool of the system global area (SGA).

Alter the Resource parameter in profile

---Alter the Composite limit which is total weighted sum of units.
ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT 5000000;

---Alter the SESSIONS_PER_USER
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;

--Total elapsed time for session
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;

--Alter CPU_PER_SESSION cpu usage per session
ALTER PROFILE DEFAULT LIMIT CPU_PER_SESSION 2;

--Alter CPU timelimit
ALTER PROFILE DEFAULT LIMIT CPU_PER_CALL 20000;

--Alter CPU CONNECT_TIME
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 1;

--Alter ideal time limit for session inactive
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 1;

Refer:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

Check unusable and invalid index in Oracle

Check unusable and not valid Index in Oracle

Following Query will convert all the unusable and not valid index in Oracle. Query will cover the complete index with partition index and sub partition index. Result will give you the rebuild command of invalid or unusable index.
You can directly run that and on sqlplus and make them valid or usable state.

Query

SELECT 'ALTER INDEX ' || OWNER || '.' ||
INDEX_NAME || ' REBUILD ' ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_INDEXES
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD PARTITION ' || PARTITION_NAME ||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_PARTITIONS
WHERE STATUS='UNUSABLE'
UNION
SELECT 'ALTER INDEX ' || INDEX_OWNER || '.' ||
INDEX_NAME ||
' REBUILD SUBPARTITION '||SUBPARTITION_NAME||
' TABLESPACE ' || TABLESPACE_NAME || ';'
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS='UNUSABLE';