How to get the last password changed time for a oracle user

Question: How to get the last password changed time for a oracle user?
Answer: The SYS view user$ consists of a column PTIME which tells you what was the last time the password was changed for the user.

Try this query:

SELECT name,
       ctime,
       ptime
FROM   sys.user$
WHERE  name = 'USER-NAME';



Note: Replace USER-NAME with the user name which you want to know the information.


CTIME Indicates - Creation Time
PTIME Indicates - Password Change Time

Here's the table DESCription:
Name         Type                Nullable Default Comments 
------------ ------------------- -------- ------- -------- 
USER#        NUMBER                                        
NAME         VARCHAR2(30 BYTE)                             
TYPE#        NUMBER                                        
PASSWORD     VARCHAR2(30 BYTE)   Y                         
DATATS#      NUMBER                                        
TEMPTS#      NUMBER                                        
CTIME        DATE                                          
PTIME        DATE                Y                         
EXPTIME      DATE                Y                         
LTIME        DATE                Y                         
RESOURCE$    NUMBER                                        
AUDIT$       VARCHAR2(38 BYTE)   Y                         
DEFROLE      NUMBER                                        
DEFGRP#      NUMBER              Y                         
DEFGRP_SEQ#  NUMBER              Y                         
ASTATUS      NUMBER                       0                
LCOUNT       NUMBER                       0                
DEFSCHCLASS  VARCHAR2(30 BYTE)   Y                         
EXT_USERNAME VARCHAR2(4000 BYTE) Y                         
SPARE1       NUMBER              Y                         
SPARE2       NUMBER              Y                         
SPARE3       NUMBER              Y                         
SPARE4       VARCHAR2(1000 BYTE) Y                         
SPARE5       VARCHAR2(1000 BYTE) Y                         
SPARE6       DATE                Y                         

Default Schemas in 11g

When an 11g database is created without tweaking any of the options, using either dbca or the installer, the schema listed in the table below, 36 of them(!), are created by default.

This document gives an overview of their purpose and function and, should the functionailty not be required, whether they can be safely deleted or not without compromising the fundamental operation of the database. Additionally, having been removed, if a schema is required again, there are details of which script(s) are required to be run in order to recreate it.

Click here to read the entire article which lists the schema's and its importance in 11g.

Metalink Note:
Further information can be found in Metalink article 160861.1

Difference between Oracle 10g, 11g with regard to Index Rebuild Online

Creating or Rebuilding Indexes Online:

Online Index Rebuilds allows you to perform DML operations on the base table during index creation. You can use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop an index in an online environment.

During index build you can use the CREATE INDEX ONLINE to create an index without placing an exclusive lock over the table. CREATE INDEX ONLINE statement can speed up the creation as it works even when reads or updates are happening on the table.

The ALTER INDEX REBUILD ONLINE can be used to rebuild the index, resuming failed operations, performing batch DML, adding stop words to index or for optimizing the index.

The CREATE INDEX ONLINE and ALTER INDEX REBUILD ONLINE options have been there for a long time to easy the task of online index rebuilding. However in highly active they still can introduce locking issues.

Table Locks:

A table lock is required on the index base table at the start of the CREATE or REBUILD process to guarantee DD information. A lock at the end of the process is also required so as to merge changes into the final index structure.

The time taken to complete the indexing process will increase as the indexing process will hang if there is an active transaction on the base table of the index being created or rebuilt at the time one of these locks is required. Another important issue to be considered is that any other transaction taking place on the base table that started after indexing process will also be locked unless the indexing releases its locked.

This issue can have serious impact on the response time in highly concurrent systems. This backlog of locked transactions can be quite significant depending on the time taken by initial transactions to commit or rollback.

Oracle 11g

Oracle11g has provided enormous improvements in the locking implications regarding creating or rebuilding indexes online. Creating or Rebuilding Indexes Online in Oracle 11g also requires two associated locks on the base table. One lock is required at the start of indexing process and other at the end of indexing process.

The indexing process still hangs until all prior transactions have been completed if an active transaction is going on the base table at the time one of these locks is required.

However such transaction will no longer be locked and complete successfully if the indexing process has been locked out and subsequent transactions relating to the base table starts afterwards.

In Oracle 11g the indexing process no longer effects other concurrent transactions on the base table. The only process potentially left hanging while waiting to acquire its associated lock resource. Now we will compare the index rebuild locking mechanism in Oracle 10g and Oracle 11g.

The difference on ground (10g vs 11g)

Session No.	Executing in 10g	Executing in 11g
1	CREATE TABLE MYTABLE AS SELECT rownum id, ‘Anantha’ name from dual connect by level <= 200000; Table Created. CREATE INDEX MYTABLE_IDX on MYTABLE(id); Index Created.	CREATE TABLE MYTABLE AS SELECT rownum id, ‘Anantha’ name from dual connect by level <= 200000; Table Created. CREATE INDEX MYTABLE_IDX on MYTABLE(id); Index Created.
2	INSERT INTO MYTABLE VALUES(200001, ‘No Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200001, ‘No Name’); 1 row created.
1	ALTER INDEX MYTABLE_INDEX REBUILD ONLINE; (Session gets hanged)	ALTER INDEX MYTABLE_INDEX REBUILD ONLINE; (Session gets hanged)
3	INSERT INTO MYTABLE VALUES(200002, ‘Again No Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200001, ‘Again No Name’); 1 row created.
2	COMMIT; Commit Complete.	COMMIT; Commit Complete.
2	INSERT INTO MYTABLE VALUES(200003, ‘Some Name’); 1 row created.	INSERT INTO MYTABLE VALUES(200003, ‘Some Name’); 1 row created.
3	COMMIT; Commit Complete.	COMMIT; Commit Complete. (Session 2) Index Altered (Session 1)

To conclude I would say that even if an index rebuild process is performed online still it requires a lock at the start and at the end of the index rebuild process and these locks eventually causes other concurrent transactions on the table to be hanged.

Therefore an Online Index Rebuilding in Oracle 11g can be still affected by concurrent transactions. However it will not in turn cause locking issues for other concurrent transactions on the base table.

Who has locked my package?

This question I have frequently faced while developing packages/procedures in PL/SQL in Oracle. 


Question: How will I identify who has locked a procedure or a package. I need to identify this because it should not hang after the compilation is issued?


Answer: You may see the details from v$sql with the search text (Your package name). If the query fetches results then join it with v$session to get the session information.


select * 
from v$sql, v$session
where sql_address=address
and upper(sql_text) like '%PACKAGE_NAME%';


Hope this simple tip saves you some time.

Getting file size with PL/SQL

UTL_FILE procedure has been enhanced in Oracle 9i and since then it provides a procedure fgetattr to return file size. PL/SQL Evangelist Steven Feuerstein has come up with this function that returns file length.

CREATE OR REPLACE FUNCTION flength (
   location_in   IN   VARCHAR2,
   file_in       IN   VARCHAR2
)
   RETURN PLS_INTEGER
IS 
   TYPE fgetattr_t IS RECORD (
      fexists       BOOLEAN,
      file_length   PLS_INTEGER,
      block_size    PLS_INTEGER
   );

   fgetattr_rec   fgetattr_t;
BEGIN
   UTL_FILE.fgetattr (
      location         => location_in,
      filename         => file_in,
      fexists          => fgetattr_rec.fexists,
      file_length      => fgetattr_rec.file_length,
      block_size       => fgetattr_rec.block_size
   );
   RETURN fgetattr_rec.file_length;
END flength; 

Pass a directory structure name as first parameter and the file name as the second parameter.

Create directory TEST as '/tmp';


select flength('TEST','test.txt') size_in_bytes from dual;


size_in_bytes
-------------
        18568

CAUTION: If you are using 9.2.0.6 then there is a database bug which might not return the exact file size. This bug has been fixed in 9.2.0.7 version.

For more information visit here.

Puzzle this time from Steven Feuerstein in Toad World

Hurry up, its an easy Puzzle this time from Steven Feuerstein in Toad World

Its May 2010, and this time Steven Feuerstein chose to play easy to his readers. I am reproducing the question and options here:

Which of the following choices correctly describe a feature of the Oracle database called "external tables"?

A. There is no such thing as an "external table" in Oracle; all tables are stored within the database.
B. External tables are tables defined in other databases, such as mySQL and SQL Server, which can be queried from within an Oracle database.
C. An external table is a read-only table defined in Oracle (it appears, for example, in the ALL_TABLES data dictionary view), whose data is stored in an operating system file.
D. An external table is a normal relational table that is accessed externally, usually from a Java method, and whose contents are displayed on web pages.

I am betting that all must know the answer:
Go to this webpage and click on the answer.

Best of luck to all of you.

Find Value in database/schema

Question: How to find any value in any column in a schema?

I have been searching for this answer for long, and stumbled upon a beautiful response in Oracle Forums and tried the block and stunning to find the output much easily. This saved my day as I was searching for occurence of a value in entire schema. Thanks for pollywog for the posting.

I will reproduce the anonymous block here for my readers:

declare
aCount pls_integer;
begin
for c in
(select table_name, column_name 
from all_tab_columns 
where owner = 'OWNER' 
and data_type = 'DATATYPE') loop


execute immediate 'select count(*) from '||c.table_name||' where '||c.column_name||' = ''value_to_find'' ' into aCount;
if aCount > 0 then
dbms_output.put_line('found value_to_find in table '||c.table_name||', column '||c.column_name);
end if;
end loop;
end;

Just modify the following in the block:
1. OWNER (The schema in which find is required)
2. DATATYPE (Data type of value which you are searching)
3. value_to_find (The string you are finding)

The output is like:

found value_to_find in table , column

Hope this tip helps you too, leave back a comment in case you like this tip.

Difference between package and serially_reusable package

A package once executed will have its variables in UGA (User Global Area). This is by default. If you are creating a package which will be executed only once, then the memory used by the package could be freed up after its execution.

It is using a pragma (hint to compiler) that we do so. This directive is called as serially_reusable. It tells the compiler to run the package and free the space once its executed. The compiler on getting this directive does not save the program variables in UGA but it does in SGA (Shared Global Area).

Each time the package with serially_reusable directive is called, its public variables are initialized. But in a normal package its not initialized every time the package is called.

Here is an example:
create or replace package pkg_with_pragma is
pragma serially_reusable;
n number := 5; -- default initialization
end pkg_with_pragma;


set serveroutput on


BEGIN
pkg_with_pragma.N := 10;
dbms_output.put_line(pkg_with_pragma.N);
END;
/


Output is
10


BEGIN
dbms_output.put_line(pkg_with_pragma.N);
END;
/


Output is
5


create or replace package pkg_without_pragma is
n number := 5; -- default initialization
end pkg_without_pragma;
/


BEGIN
pkg_without_pragma.N := 10;
dbms_output.put_line(pkg_without_pragma.N);
END;
/


Output is
10


BEGIN
pkg_without_pragma.N := 10;
dbms_output.put_line(pkg_without_pragma.N);
END;
/


Output is
10


References: PL/SQL User's Guide and Reference, Oracle® Database Application Developer's Guide - Fundamentals, Burleson Consulting

Unwrap Oracle 10g/11g PLSQL

Article and Script Courtesy: Niels Teusink


The Oracle wrap utility can be used to obfuscate PL/SQL code, to ensure it can't be easily read. Pete Finnigan described (pdf) the wrapping process for Oracle 9g, but for 10g and 11g it still remains a bit of a mystery. I decided to release my Python unwrapping utility (supports 10g and 11g).

The unwrapping steps for 10g are nicely described in the Oracle Hacker's Handbook, but the actual substitution table needed to decode the package is omitted. Nobody (as far as I know) has published it. A lot of people seem to know how to do it though, there is even an online unwrapper available (and I'm sure everyone seriously involved in Oracle security knows how to do it). A Russian-made closed source tool is also available, but tends to upset virus scanners.

So to save everyone a couple of hours of figuring it out, here it is: unwrap.py

It's easy to use (I've used the wrapped procedure from this article as an example):

$ ./unwrap.py wrapped.txt
=== Oracle 10g/11g PL/SQL unwrapper - by Niels Teusink - blog.teusink.net ===

PROCEDURE WRAP_IT (SEED_IN NUMBER)
IS
  V_RAND INTEGER;
BEGIN
  DBMS_RANDOM.INITIALIZE (SEED_IN);
  FOR I IN 1..5 LOOP
   V_RAND := MOD(ABS(DBMS_RANDOM.RANDOM),45);
   DBMS_OUTPUT.PUT_LINE(I||': '||V_RAND);
  END LOOP;
END;


Ask Anantha Team thanks Niels for permitting to post his article here. 

Oracle 11g Enhancements-Compound Triggers

This feature is part of trigger enhancements released in Oracle 11g Release 1. This particular enhancement paves way to use a single trigger to perform more tasks which could'nt have been possible before.

For example, you can use a single trigger for row triggers as well as row-level triggers. The syntax for a compound trigger is as follows:

CREATE OR REPLACE TRIGGER triggername
  FOR triggeraction ON tablename
  COMPOUND TRIGGER


  -- Variable declaration.


  BEFORE STATEMENT IS
  BEGIN
    /*Some PL/SQL Code here*/
  END BEFORE STATEMENT;


  BEFORE EACH ROW IS
  BEGIN
    /*Some PL/SQL Code here*/
  END BEFORE EACH ROW;


  AFTER EACH ROW IS
  BEGIN
    /*Some PL/SQL Code here*/
  END AFTER EACH ROW;


  AFTER STATEMENT IS
  BEGIN
    /*Some PL/SQL Code here*/
  END AFTER STATEMENT;


END triggername;
/

Where triggeraction can be INSERT, UPDATE or DELETE.

Example:-

CREATE OR REPLACE TRIGGER t_employee FOR INSERT OR UPDATE OR DELETE ON emp COMPOUND TRIGGER
  l_emp_id emp.empno%type;


  BEFORE STATEMENT IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - Before Statement Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - Before Statement Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - Before Statement Trigger');
END IF;
  END BEFORE STATEMENT;
  
  BEFORE EACH ROW IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - Before Each Row Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - Before Each Row Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - Before Each Row Trigger');
END IF;
  END BEFORE EACH ROW;
  
  AFTER EACH ROW IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - After Each Row Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - After Each Row Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - After Each Row Trigger');
END IF;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
    IF INSERTING THEN
dbms_output.put_line('INSERT - After Statement Trigger');
END IF;
IF UPDATING THEN
dbms_output.put_line('UPDATE - After Statement Trigger');
END IF;
IF DELETING THEN
dbms_output.put_line('DELETE - After Statement Trigger');
END IF;
  END AFTER STATEMENT;


END t_emplouee;
/

I don't have a 11g database to test create this script. Just create this trigger and there goes the tension of maintaining multiple triggers.