Thursday, January 27, 2011

flashback tables

to flashback update

select * from v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;


alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

begin
dbms_logmnr.start_logmnr
(
starttime => '27-JAN-2011 11:25:10',
endtime => '27-JAN-2011 11:28:35',
options => dbms_logmnr.dict_from_online_catalog +
dbms_logmnr.continuous_mine +
dbms_logmnr.no_sql_delimiter +
dbms_logmnr.print_pretty_sql
);
end;
/

select seg_name ,seg_owner ,username, xid , operation , sql_redo
from v$logmnr_contents
where seg_owner = 'CCARE'
and username = 'DBAHAKKI';
--where seg_name like 'ACC' ;


-- from sys user

execute dbms_logmnr.end_logmnr;

begin
dbms_flashback.TRANSACTION_BACKOUT
(
numtxns => 1 ,
xids => xid_array('01001900A70C0000'),
options => DBMS_FLASHBACK.CASCADE
);
end;

/


--to flashback delete
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);


insert into table_name
select * from table_name as of timestamp to_timestamp(‘2011-01-19 16:00:00’,’yyyy-mm-dd hh24:mi:ss’);
SQL>commit;

Monday, January 17, 2011

how to find what is locking my table

1-

SELECT LPAD(' ',DECODE(l.xidusn,0,3,0)) || l.oracle_username "User Name",o.owner, o.object_name, o.object_type
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id
ORDER BY o.object_id, 1 desc


2-

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id

3-

select  oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id,  owner || '.' || object_name object,object_type
,  decode( l.block,0, 'Not Blocking',1, 'Blocking',2, 'Global') status
,  decode(v.locked_mode,0, 'None',1, 'Null',2, 'Row-S (SS)',3, 'Row-X (SX)',4, 'Share',5, 'S/Row-X (SSX)',6, 'Exclusive'
, TO_CHAR(lmode)) mode_held
from       v$locked_object v,  dba_objects d,  v$lock l,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,  session_id


to kill the session
ALTER SYSTEM KILL SESSION 'sid,serial#';

Tuesday, January 4, 2011

Case Sensitive Passwords in Oracle Database 11g

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL>


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL>

Monday, January 3, 2011

Temporary Tablespace Groups

Temporary Tablespace Groups
Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:

-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;The tablespaces assigned to a group can be viewed using:

SELECT * FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2

2 rows selected.Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:

-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;

-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;A tablespace can be removed from a group using:

ALTER TABLESPACE temp2 TABLESPACE GROUP '';

SELECT * FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP

1 row selected.There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';

-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;

no rows selected