Pages

Monday, March 31, 2014

Auditing DDL changes in Oracle Database


Auditing changes within the database are becoming more and more important. As well as auditing changes to data, auditing DDL changes can be just as critical. This allows the DBA to not only know what changes have been made but also what haven’t. Being able to see what changes have been made to the database can make debugging much quicker.

This solution consists of two tables, one sequence and one trigger.
 
The Tables and Sequence
:


CREATE TABLE ddl_events
( eventId          NUMBER(10,0),
  eventDate        DATE,
  oraLoginUser     VARCHAR2(30),
  oraDictObjName   VARCHAR2(30),
  oraDictObjOwner  VARCHAR2(30),
  oraDictObjType   VARCHAR2(30),
  oraSysEvent      VARCHAR2(30),
  machine          VARCHAR2(64),
  program          VARCHAR2(64),
  osuser           VARCHAR2(30) );

CREATE TABLE ddl_events_sql
( eventId          NUMBER(10,0),
  sqlLine          NUMBER(10,0),
  sqlText          VARCHAR2(4000) );

CREATE SEQUENCE dsq_ddlEvents START WITH 1000;
The parent table ddl_events stores data about the DDL event
•    eventId : Unique key generated by the sequence.
•    eventDate : Populated with SYSDATE.
•    oraLoginUser : The login username taken from the ORA_LOGIN_USER function.
•    oraDictObjName : The name of the event object taken from the ORA_DICT_OBJ_NAME function.
•    oraDictObjOwner : The owner of the event object taken from the ORA_DICT_OBJ_OWNER function.
•    oraDictObjType : The type of the event object taken from the ORA_DICT_OBJ_TYPE function.
•    oraSysEvent : The type of event, EG Create, Alter, Delete. Taken from the ORA_SYS_EVENT function.
•    machine : The name of the machine the event was issued from. Taken from v_$session.
•    program : The name of the program use to issue the command. Taken from v_$session.
•    osuser : The operating system user name. Taken from v_$session.

The ddl_events_sql table stores the sql text from the command. This is a child table of ddl_events with the eventId being the foreign key. EventId and sqlLine can be used to uniquely identify a record.
•    eventId : Link to ddl_events
•    sqlLine : The line number of the executed command
•    sqlText : An sql text line of the command. Taken from the ORA_SQL_TXT function.


The Trigger
CREATE OR REPLACE TRIGGER dtr_ddlEvents
AFTER DDL ON DATABASE
DECLARE

  l_eventId    NUMBER(10,0);
  l_sqlText    ORA_NAME_LIST_T;

BEGIN

  SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL;

  INSERT INTO ddl_events
  ( SELECT l_eventId,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+) );


  FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( l_eventId, l, l_sqlText(l) );
  END LOOP;

END;
/

Auditing the DDL changes is made easy by the DDL triggers. This solution uses and AFTER DDL ON DATABASE clause. This will audit all the changes made on the database. If you are just looking to audit a particular schema the AFTER DDL ON SCHEMA clause could be used instead.

The trigger inserts the data into the into the ddl_events table with data from built-in functions and the v_$session view. The outer join on the v_$session view enables the bulti-in functions to still populate for background processes.

The ddl_events_sql table is then populated by the ORA_SQL_TXT function. The loop populates the ddl_events_sql table for each line of the statement.

Variations :

If auditing the database is too much, a single schema can be audited by using AFTER DDL ON SCHEMA in place of AFTER DDL ON DATABASE.

If auditing the sql text gives too much data, this code can easily be removed, or modified so that the first 4000 characters is inserted into the ddl_events table.

The sql text code above includes auditing for password changes. As the password is sensitive you may want to hide it from the log. You can do this by modifying the sql text loop as below.

   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    IF  ORA_DICT_OBJ_TYPE = 'USER'
    AND INSTR(UPPER(l_sqlText(l)),'IDENTIFIED BY') != 0
    THEN
      l_sqlText(l) := SUBSTR(l_sqlText(l),1,INSTR(UPPER(l_sqlText(l)),'IDENTIFIED BY')+13)||'*';
    END IF;
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( l_eventId, l, l_sqlText(l) );
  END LOOP;
The additional if statement checks for user statements that contain IDENTIFIED BY clauses, the text to the right of the clause is then replaced with an asterisk.

*****
I know auditing system can be more expensive so we can skip by adding a small clause to exclude username right after the below line.

 WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+)
     and username not in ('SYS','SYSTEM'));

Reference:
http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11090

Wednesday, March 26, 2014

Find accounts unused for days

Find accounts unused for days
                                                

Summary
Here's a script that shows accounts and number of days since last use. It assumes session auditing is enabled.
SELECT RPAD(da.username, 12) "Username", 
       TRUNC(SYSDATE - MAX(da.TIMESTAMP)) "Days Inactive",
       LPAD(du.account_status, 16) "Status",
       LPAD(TO_CHAR(MAX(da.TIMESTAMP), 'DD-MON-YYYY'), 16) "Last Login"
FROM dba_users du, dba_audit_session da  WHERE da.action_name LIKE 'LOG%'
--  AND da.username NOT IN ('SYS','SYSTEM')  -- itemize accounts to exclude
--  AND du.profile != ''       -- or profiles to exclude
    AND du.username = da.username
--  AND du.account_status = 'OPEN'        -- could look at just OPEN if desired
GROUP BY da.username, du.account_status
--HAVING MAX(da.TIMESTAMP) <= SYSDATE - 1 ORDER BY 2,1 DESC;
 
The alternative if you don't want to enable auditing is to create a login trigger that stores login information in some table
But....
Logon triggers always have a little risk; if something goes wrong (and things can go wrong) logins could get blocked; for example, if the logon table is unavailable, or a record is locked etc. You must be at the edge to disable them if something go wrong.

Top 10 Reasons DBAs Quit

I found this interesting article online & would like to share:

The role of the DBA is an anomaly in the IT environment. DBAs are often extremely critical yet hidden from view; highly paid yet individual contributors; and very knowledgeable yet rarely consulted. So why does an extremely critical, highly paid, and very knowledgeable resource have a high risk profile for quitting? What factors can an IT manager consider when trying to increase retention rates of DBAs?
The following are the top 10 reasons DBAs quit, and leave their companies at risk:
  1. Burnout – constant overtime, holiday disruptions, and frequent weekend work
  2. On-Call – being on-call too often (or all the time) with frequent incidents
  3. More Pay – the best DBAs are highly valuable, and are always in high demand
  4. Recognition – real or perceived inadequate praise, recognition, or acknowledgement
  5. Constant Firefighting – always reacting to issues with inability to put in proactive measures
  6. Insufficient Involvement – often ignored during design, architecture, or project planning
  7. Job Satisfaction – working on routine tasks with no exposure to advanced features & technologies
  8. No Training – little to no training, conferences, books, or collaboration to improve themselves
  9. Management – manager not understanding DBA workloads, stress, needs, and value
  10. Changing Roles – DBAs leverage their in-depth data knowledge to get a “better” role in IT
No manager can make all employees happy, and that should not be the goal. Rather, the goal should be to cost-effectively minimize unwanted DBA turnover to protect the company from unnecessary risk to data stability and customer impact. Since these 10 factors can vary in intensity from company to company, and vary in importance from individual to individual, the following are the practical “no cost” steps a DBA manager should take:
  • Understand the leading causes of DBA turnover – the 10 listed above.
  • Diagnose your DBA team to determine if any are manifested in your environment.
  • Acknowledge issues – let the team know you are listening and understand their issues.
There, that’s half the battle. Your team will now know that you “get it” even if you aren’t able to immediately address their pain points. You will also now know which pain points have the highest leverage among your team so you can assess the most cost-effective ways to minimize unwanted DBA turnover
For more information please contact myself, Datavail or request of copy of the whitepaper: Innovative Solutions for Database and DBA Management.

Monday, March 24, 2014

ORA-24247: network access denied by access control list

 ORA-24247: network access denied by access control list (ACL)
Yesterday I was trying to send mail from my Oracle Database and I got below mentioned error :-

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "CINP01314", line 255
ORA-06512: at line 21

 In 11g Database , we need to create and configure ACL(access control list). In order to use PL/SQL network utility pakages like UTL_SMTP, UTL_MAIL, we have to configure an ACL file that provides fine grained access to external network services.

Steps to configure :-

1. Find out your SMTP outgoing mail server and configure the database parameter smtp_out_server.

SQL> alter system set smtp_out_server= '10.10.10.10' ;
############### only for UTL_MAIL package this parameter needs to be set#################

system altered

2. Create an access control list file :-

begin
DBMS_NETWORK_ACL_ADMIN
.create_acl (
acl => 'ACL_PERMISSION.xml',
description => 'Enables mail to be sent',
principal => 'ARVIND',   -- USERNAME to which access has to be granted
is_grant => true,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

--  Drop an access control list :-

BEGIN
  DBMS_NETWORK_ACL_ADMIN.drop_acl (
    acl         => '/sys/acls/ACL_PERMISSION.xml');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

3. Assign this ACL to your SMTP network host for your email server :-

begin
dbms_network_acl_admin.assign_acl (
acl => 'ACL_PERMISSION.xml',
host => '100.02.03.04',    -- SMTP network host
lower_port => 25);
commit;
end;
/

PL/SQL procedure successfully completed.

--  TO drop ACL assignments :-

begin
dbms_network_acl_admin.unassign_acl (
acl => 'ACL_PERMISSION.xml',
host => '100.02.03.04',    -- SMTP network host
lower_port => 25);
COMMIT;
end;
/

PL/SQL procedure successfully completed.

4. Grant permission to use ACL file :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'ACL_PERMISSION.xml',
principal => 'ARVIND',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
end;
/

PL/SQL procedure successfully completed.

-- If we want to delete or revoke this permission :-

BEGIN
  DBMS_NETWORK_ACL_ADMIN.delete_privilege (
    acl         => '/sys/acls/ACL_PERMISSION.xml',
    principal   => 'ARVIND',
    is_grant    => TRUE,
    privilege   => 'connect');
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

5. Check that for ARVIND user permission has been set properly :-

SQL> col HOST for a45
SQL> SELECT host, lower_port, upper_port, privilege, status FROM   user_network_acl_privileges;

HOST                                                        LOWER_PORT UPPER_PORT PRIVILEGE             STATUS
---------------------------------------------       ----------------- ---------------- --------------------- ---------------------
100.02.03.04                                             25                    25               connect               GRANTED
<your smtp server host name or address>                                            connect               GRANTED
black                                                                                                  connect               GRANTED

--Status column must have value granted.

SQL> SELECT DECODE(
         DBMS_NETWORK_ACL_ADMIN.check_privilege('ACL_PERMISSION.xml', 'ARVIND', 'connect'),
         1, 'GRANTED', 0, 'DENIED', NULL) privilege
FROM dual;

PRIVILEGE
---------------------
GRANTED

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30

SQL> SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;.

ACL                            PRINCIPAL                      PRIVILEGE             IS_GRANT        START_DATE                        END_DATE
------------------------------ ------------------------------ --------------------- --------------- --------------------------------- ---------------------------------
/sys/acls/ACL_PERMISSION.xml         ARVIND                       connect               true

Once you got output like above queries. You can test you procedure to send mail, it will work.
As we have create ACL for ACL_PERMISSION.xml we can create for utl_mail.xml and utl_tcp.xml after creation of both these ACL's output of above query will be like below :-

ACL                            PRINCIPAL                      PRIVILEGE             IS_GRANT        START_DATE                        END_DATE
------------------------------ ------------------------------ --------------------- --------------- --------------------------------- ---------------------------------
/sys/acls/utl_mail.xml          ARVIND                         connect               true
/sys/acls/ACL_PERMISSION.xml         ARVIND                         connect               true
/sys/acls/utl_tcp.xml           ARVIND                         connect               true

TO enable this ACL permission for a different USER other than above user(ARVIND)  :-

Connect with TEST user and run below mentioned :-

begin
DBMS_NETWORK_ACL_ADMIN.add_privilege (
acl => 'ACL_PERMISSION.xml',
principal => 'TEST',
is_grant => FALSE,
privilege => 'connect');
commit;
end;
/

PL/SQL procedure successfully completed.

begin
dbms_network_acl_admin.assign_acl (
acl => 'ACL_PERMISSION.xml',
host => '100.02.03.04',
lower_port => 25);
end;
/

PL/SQL procedure successfully completed.

SQL> COLUMN acl FORMAT A30
SQL> COLUMN principal FORMAT A30

SQL> SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;.

ACL                            PRINCIPAL                      PRIVILEGE             IS_GRANT        START_DATE                        END_DATE
------------------------------ ------------------------------ --------------------- --------------- --------------------------------- ---------------------------------
/sys/acls/ACL_PERMISSION.xml         ARVIND                       connect               true
/sys/acls/ACL_PERMISSION.xml         TEST                       connect               false

-- This query has to be run with TEST user and Status column should have value "GRANTED"

SQL> SELECT host, lower_port, upper_port, privilege, status FROM   user_network_acl_privileges;

HOST                                                        LOWER_PORT UPPER_PORT PRIVILEGE             STATUS
---------------------------------------------       ----------------- ---------------- --------------------- ---------------------
100.02.03.04                                             25                    25               connect               GRANTED
<your smtp server host name or address>                                            connect               GRANTED
black                                                                                                  connect               GRANTED

Thursday, March 20, 2014

ORA-01111: name for data file 129 is unknown - rename to correct file

Recently i was working on moving some data file on the primary server and i have set the standby_file_management='MANUAL'. and  then I saw the following error on my standby server.



ALTER DATABASE RECOVER managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (ORCL)
Thu Mar 20 16:07:33 2014
MRP0 started with pid=35, OS id=22622
MRP0: Background Managed Standby Recovery process started (ORCL)
started logmerger process
Thu Mar 20 16:07:38 2014
Managed Standby Recovery not using Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/ORCL/ORCL/trace/ORCL_pr00_22624.trc:
ORA-01111: name for data file 214 is unknown - rename to correct file
ORA-01110: data file 214: '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00214'
ORA-01157: cannot identify/lock data file 214 - see DBWR trace file
ORA-01111: name for data file 214 is unknown - rename to correct file
ORA-01110: data file 214: '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00214'
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (ORCL)
Completed: ALTER DATABASE RECOVER managed standby database disconnect from session

Here  is what I did to resolve it:

 STEP1:- Check the name of the datafile on standby database

select name from v$datafile; ---- This command should show the datafile name as UNNAMEDxxxxx

STEP2:- ON STANDBY DATABASE
alter system set standby_file_management='manual';

STEP3:- Rename the datafile
alter database create datafile 'C:\ORACLE\ORA10G\DATABASE\UNNAMED00129' as 'C:\ORACLE\ORA10G\DATABASE\actual_dbfile_name.dbf'


STEP4:- On the standby database
alter system set standby_file_management='auto';

STEP5:- On the standby database
recover managed standby database disconnect;

shutdown and then reopen the standby database and continue applying the redo logs


sample :
SQL> alter system set standby_file_management='manual';

System altered.

SQL> alter database create datafile '/u01/app/oracle/product/11.2/db_1/dbs/UNNAMED00214' as '/u03/oracle/oradata/ORCL/datafiles/auditdata03.dbf';

and now start archive log application.
SQL> recover managed standby database disconnect from session;
Media recovery complete.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

 /u03/oracle/oradata/ORCL/datafiles/ORCL_data118.dbf
/u03/oracle/oradata/ORCL/datafiles/ORCL_data119.dbf
/u03/oracle/oradata/ORCL/datafiles/auditdata03.dbf





Script to delete archive logs on standby server (oracle)

Frist Create file vi rman_script.sh

then add the following lines in rman_script.sh file

#!/bin/bash
###################################################################
#  this script will delete the archive and obsolete file on standby server  ##########
###################################################################

ORACLE_HOME=/u01/app/oracle/product/11.2/db_1;
export ORACLE_HOME;
ORACLE_SID=ORCL;
export ORACLE_SID;

rman msglog " now.log " <<EOF
connect target /;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO BACKUPSET;
run {
CROSSCHECK BACKUP;
DELETE NOPROMPT OBSOLETE;
DELETE NOPROMPT EXPIRED BACKUP;
delete noprompt archivelog until time 'SYSDATE-7';
crosscheck archivelog all;
}
Exit;
EOF


Note:- It will delete all the archive of 7 days back. (SYSDATE-7) .
then save it and exit
then give the executing permission to rman_script.sh
chmod 777 rman_script.sh
now run the command 
crontab -e
and add the file in crontab ( Automatically Running Job )
00 6 * * *  "/u01/app/oracle/product/rman_script.sh"

Monday, March 17, 2014

Count All the Rows in all tables in a schema

Count All the Rows in all tables in a schema

 you can do it various options

Option 1. 


you can write a sql to get the out output an the then run the output to get the count for all tables .


select 'select count (*) as '||owner||'_'||table_name||' from ' ||owner||'.'||table_name||';' from all_tables tables where owner='XXX' order by 1;


Then run output manually:

select count (*) as USER1_ASP_AGREEMENT_SCHEDULE from USER1.ASP_AGREEMENT_SCHEDULE;
select count (*) as USER2_ASP_CITI_USER_ACCOUNT from USER2.USER_ACCOUNT;



Option 2. 

 This simple sql will get you all the rows count for all tables in schemas that you want.



SELECT OWNER,table_name, num_rows counter from DBA_TABLES WHERE owner in 
('USER1','USER2') ORDER BY 1,2;

sample output :

select count(*) from USER1.QP_INFO;
select count(*) from
USER1.REFUND_ENTRY;

 Option 3.


1. Using DBA_TABLES+
You can gather the Database statistics using DBMS_STATS.GATHER_DATABASE_STATS; And then simply Query DBA_TABLES to sum the NUM_ROWS column grouping on OWNER column. This will yeild you the rowcounts of each of schemas.

2. Count Rows by Query+
You can gather the Table rowcount, without using DBMS_STATS, using the below script: 


select table_name,
   to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) X from '||table_name))
              ,'/ROWSET/ROW/X')) count
    from dba_tables
  where owner = 'owner'


Purging a Database Audit Trail AUD$

  1. Purging a Subset of Records from the Database Audit Trail

    You can manually remove records from the database audit trail tables. This method can be useful if you want to remove a specific subset of records. You can use this method if the database audit trail table is in any tablespace, including the SYSTEM tablespace.
    For example, to delete audit records that were created later than the evening of February 28, 2009 but before March 28, 2009, enter the following statement:
    DELETE FROM SYS.AUD$
       WHERE NTIMESTAMP# > TO_TIMESTAMP ('28-FEB-09 09.07.59.907000 PM') AND
       NTIMESTAMP# < TO_TIMESTAMP ('28-MAR-09 09.07.59.907000 PM');
    
    Alternatively, to delete all audit records from the audit trail, enter the following statement:
    DELETE FROM SYS.AUD$;
    
    Only the user SYS or a user to whom SYS granted the DELETE privilege on SYS.AUD$ can delete records from the database audit trail.
    Note:
    If the audit trail is full and connections are being audited (that is, if the AUDIT SESSION statement is set), then typical users cannot connect to the database because the associated audit record for the connection cannot be inserted into the audit trail. In this case, connect as SYS with the SYSDBA privilege, and make space available in the audit trail. Remember that operations by SYS are not recorded in the standard audit trail, but they are audited if you set the AUDIT_SYS_OPERATIONS parameter to TRUE.
    After you delete the rows from the database audit trail table, the freed space is available for reuse by that table. (The SYS.AUD$ table is allocated only as many extents as are necessary to maintain current audit trail records.) You do not need to do anything to make this space available to the table for reuse. If you want to use this space for another table, then follow these steps:


    Move the AUD$ table to an auto segment space managed tablespace.
    For example:
    BEGIN
      DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
       (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
       audit_trail_location_value => 'USERS');
    END;
    /
     
  2. Run the following statements:
    ALTER TABLE SYSTEM.AUD$ ENABLE ROW MOVEMENT;
    ALTER TABLE SYSTEM.AUD$ SHRINK SPACE CASCADE;
    
  3. If you must move the AUD$ table back to the SYSTEM tablespace, then run the following statement:
    BEGIN
     DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
      (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
      audit_trail_location_value => 'SYSTEM');
    END;
    /
    
If you want to both delete all the rows from the database audit trail table and free the used space for other tablespace objects, use the TRUNCATE TABLE statement. For example:
TRUNCATE TABLE SYS.AUD$;
Note:
SYS.AUD$ and SYS.FGA_LOG$ are the only SYS objects that can ever be directly modified.
 
 
Reference :
http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#DBSEG473
http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php
 

Thursday, March 6, 2014

Create a sample schema with 1millions rows in Oracle

We always to test the database for various reasons we will need to create a tables 
with a lot of data in it.We can use below script that will allow us to create a table
and populate the table with 10,00,000 rows with simple plsql block.
This is just for demo purpose and have been tested. 
 
-- Create and populate a small table.
 
 CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);

INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT; 
 
 
 
 
 
 -- here we will create and populate a larger table that we will later partition.
 CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
);

DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 1000000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;
    
    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/

-- Lets apply some constraints to the table.
ALTER TABLE big_table ADD (
  CONSTRAINT big_table_pk PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i ON big_table(created_date);

CREATE INDEX bita_look_fk_i ON big_table(lookup_id);

ALTER TABLE big_table ADD (
  CONSTRAINT bita_look_fk
  FOREIGN KEY (lookup_id)
  REFERENCES lookup(id)
);
 
 
Note : If you find any data on this site irrelavant or copy right issues 
please let me know . I will delete the content. 
This post is just for education purpose.