Ask Anantha

Virtual Columns has been introduced in Oracle Database 11g Release 1. Here is a good tutorial I could find from Oracle-Base website. The link for the tutorial is at the bottom of this article. - Anantha When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees (  id          NUMBER,  first_name  VARCHAR...

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