SQL> show parameter audit_trail NAME TYPE VALUE ———————————— ———– —— audit_trail string DB SQL> AUDIT ALTER ANY OUTLINE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER ANY ROLE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER DATABASE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER SEQUENCE BY ACCESS; Audit succeeded. SQL> AUDIT ALTER …
col OS_USERNAME for a10; col USERNAME for a10; col TIMESTAMP for a10; col OWNER for a10; col OBJ_NAME for a10; col ACTION for a20; select OS_USERNAME,USERNAME,TIMESTAMP,OWNER,OBJ_NAME,ACTION from dba_audit_trail order by TIMESTAMP; select Min(NTIMESTAMP#) “Last AUD$ Purge Date ” from sys.aud$ where OBJ$NAME !=’AUD$’; SQL> select min(timestamp#)from sys.aud$; MIN(NTIMESTAMP#) —————————————— 15-SEP-09 …
Auditing is the monitoring and recording of selected user database actions. When you use standard auditing, Oracle Database writes the audit records to either to DBA_AUDIT_TRAIL (the SYS.AUD$ Table), the operating system audit trail, or to the DBA_COMMON_AUDIT_TRAIL view, which combines standard and fine-grained audit log records.In addition, the actions …
There many decent Technology Blogs in the web (Not only TechDoubts.com :)) .But are you still facing difficulty in finding the right technology blogs ? Search the website Technorati.com, which contains a large blog directory on Tech Blogs and that will certainly help you. 3NKU4KUR9YGB
Oracle will not allow to drop the default temporary tablespace from a database. We have to assign a new default temp tablespace for the database to drop the current tablespace. Suppose we need to drop and recreate the default temporary tablespace TEMP. Step 1: Create a new temporary tablespace temp1 …
How to check the database/instance name without having access to data dictionary views? How to get the client machine details from which I have connected to the Oracle database? How can I know my session details? These questions might have come up in the mind of many oracle database users …
Sometimes we need to create a user similar to another user or we need to recreate a user in another database.In those situations,Oracle supplied package DBMS_METADATA can be used to generate the user creation DDL. >> To generate the User creation script SQL> SELECT dbms_metadata.get_ddl(‘USER’,’TEST’) FROM dual; DBMS_METADATA.GET_DDL(‘USER’,’TEST’) …
Oracle provides no direct method to change the owner of a table. But we often face such situations to change the schema owner of the table. We have some work around to satisfy this requirement. Lets see them. 1. Export the table and import it into the new schema using …
Most of the time we are skeptical of the index usage. Though the index is created for the table, we may not be able to determine if it is actually used. Inorder to determine if the index is used, we can use the MONITORING USAGE clause in the alter index …
Oracle 11g offers a new feature called Invisible Indexes by which an index can be marked as invisible by the Oracle. Once an index is set as invisible, Oracle optimizer no longer considers it. This is a very useful feature where we do not want the index to take effect …
Active Session History (ASH) is a quick solution to do performance analysis of the Oracle session based on time, module, action or SQL_ID and also to identify transient performance problem that lasts for few minutes. Each active session running in the database will be sampled (divided into smaller units) and the …
Active Session History (ASH) is a very important feature to analyze sessions in Oracle to troubleshoot transient problems that last for just few minutes. ASH reports gives the details needed to analyze the sessions. Below example shows how to generate ASH(Active Session History) reports from SQLPLUS. The same can be done using …
DBMS_JOB package is used to manage and schedule job in Oracle. Given below is a simple example with detailed steps which illustrates DBMS_JOB. The requirement is to insert employee details into the table EMPLOYEES. When the job is invoked the details would be inserted. The details needs to passed as …
Join is a method used to combine two or more tables,views or materialized views based on a common condition. There is wide variety of classification. Below given in one such classification. Different types of Joins 1. Cross Join/Cartesian Product 2. Natural Join 3. Inner Join 4. Outer join a.Left Outer …
Among the many enhancements in Oracle 11g, very important one is the default auditing.In Oracle 11g, parameter audit_trail is set to the value DB by default. And many privileges audited by access are enabled by default.See the list below. ALTER ANY PROCEDURE ALTER ANY TABLE ALTER DATABASE ALTER PROFILE AUDIT ROLE …
Temporary tablepspace is a very dynamic tablespace which gets filled up quickly, due to SQL operations that inludes sorting like CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS. Though this tablespace is not backed up or used for recovery, this is a very important tablespace for …