Want to post your scripts here in this blog ? Email it to snojha@gmail.com. You will be rewarded for your scripts if selected by our experts.

Friday, September 18, 2009

How to find locked oracle database user account details

USER_AUDIT_SESSION contains audit trail records concerning connections and disconnections of the current user.
======================================================================================
Select os_username, username,userhost,terminal,timestamp,action_name,logoff_time,RETURNCODE
from DBA_AUDIT_SESSION where RETURNCODE in (1017,28000) order by timestamp.
look for the row just before reurncode 28000 and you will get when,which os user from which terminal the account got unlocked.

DBA_AUDIT_SESSION table
======================================================================================
Column Datatype NULL Description
OS_USERNAME VARCHAR2(255) Operating system login username of the user whose actions were audited
USERNAME VARCHAR2(30) Name (not ID number) of the user whose actions were audited
USERHOST VARCHAR2(128) Client host machine name
TERMINAL VARCHAR2(255) Identifier of the user's terminal
TIMESTAMP DATE Date and time of the creation of the audit trail entry (date and time of user login for entries created by AUDIT SESSION) in the local database session time zone
ACTION_NAME VARCHAR2(28) Name of the action type corresponding to the numeric code in the ACTION column in DBA_AUDIT_TRAIL
LOGOFF_TIME DATE Date and time of user log off
LOGOFF_LREAD NUMBER Logical reads for the session
LOGOFF_PREAD NUMBER Physical reads for the session
LOGOFF_LWRITE NUMBER Logical writes for the session
LOGOFF_DLOCK VARCHAR2(40) Deadlocks detected during the session
SESSIONID NUMBER NOT NULL Numeric ID for each Oracle session
RETURNCODE NUMBER NOT NULL Oracle error code generated by the action.
Some useful values:
0 - Action succeeded
2004 - Security violation
28000 - user locked
1017 -wrong combination user/pasword
CLIENT_ID VARCHAR2(64) Client identifier in each Oracle session
SESSION_CPU NUMBER Amount of CPU time used by each Oracle session
EXTENDED_TIMESTAMP TIMESTAMP(6) WITH TIME ZONE Timestamp of the creation of the audit trail entry (timestamp of user login for entries created by AUDIT SESSION) in UTC (Coordinated Universal Time) time zone
PROXY_SESSIONID NUMBER Proxy session serial number, if an enterprise user has logged in through the proxy mechanism
GLOBAL_UID VARCHAR2(32) Global user identifier for the user, if the user has logged in as an enterprise user
INSTANCE_NUMBER NUMBER Instance number as specified by the INSTANCE_NUMBER initialization parameter
OS_PROCESS VARCHAR2(16) Operating System process identifier of the Oracle process

Wednesday, August 19, 2009

Get table and index DDL using DBMS_METADATA.GET_DDL

Prior to Oracle9i, getting table and index DDL was a time-consuming and tricky process. You could run the export utility with ROWS=NO, but the output was hard to re-use because of quoted strings. The only other option was to write complex dictionary scripts that might not work on complex objects such as IOT and nested tables.

Punching DDL from the dictionary is very useful when you are migrating a system to a new platform and you want to pre-create the objects in a new tablespace so that you can import with IGNORE=Y.

In Oracle9i we have the exciting new dbms_metadata utility to display DDL directly from the data dictionary. Using this powerful utility, we can punch individual objects or an entire schema.

Best of all, it is easy. You simply execute dbms_metadata. get_ddl.

capture index ddl before you drop.


create table indxddl
(
tbl_name varchar(32),
indx_name varchar(32),
indxddl varchar(4000)
);

create table test31(id numeric(16), name varchar2(128));


create index test31indx1 on test31(id);


insert into indxddl(tbl_name,indx_name,indxddl) values ('test31','test31indx1','create index test31indx1 on test31(id)');
INSERT INTO INDXDDL(TBL_NAME,INDX_NAME,INDXDDL) VALUES ('TEST31','TEST31INDX1','CREATE INDEX TEST31INDX1 ON TEST31(ID)');

set long 1000
select * from indxddl;

delete from indxddl;

create index test31indx2 on test31(name);

select upper(index_name) from user_indexes where upper(table_name)='TEST31'
minus
select upper(indx_name) from indxddl where upper(tbl_name)='TEST31';

SELECT DBMS_METADATA.GET_DDL('INDEX','TEST31INDX2') FROM USER_INDEXES u;

CREATE OR REPLACE procedure indexddl_proc( p_table_name varchar2)
as
cnt number;
indexddl varchar(4000);
begin
select count(*)
into cnt
from
(select upper(index_name) from user_indexes where table_name ='TEST31'
minus
select upper(indx_name) from INDXDDL where tbl_name = 'TEST31');
if cnt>0 then
for i in
(select index_name as a from user_indexes where table_name ='TEST31'
minus
select upper(indx_name) as a from indxddl where upper(tbl_name)= 'TEST31')
loop
indexddl:=DBMS_METADATA.GET_DDL('INDEX',i.a);
--SELECT DBMS_METADATA.GET_DDL('INDEX',i.a) into indexddl FROM USER_INDEXES u;
insert into indxddl(tbl_name,indx_name,indxddl) values (upper(p_table_name),upper(i.a),upper(indexddl));
end loop;
end if;

for x in (select table_name,index_name
from indxddl
where table_name = ltrim(rtrim(p_table_name,''''), '''' ) ) loop
execute immediate 'drop index '||x.index_name;
end loop;
execute immediate 'truncate table '||ltrim(rtrim(p_table_name,''''), '''' );
exception
when others then
raise;
end indexddl_proc;
/

execute indexddl_proc('TEST31');

To punch off all table and indexes for the EMP table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;

spool off;

Here is the output. The only thing missing is the ending semicolons after each statement. Just for illustration, we show how a primary key can be punched as part of the table DDL or separately using the INDEX argument.
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 12288 NEXT 12288 MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."DEPT_IDX" ON "SCOTT"."DEPT" ("DNAME")
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM"

Now we can modify the syntax to punch a whole schema. It us easily done by selecting dbms_metadata. get_ddl and specifying USER_TABLES and USER_INDEXES. :

set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u;

spool off;

Wednesday, May 20, 2009

Find the tables/indexes that have stale statistics in a database

Using the code below one can find the tables/indexes that have stale statistics in a database, when options=>’GATHER AUTO’
is used oracle gathers statistics analyzes the tables/indexes that have stale statistics. Table monitoring is
enabled by default in 10g to find table which is used to find table statistics, when STATISTICS_LEVEL is set
to BASIC table monitoring is disabled. In 9i one can enable/disable table monitoring by
calling DBMS_STATS.ALTER_SCHEMA_TABLE_MONITORING

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/

SYS.COL_USAGE$ TABLE
SYS.DEPENDENCY$ TABLE
SYS.HISTGRM$ TABLE
SYS.HIST_HEAD$ TABLE


Note: To find schema level stats that are stale one can call
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>’SCOTT’, objlist=>ObjList, options=>’LIST STALE’);

Detailed report of all invalid objects in the database.

SET LINESIZE 145
SET PAGESIZE 9999

clear columns
clear breaks
clear computes

column owner format a25 heading 'Owner'
column object_name format a30 heading 'Object Name'
column object_type format a20 heading 'Object Type'
column status format a10 heading 'Status'

break on owner skip 2 on report
compute count label "" of object_name on owner
compute count label "Grand Total: " of object_name on report

SELECT
owner
, object_name
, object_type
, status
FROM dba_objects
WHERE status <> 'VALID'
ORDER BY owner, object_name
/

Truncating Listener Log File

$cp $ORACLE_HOME/network/log/listener.log $ORACLE_HOME/network/log/listener.log_`date +"%Y%m%d"`

$cat /dev/null > $ORACLE_HOME/network/log/listener.log

$nohup compress $ORACLE_HOME/network/log/listener.log_`date +"%Y%m%d"` &

Refreshing stale statistics using the DBMS_STATS package and the GATHER AUTO option.

BEGIN
DBMS_STATS.gather_schema_stats (
ownname => USER,
cascade => TRUE,
options => 'GATHER AUTO');
END;
/

The results of the statistics refresh are evident in the %_TABLES views, as shown below.

SELECT table_name,
num_rows,
avg_row_len
FROM user_tables;

TABLE_NAME NUM_ROWS AVG_ROW_LEN
------------------------------ ---------- -----------
DEPARTMENTS 1000 21

To automate the gathering of statistics, simply schedule a job to the call the DBMS_STATS package. The example below refreshes stale statistics for for schema "TIM" at midnight every day.

SET SERVEROUTPUT ON
DECLARE
l_job BINARY_INTEGER;
BEGIN
DBMS_JOB.submit (
job => l_job,
what => 'BEGIN DBMS_STATS.gather_schema_stats (' ||
'ownname => ''TIM'', cascade => TRUE, options => ''GATHER AUTO''); END;',
next_date => SYSDATE,
interval => '/* 1 Day Interval */ TRUNC(SYSDATE) + 1');
COMMIT;
DBMS_OUTPUT.put_line('Job: ' || TO_CHAR(l_job));
END;
/

Alternatively we could set monitoring on for all database tables, with the exception of those in the SYS schema, and use the following procedure:

DBMS_STATS.gather_database_stats(cascade => TRUE, options => 'GATHER AUTO');

Since only those tables with stale or empty stats are processed the overhead on the system is greatly reduced.

Note. In Oracle 10g, gathering stale statistics for the database once a day happens by default.

Statistics on Tables, Indexes and Columns

Statistics on tables, indexes, and columns are stored in the data dictionary. To view statistics in the data dictionary, query the appropriate data dictionary view (USER, ALL, or DBA). These DBA_* views include the following:

* DBA_TABLES
* DBA_OBJECT_TABLES
* DBA_TAB_STATISTICS
* DBA_TAB_COL_STATISTICS
* DBA_TAB_HISTOGRAMS
* DBA_INDEXES
* DBA_IND_STATISTICS
* DBA_CLUSTERS
* DBA_TAB_PARTITIONS
* DBA_TAB_SUBPARTITIONS
* DBA_IND_PARTITIONS
* DBA_IND_SUBPARTITIONS
* DBA_PART_COL_STATISTICS
* DBA_PART_HISTOGRAMS
* DBA_SUBPART_COL_STATISTICS
* DBA_SUBPART_HISTOGRAMS

Oracle 10g Data Dictionary Table Statistics

To collect statistics on dictionary objects, execute the following statements. The collection process can be done in different formats.

Format (1)
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

Format (2)
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

Format (3)
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

As seen in the above examples, the gather_schema_stats procedure accepts the sys argument to perform statistics collections. In case of the gather_database_stats procedure, there is a new argument gather_sys whose default value is FALSE. When needed, you can set it to TRUE and execute the procedure.

There is another procedure, delete_dictionary_stats, which allows you to remove data dictionary stats.

When you execute the gather_dictionary_stats procedure, it gathers statistics from the SYS and SYSTEM schemas, as well as any other schemas that are related, such as OUTLN or DBSNMP schemas.