Oracle Flashback Version Query and Oracle Flashback Transaction Query

Oracle Flashback Version Query and Oracle Flashback Transaction Query
====================================================================
No need to db archive mode , recyclebin on


You are informed that the record empno=7499 is missing from
the scott.EMP table. You need to identify the following:

delete from emp where empno=7499;
commit ;

* The transaction identifier of the transaction that deleted the empno record
* The SQL statements necessary to undo the delete
* The user who executed the transaction

Which would you use?


A. Oracle Flashback Drop only
B. Oracle Flashback Version Query only
C. Oracle Flashback Version Query and Oracle Flashback Transaction Query
D. RMAN REPORT command only

Ans : C

Practical example :
-----------------

1. --SHOW all rows of emp table

select * from emp

2. --Delete one row

delete from emp where empno=7499

3. --commit the change

commit

4.----Oracle Flashback Version Query to find out the identified (history) of the transaction.

SELECT versions_xid, versions_operation
FROM emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE empno=7499;


5.---Oracle Flashback Transaction Query to see the all information (audit) as well as undo sql


SELECT XID, COMMIT_TIMESTAMP, LOGON_USER,OPERATION, TABLE_NAME, TABLE_OWNER, UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid=HEXTORAW('09002700CE180000');

Oracle Database Security Checklist

Oracle Database Security Checklist
==================================
For a production Database, must need to check the following points for
better security.



1. Protecting the database environment.............................................................
2. Install only what is required..........................................................................
3. Lock and expire default user accounts...........................................................
4. Changing default user passwords...................................................................
5. Change passwords for administrative accounts.............................................
6. Change default passwords for all users...........................................................
7. Enforce password management......................................................................
8. Secure batch jobs............................................................................................
9. Manage access to SYSDBA and SYSOPER roles..........................................
10. Enable Oracle data dictionary protection......................................................
11. Follow the principle of least privilege.............................................................
12. Public privileges..............................................................................................
13. Restrict permissions on run-time facilities......................................................
14. Authenticate clients........................................................................................
15. Restrict operating system access.....................................................................
16. Secure the Oracle listener..............................................................................
17. Secure external procedures.............................................................................
18. Prevent runtime changes to listener................................................................
19. Checking network IP addresses......................................................................
20. Harden the operating system.........................................................................
21. Encrypt network traffic..................................................................................
22. Apply all security patches...............................................................................
23. Report security issues to Oracle....................................................................

ORA-01536: space quota exceeded for tablespace

ORA-01536: space quota exceeded for tablespace


1. TO FIND OUT THE QUOTA FOR A USER ON THAT TABLESPACE
USE FOLLOWING QUERY:-


SELECT *
FROM dba_ts_quotas
WHERE USERNAME='ISLBAS'
AND TABLESPACE_NAME='ISLSYS'



2. Increase the quota for that user by this following command



ALTER USER ISLBAS QUOTA UNLIMITED ON ISLSYS;



OR
==

another cause may happen this error

this cause is :-
your are working on a table which owned by another user
so first need to find out that user. then give the owner user
quota unlimited to the tablespace .


For this :- you have to do

1.

select NAME,TYPE
from dba_dependencies
where REFERENCED_NAME='STFACMAS';



2.

select OWNER,OBJECT_NAME
from dba_objects
where OBJECT_NAME='DBT_STFACMAS_CD_CURBAL';


3.

grant unlimited tablespace to ISLBAS;

Index Monitoring whether they are being used

Index Monitoring whether they are being used
================================================
Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.

To start monitoring the usage of an index, issue this statement:

ALTER INDEX index MONITORING USAGE;

Later, issue the following statement to stop the monitoring:

ALTER INDEX index NOMONITORING USAGE;

The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored. The view also contains the start and stop times of the monitoring period, and a MONITORING column (YES/NO) to indicate if usage monitoring is currently active.

Each time that you specify MONITORING USAGE, the V$OBJECT_USAGE view is reset for the specified index. The previous usage information is cleared or reset, and a new start time is recorded. When you specify NOMONITORING USAGE, no further monitoring is performed, and the end time is recorded for the monitoring period. Until the next ALTER INDEX...MONITORING USAGE statement is issued, the view information is left unchanged.


Example:

ALTER INDEX STFACMAS_IDX1 MONITORING USAGE;


select * from V$OBJECT_USAGE ;


select * from stfacmas
where crdnum ='5127724201336015';


select * from V$OBJECT_USAGE ;

Date Format in oracle

Date Formats of Oracle Language
==============================
==============================


Format mask Description
========== ==============
CC : Century
SCC : Century BC prefixed with -
YYYY :Year with 4 numbers
SYYY :Year BC prefixed with -
IYYY :ISO Year with 4 numbers
YY :Year with 2 numbers
RR :Year with 2 numbers with Y2k compatibility
YEAR :Year in characters
SYEAR :Year in characters, BC prefixed with -
BC :BC/AD Indicator *
Q :Quarter in numbers (1,2,3,4)
MM :Month of year 01, 02...12
MONTH :Month in characters (i.e. January)
MON :JAN, FEB
WW :Weeknumber (i.e. 1)
W :Weeknumber of the month (i.e. 5)
IW :Weeknumber of the year in ISO standard.
DDD :Day of year in numbers (i.e. 365)
DD :Day of the month in numbers (i.e. 28)
D :Day of week in numbers(i.e. 7)
DAY :Day of the week in characters (i.e. Monday)
FMDAY :Day of the week in characters (i.e. Monday)
DY :Day of the week in short character description (i.e. SUN)
J :Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH :Hournumber of the day (1-12)
HH12 :Hournumber of the day (1-12)
HH24 :Hournumber of the day with 24Hours notation (0-23)
AM :AM or PM
PM :AM or PM
MI :Number of minutes (i.e. 59)
SS :Number of seconds (i.e. 59)
SSSSS :Number of seconds this day.
DS :Short date format. Depends on NLS-settings. Use only with timestamp.
DL :Long date format. Depends on NLS-settings. Use only with timestamp.
E :Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE :The full era name
FF :The fractional seconds. Use with timestamp.
FF1..FF9 ;The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM :Fill Mode: suppresses blianks in output from conversion
FX :Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I :the last 3,2,1 digits of the ISO standard year. Output only
RM :The Roman numeral representation of the month (I .. XII)
RR :The last 2 digits of the year.
RRRR :The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC :Century. BC dates are prefixed with a minus.
CC :Century
SP :Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH :Spelled and ordinal format; 1 results in first.
TH :Converts a number to it's ordinal format. For example 1 becoms 1st.
TS :Short time format. Depends on NLS-settings. Use only with timestamp.
TZD :Abbreviated time zone name. ie PST.
TZH :Time zone hour displacement.
TZM :Time zone minute displacement.
TZR :Time zone region
X :Local radix character. In america this is a period (.)

================= ==== ====================================

Some Examples:
-----------------------

SQL>
SQL>
SQL> select to_char(sysdate,'CC') from dual;

TO
--
21

SQL>
SQL> select to_char(sysdate,'YYYY') from dual;

TO_C
----
2010

SQL>
SQL> select to_char(sysdate,'YEAR') from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWENTY TEN

SQL>
SQL> select to_char(sysdate,'MONTH') from dual;

TO_CHAR(S
---------
JUNE

SQL>
SQL> select to_char(sysdate,'BC') from dual;

TO
--
AD

SQL>
SQL> select to_char(sysdate,'RM') from dual;

TO_C
----
VI

SQL>
SQL> select to_char(sysdate,'Q') from dual;

T
-
2

SQL>