Automatically Locking a User Account After a Failed Login

Oracle Database can lock a user's account after a specified number of consecutive failed log-in attempts. You can set the PASSWORD_LOCK_TIME user's profile parameter to configure the account to unlock automatically after a specified time interval or to require database administrator intervention to be unlocked. The database administrator also can lock accounts manually, so that they must be unlocked explicitly by the database administrator.

SQL> CREATE PROFILE prof LIMIT

     FAILED_LOGIN_ATTEMPTS 10

     PASSWORD_LOCK_TIME 30;

SQL> ALTER USER DHANA PROFILE prof;

Above query sets the maximum number of failed login attempts for the user DHANA to 10 (the default), and the amount of time the account locked to 30 days. The account will unlock automatically after30 days.

SQL> SELECT * FROM DBA_PROFILES
          WHERE resource_name = 'PASSWORD_LOCK_TIME';

If you do not specify a time interval for unlocking the account,  then  PASSWORD_LOCK_TIME assumes the value specified in a default profile. (The recommended value is 1 day.) If you specify PASSWORD_LOCK_TIME as UNLIMITED, then you must explicitly unlock the account by using an ALTER USER statement.

SQL> ALTER USER DHANA ACCOUNT UNLOCK;

After a user successfully logs into an account, Oracle Database resets the unsuccessful login attempt count for the user, if it is non-zero, to zero.

Controlling Password Aging and Expiration

SQL> CREATE PROFILE prof LIMIT

     FAILED_LOGIN_ATTEMPTS 4

     PASSWORD_LOCK_TIME 30

     PASSWORD_LIFE_TIME 180

     PASSWORD_GRACE_TIME 3;

SQL> ALTER USER DHANA PROFILE prof;

SQL> SELECT * FROM DBA_PROFILES  

     where resource_name IN                 

     ('PASSWORD_LOCK_TIME','PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME');

Enabling Archive Log Mode

SQL> SELECT LOG_MODE FOR V$DATABASE;

SQL> SHOW PARAMATER LOG_ARCHIVE_START

SQL> SHOW PARAMATER LOG_ARCHIVE_DEST_1

SQL> ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;

SQL> SHUT IMMEDIATE;

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

Format Masking

nls_parameters:
Optional national language support parameters (nls_parameters) are useful for specifying the language and format in which the names of date and numeric elements are returned. These parameters are usually absent, and the default values for elements such as day or month names and abbreviations are used.

There is a publicly available view called NLS_SESSION_PARAMETERS that contains the NLS parameters for your current session.

 SELECT * FROM NLS_SESSION_PARAMETERS;

 SHOW PARAMETER NLS_CURR

 SELECT * FROM NLS_SESSION_PARAMETERS;

By default, NLS_CURRENCY would be '$'. We can change this to Session level.

ALTER SESSION set NLS_CURRENCY='Rs';

ALTER SESSION set NLS_CURRENCY='$'  ;

Numeric format Masks:
SELECT TO_CHAR(000201,'$000000.099')||' represents the price in Dollars' from DUAL;

SELECT TO_CHAR(000201,'L000000.099')||' represents the price in local Currency' from DUAL;

Date format Masks: 

SELECT  to_char(sysdate)||' is today''s date' from DUAL;

SELECT  TO_CHAR(sysdate,'Month')||'is a special time' from DUAL;

By prefixing the format model with the letters fm, Oracle is instructed to trim all spaces from the names of days and months. There are many formatting options for dates being converted into characters, some of which are listed in below diagram.

SELECT TO_CHAR(sysdate,'fmMonth')||'is a special time' from DUAL;

How to convert a oracle table into XML

SQL> SELECT DBMS_XMLGEN.GETXML('SELECT * FROM EMP') TEST_XML FROM DUAL;   

Split Functionality

SQL> CREATE OR REPLACE type split_tbl AS   TABLE OF VARCHAR2(32767);

SQL> CREATE OR REPLACE
  FUNCTION split(
      p_list VARCHAR2,
      p_del  VARCHAR2 := ',' )
    RETURN split_tbl pipelined
  IS
    l_idx pls_integer;
    l_list  VARCHAR2(32767) := p_list;
    l_value VARCHAR2(32767);
  BEGIN
    LOOP
      l_idx   := instr(l_list,p_del);
      IF l_idx > 0 THEN
        pipe row(SUBSTR(l_list,1,l_idx-1));
        l_list := SUBSTR(l_list,l_idx +LENGTH(p_del));
      ELSE
        pipe row(l_list);
        EXIT;
      END IF;
    END LOOP;
    RETURN;
  END split;

SQL>   SELECT split('Raju,Kishore,Swathi,Lars') FROM dual;

Above procees seems a bit clumsy. More over we can get this result with out creating a function.

SQL> SELECT REGEXP_SUBSTR ('Raju,Kishore,Swathi,Lars', '[^,]+', 1, level) val
        FROM DUAL
        CONNECT BY 
        level <= LENGTH(REGEXP_REPLACE('Raju,Kishore,Swathi,Lars','[^,]*'))+1;

New 11g Result Cache

select /*+ result_cache */ SUM(sal)
from   scott.emp
where  deptno=20;


When a query with RESULT_CACHE hint is run, Oracle will see if the results of the query have already been executed, computed, and cached, and, if so, retrieve the data from the cache instead of querying the data blocks and computing the results again. Take the following important points into consideration before using this feature:

The Result Cache feature is useful only for SQL queries that are executed over and over again frequently.

The underlying data doesn’t change very often. When the data changes, the result set is removed from the cache.

If you are executing the same queries over and over, using the RESULT_CACHE hint often makes subsequent queries run faster


Note that the Result Cache does not work when you are logged in as sysdba.

Query to get Invalid Objects in Oracle Schema

SQL>  SELECT SUBSTR(object_name,1,20) "Object",
           OBJECT_TYPE "OType",
           TO_CHAR(LAST_DDL_TIME, 'DD-MON-YYYY HH24:MI:SS') "Change Date",
            status
           FROM USER_OBJECTS
           WHERE STATUS <> 'VALID';

Alert Log

The alert log file (also referred to as the ALERT.LOG) is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.

Oracle will automatically create a new alert log file whenever the old one is deleted.

Location of Alert log file :

Oracle will write the alert.log file to the directory as specified by the BACKGROUND_DUMP_DEST parameter. If this parameter is not set, the alert.log will be created in the ORACLE_HOME/rdbms/trace directory.

SQL> show parameter BACKGROUND_DUMP_DEST

User can write to Alert log file:

 -- Write message to alert.log
 exec dbms_system.ksdwrt(2, 'Look Ma, I can write to the alert.log file!');
 PL/SQL procedure successfully completed.

 -- Flush the buffer
 exec dbms_system.ksdfls;
 PL/SQL procedure successfully completed.

Proper usage of Oracle Indexes

Reverse key Indexes:

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table).  For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed. 

Note: An Oracle reverse key index DOES NOT change the functionality of the index, and it's not the same as reversing the index key values.

In general, an Oracle reverse key index relieve data block contention (buffer busy waits) when inserting into any index where the index key is a monotonically increasing value which must be duplicated in the higher-level index nodes.

With the index key reversal, only the lowest-level index node is changed, and not all of the high-order index nodes, resulting in far faster insert speed.  For updates, Oracle updates the index nodes with each update statement.
Depending on the size of your update batch, it's often faster to drop, update and then re-build the index. If you have more than one CPU, you might consider doing a parallel index rebuild for faster speed. It splits-up the full-table scan:

Bitmap Index vs. B-tree Index: Which and When?

Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values--such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems.
In fact, a bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.

Query to get all Function based Indexes:

SELECT table_name,
  index_name,
  column_expression
FROM DBA_IND_EXPRESSIONS
WHERE TABLE_OWNER IN ( 'SCOTT')
  AND TABLE_NAME = 'EMP'
ORDER BY index_name,
  column_position;

BLOB Datatype

BLOB : Binary Large Object  is a varying-length binary string that can be up to 2,147,483,647 characters long. Like other binary types, BLOB strings are not associated with a code page. In addition, BLOB strings do not hold character data.

Example: 
create table TABLE_BLOB
( id number,
  data blob);
  
 insert into table_blob values(1,utl_raw.cast_to_raw('Dhanunjay')); 
  
 select * from TABLE_BLOB ;

Tablespace Management

A Tablespace is a

• Logical storage unit
• Bridge between objects and physical files
• Collection of Physical files ( Eg : System.dbf, Sysaux.dbf, UNDO.dbf, userdata.dbf )

 Oracle contains 3 types of tablespaces, which is an allocation of space in the database that can contain schema objects.

• A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
• An undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.
• A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in temp files.

Prerequisites to create a Tablespace:

• You must have the CREATE TABLESPACE system privilege 
• To create the SYSAUX tablespace, you must have the SYSDBA system privilege.

Syntax: 

SQL>  CREATE TABLESPACE <TBS>  DATAFILE <'/DISK2/ORADATA/RAC18/DATA.dbf'> SIZE 10M;

SQL> DROP TABLESPACE USERS;
With above statement, Tablespace will be dropped logically. Physically the datafiles will not be dropped. It just disconnects the link.
If we want to drop contents and datafiles, then we need to fire below query
SQL> DROP TABLESPACE USERS INCLUDING  CONTENTS AND DATAFILES;

BIGFILE | SMALLFILE

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.

A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (222) blocks.

SQL>  CREATE BIGFILE  TABLESPACE <TBS>  DATAFILE <'/DISK2/ORADATA/RAC18/DATA.dbf'> SIZE 10M;

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restriction on Bigfile Tablespaces You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE clause.

Uses of Tablespaces:

• Availability
• Performance

Working with Tablespaces:

• We can have 65536 tablespaces in database
• We can see the details of tablespaces in DBA_TABLESPACES
  SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
• Status of a tablespace can be Off line or On line ( READ ONLY or READ WRITE )
  SQL> ALTER TABLESPACE USERS READ ONLY;
  SQL> ALTER TABLESPACE USERS READ WRITE;
  SQL> ALTER TABLESPACE USERS OFFLINE;
• We can see the details of datafiles in DBA_DATA_FILES
  SQL> SELECT FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024  FROM DBA_DATA_FILES;
• To reuse a physical file
  SQL> CREATE TABLESPACE TBS DATAFILE  '</loc> ' REUSE;

Resizing the tablespace:

• SQL> ALTER TABLESPACE USERS ADD DATAFILE </loc> SIZE 20M;
• SQL> ALTER TABLESPACE USERS DROP DATAFILE </loc> ;
• SQL> ALTER DATABASE DATAFILE 4 RESIZE 40M;
  OR
  SQL> ALTER DATABASE DATAFILE  </loc>  RESIZE 40M;
• SQL> ALTER DATABSE DATAFILE 4 AUTO EXTEND ON;

Temporary Tablespaces:

It is allocation of space in the database that can contain schema objects for the duration of a session.

How to get Create Table script in Oracle

SELECT DBMS_METADATA.GET_DDL('TABLE', 'TABLE_NAME') FROM dual;

Boolean Datatype in Oracle

There is no such type of datatype called Boolean in Oracle

Drop a Database

SQL> SHUT IMMEDIATE;

SQL> STARTUP MOUNT EXCLUSIVE RESTRICT;

SQL> DROP DATABASE ;

Oracle Database Creation in Silent Mode

dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname ora11g -sid ora11g -responseFile NO_VALUE -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration LOCAL

Enter SYS user password:

Enter SYSTEM user password:

Enter DBSMNP user password:

Enter SYSMAN user password:

Copying Database files

1% Complete

37% Complete

Creating and starting Oracle Instance

40% Complete

62% Complete

Completing Database Creation

66% Complete

100% Complete

Look at th Log file "D:\Oracle......log" for further details.

Foreign key relationship between two different schemas

Let use assume 2 schemas scott1 and scott2.
In scott1 we have emp table and in scott2 we have dept table. Now take the requirement to establish foreign key relation ship between scott1.emp and scott2.dept;

SQL> GRANT REFERENCES ON SCOTT2.DEPT TO SCOTT1

SQL> CONN SCOTT1/PASSWORD

SQL> ALTER TABLE EMP ADD CONSTRAINT FK_EMP_DEPTID FOREIGN KEY (SCOTT_DEPT_ID) REFERENCES SCOTT2.DEPT (ID);

JDA

1. Difference b/w count(*) and count(1)
2. If a record is inserted in an table, the relevant MV should automatically be refreshed. How can it be done?
3. Why XML is used for any system to system communication
4. DELETE and TRUNCATE
5. Symantics
6. What do you exactly mean by rowid and rownum
7. How do you load data from file to table. Have you ever user SQL Loader.

Working with TABLE Type

CREATE OR REPLACE TYPE SCOTT.v_emp_data AS OBJECT

(

  EMPNO    number(4),

  ENAME     varchar2(10),

  JOB  varchar2(9),

  mgr number(4),

  hiredate date,

  sal  number(7,2),

  comm   number(7,2),

  deptno     number(2)

)

/

CREATE OR REPLACE TYPE SCOTT.v_emp_tab as table of  v_emp_data;

/

CREATE OR REPLACE PROCEDURE scott.d_loaddata (i_data v_emp_tab)

AS

   v_data   v_emp_tab;

BEGIN

   INSERT INTO emp_load

      SELECT *

        FROM TABLE (i_data);

   NULL;

END;

Working with XMLType and xmltable

Query to get multiple records from a single XML.

select * from xmltable('*' passing xmltype('<employee>
<emp>

    <empno>102</empno>

    <ename>Krishna</ename>

    <job>Accounts</job>

    <mgr></mgr>

    <hiredate></hiredate>

    <sal>15000</sal>

    <comm>10</comm>

    <deptno>50</deptno>

</emp>

<emp>

    <empno>101</empno>

    <ename>Raja</ename>

    <job>Development</job>

    <mgr>102</mgr>

    <hiredate></hiredate>

    <sal>15000</sal>

    <comm>10</comm>

    <deptno>50</deptno>

</emp>

</employee>').extract('//emp')

columns empno int path 'empno',

ENAME varchar2(10) path 'ename',

JOB varchar2(9) path 'job',

mgr number path 'mgr' ,

HIREDATE date path 'hiredate',

sal number path 'sal' ,

comm number path 'comm' ,

deptno number path 'deptno'

) x;

                                     

Procedure to insert multiple records from an XML into a table:        

CREATE OR REPLACE PROCEDURE scott.d_loadxmldata (i_data xmltype)

AS

BEGIN

INSERT INTO emp_load

select * from xmltable('*' passing i_data.extract('//emp')

columns empno int path 'empno',

ENAME varchar2(10) path 'ename',

JOB varchar2(9) path 'job',

mgr number path 'mgr' ,

HIREDATE date path 'hiredate',

sal number path 'sal' ,

comm number path 'comm' ,

deptno number path 'deptno'

) x;

commit;

END;

How to convert a oracle table data into XML

SQL> SELECT DBMS_XMLGEN.GETXML('SELECT * FROM EMP') TEST_XML FROM DUAL;