Ask Anantha

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 DESC ription: Name         Type                Nullable Default Comments  ------------ ------------------- -------- ------- --------  USER#        NUMBER                                         NAME         VARCHAR2(30 BYTE)   ...

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

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...

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.

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.fexi...

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 th...

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 requ...

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....

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  th...

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*/ ...