A database consists of an organized collection of data for one or more uses, typically in digital form. One way of classifying databases involves the type of their contents, for example: bibliographic, document-text, statistical. Digital databases are managed using database management systems, which store database contents, allowing data creation and maintenance, and search and other access.

In this project i will be uploading and try to create new projects such as scripts and database
------------------------------------------------------------------------------------------------------------


NOTE for every Task:
You have to create the tables and have to populate with the data required.
My work just includes the Queries to process the results/Output.


Task1:
1. @Task-1.sql

Reference:
1. http://oracletoday.blogspot.com/2005/11/bulk-collect_15.html
2. http://www.java2s.com/Code/Oracle/Cursor/FETCHcursorBULKCOLLECT.htm
3. http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html
4. http://www.oracle-training.cc/teasyoracleplsqlbulkcollectionoperation.htm



Task2:
1. @Task2-1.sql
2. @Task2-2.sql
3. @Task2-3.sql

Notes:

In Task 2.2:
If you are testing with the new user who is SYSDBA or not you have to run:

ALTER SESSION SET nlsdateformat = 'DD-MM-YYYY HH24:MI:SS';

Whenever you open SQLPLUS for the next session.

In Task2.3
I had provided the two types of output one is the normal format and another UserFriendly Format.
References:
1. http://www.dba-oracle.com/artbuildersec_audit.htm
2. http://download.oracle.com/docs/cd/B2835901/appdev.111/b28843/tdddgtriggers.htm#BABFBEJH



Task3:
1. @Task3-1.sql
2. @Task3-2.sql

Notes:
In Task3.1:
I had used "exec usersfreespace;" in order to confirm the success of the output and thinking that it could be required.
In Task3.2:
If the ProgramName, ProgramSCHEDULE and CREATE_JOB names are in use then you have to first delete those Programs names and then rum the script.

Reference:
1. http://www.oradev.com/dbms_scheduler.jsp
2. http://apex-at-work.blogspot.com/2009/06/dbmsscheduler-examples.html


Task4:
1. @Task4.sql

Reference:
1. http://www.dbasupport.com/forums/archive/index.php/t-40168.html



Task5:
1. @Task5-1.sql
2. @Task5-2-1.sql
3. @Task5-2-2.sql
4. @Task5-3.sql

Note:
In Task 5-1 it will work with the Oracle database internal table such as “MGMT_JOB”

Reference:
1. Book: Informatics Practices by Sumita Arora (Dhanpat Rai Publications)
2. http://www.oracle-base.com/articles/8i/CostBasedOptimizerAndDatabaseStatistics.php



Task6:
Notes:
1. Login as dbastudent
2. @Task6-1.sql
3. @Task6-2.sql
4. Select * from employee;
5. @Task6-2Remove.sql;

* If you are using schema DBASTUDENT then its fine. But if you want to try with some another user then you have to change in the object_schema to the user you want to apply the policy.

Reference:
1. http://books.google.com.au/books?id=cuNZMN4703sC&pg=PA227&lpg=PA227&dq=droprlspolicy+oracle&source=bl&ots=zJBHyN57Bs&sig=vZ8i6RmMfk6htY- 6vJgga0yWSp8&hl=en&ei=sIfNSouEIsaPkQWLhrnuAw&sa=X&oi=bookresult&ct=result&resnum=2#v=onepage&q=rls.droppolicy&f =false
2. http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/
3. http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/vpd.htm#i1006406



Task7:
Note:
1. Login as Oracle User.
2. Run the query, “Create User “01” IDENTIFIED BY qwe;
3. Grant CONNECT TO “01”;
4. GRANT RESOURCE TO “01”;
5. @TASK7-1
6. @TASK7-2
7. Come back to the dbastudent user and run the query as:
GRANT SELECT ON employee to “01”;
8. Login to the Oracle as user 01 and password qwe.
9. Run the query as: SELECT * FROM dbastudent.employee;
10. @Task7-2Remove.sql

* If you are using schema DBASTUDENT then its fine. But if you want to try with some another user then you have to change in the object_schema to the user you want to apply the policy.

Reference:
1. http://books.google.com.au/books?id=cuNZMN4703sC&pg=PA227&lpg=PA227&dq=droprlspolicy+oracle&source=bl&ots=zJBHyN57Bs&sig=vZ8i6RmMfk6htY- 6vJgga0yWSp8&hl=en&ei=sIfNSouEIsaPkQWLhrnuAw&sa=X&oi=bookresult&ct=result&resnum=2#v=onepage&q=rls.droppolicy&f=false
2. http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/
3. http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/vpd.htm#i1006406

Last edited Oct 7, 2010 at 8:55 AM by pgargaupp, version 2