Database Administrator: August 2012

Wednesday, August 29, 2012

Oracle 11g Advanced Compression

You can implement advanced compression option into your databases having more update, insert operation tables to manage high growing amount of data.

Followings are very simple command to implement advanced compression

alter table tablename compress for all operations;

alter index index1 rebuild compress;

alter index index2 rebuild compress;

SELECT table_name, compression, compress_for FROM user_tables where table_name='tablename';

select index_name,COMPRESSION,STATUS from dba_indexes where ='tablename';

Satish,

Please implement the same on UAT first to get performance.

Following is test case to show the difference between 10gR2 Table Compression feature and 11gR2’s Advanced Compression. Oracle provided table level compression feature in 10gR2. While this compression provided some storage reduction, 10g’s table compression only compressed the data during BULK LOAD operations. New and updated data were not compressed.

With 11g’s Advanced Compression new and updated data are also compressed; achieving highest level in storage reduction, while providing performance improvements as compressed blocks result in more data being moved per I/O.

Note1: Basic compression comes with oracle 11g Enterprise Edition, To make table as OLTP compressed its again extra cost (US$11,500.00/ Processor) perpetual option with Enterprise Edition.

Note2: There is tradeoff between Disk IO and CPU. it depends on how your system is configured. If your performance bottleneck is disk I/O, you almost certainly will benefit from using compression, because it saves a lot of disk reads. If you are on the other hand low on CPU, you might not always.

------------------------------Test Case-----------------------------------------------

Following test case was executed in 10g database server.

A table called TEST was created without COMPRESSION option.

SQL> select table_name,compression from dba_tables where table_name = 'TEST';

TABLE_NAME COMPRESS
------------------------- -------------
TEST DISABLED

SQL> select bytes from dba_segments where segment_name = 'TEST';

SUM(BYTES)
------------------
92274688

The size of the table was around 92MB.

Now create another table called TEST_COMPRESSED with COMPRESS option.

SQL> create table TEST_COMPRESSED COMPRESS as select * from test;

Table created.

SQL> select table_name, compression from dba_tables where table_name like 'TEST
%';

TABLE_NAME COMPRESS
------------------------------ ---------------
TEST_COMPRESSED ENABLED
TEST DISABLED

Now let’s check the size of the COMPRESSED table.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

SUM(BYTES)
----------------
30408704

Check out the size of the COMPRESSED table. It is only 30MB, around 30% reduction in size. So far so good.

Now let’s do a plain insert into the COMPRESSED table.

SQL> insert into TEST_COMPRESSED select * from TEST;

805040 rows created.

SQL> commit;

Commit complete.

Let’s check the size of the COMPRESSED table.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED'

2 /

SUM(BYTES)
----------
117440512

Wow! From 30MB to 117MB? So, plain INSERT statement does not COMPRESS the data in 10g.

(You will see this is not the case with 11g)

Now let’s do the same insert with a BULK LOAD

SQL> insert /*+ APPEND */ into TEST_COMPRESSED select * from TEST;

805040 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

SUM(BYTES)
----------
142606336

Ok, now the size of the COMPRESSED table is 142MB from 117MB. For the same number of rows, the table size only increased by 25MB. So BULK LOAD compresses the data.

Let’s check other DML statements such as DELETE and UPDATE against the COMPRESSED table.

SQL> delete from test_compressed where rownum < 100000;

99999 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

SUM(BYTES)
----------
142606336

No change in total size of the table. DELETE has no impact as expected.

Let’s check UPDATE.

SQL> update test_compressed set object_name = 'XXXXXXXXXXXXXXXXXXXXXXXXXX' where
rownum < 100000;

99999 rows updated.

SQL> commit;

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

SUM(BYTES)
----------
150994944

The table size is increased by 8MB? No compression for UPDATE statement either.

All this clearly shows that 10g’s Table COMPRESSION would work great for initial BULK LOADS, however subsequent UPDATE’s, DELETE’s and INSERT’s will not result in COMPRESSED blocks.

Now, let’s see 11g’s Test Results.

The following SQL statements were executed against 11.2.0.1 database version.

TEST table of 100MB in size was created as before.

SQL> select bytes from dba_segments where segment_name = 'TEST';

BYTES
----------
100663296

So 100MB of table created.

Let’s create a table with COMPRESS FOR ALL OPERATIONS option. This is only available in 11g.

SQL> create table test_compressed compress for all operations as select * from
test;

Table created.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

BYTES
----------
31457280

Check out the size of the compressed table vs. uncompressed table. 30% less space usage on a compressed table. Not a big difference compared to 10g.

Let’s check other DML statements.

Let’s do a plain insert to the compressed table.

SQL> insert into TEST_COMPRESSED select * from test;

789757 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

BYTES
----------
75497472

11g’s Advanced compression, compressed 100MB of data to 40MB and inserted to the compressed table, WITHOUT BULK LOAD option.

Now let’s do the BULK LOAD onto 11g’s COMPRESSED table.

SQL> insert into /*+ APPEND */ test_compressed select * from TEST;

789757 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

BYTES
----------
109051904
It has a same impact as PLAIN insert.

What about deletes and updates?

SQL> delete from test_compressed where rownum < 100000;

99999 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

BYTES
----------
109051904

No change in deletes. This is expected as the blocks are compressed when the new rows are added to the existing blocks and that the threshold reaches PCTFREE.

SQL> update test_compressed set object_name = 'XXXXXXXXXXXXXXXXXXXXXXXXXX' where

2 rownum < 100000;

99999 rows updated.

SQL> commit;

Commit complete.

SQL> select bytes from dba_segments where segment_name = 'TEST_COMPRESSED';

BYTES
----------
109051904

There is no change in this case as existing blocks were able to accommodate updates. However the same update generated more data in 10g.

Saturday, August 25, 2012

CONSTRAINT ERROR Find What data creating problem

d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\admin\utlexcpt.sql

create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));

if someone disable the constraint and try to enable, it show error exceptions table stores the table and rowid

Alter Table <table_name> Enable constraint <ck_constriantname> exceptions into exceptions;

Friday, August 24, 2012

RESTRICT NON DBA USER TO ACCESS DEVELOPMENT TOOLS LIKE SQLPLUS , TOAD,...

Create or Replace TRIGGER TG_OTHER_TOOL
v_prog sys.v_$session.program%TYPE;
v_module sys.v_$session.program%TYPE ;
lnUsercode number ;
lcUserdetails Varchar2(100) ;
lnTotalrecords number ;
Begin
Select program,module into v_prog,v_module From sys.v_$session
Where audsid = USERENV('SESSIONID')
and audsid != 0 -- Don't Check SYS Connections
and rownum = 1; -- Parallel processes will have the same AUDSID's
Select Count(*) into lnTotalrecords From Dba_Users Dba_Users
Where Dba_Users.Username=User and Dba_Users.Default_tablespace='USR' ;
If lnTotalrecords!=0 then
IF UPPER(V_MODULE) NOT IN ('LD.EXE','LDSCHEDULER.EXE','LDSCHEDULER.VSHOST.EXE','DBMS_SCHEDULER','DLLHOST.EXE')
Then
Raise_application_error(-20000,'On Database Development tools are Restricted');
end if;
end if ;
lnUsercode:=-1;
If ((upper(v_prog)='LD.EXE' and upper(v_module)='LD.EXE') or
(upper(v_prog)='LDSCHEDULER.EXE' and upper(v_module)='LDSCHEDULER.EXE') or
(upper(v_prog)='LDSCHEDULER.VSHOST.EXE' and upper(v_module)='LDSCHEDULER.VSHOST.EXE') or
(upper(v_prog)='ORACLE.EXE (J000)' and upper(v_module)='DBMS_SCHEDULER') or
(upper(v_prog)='DBMS_SCHEDULER' and upper(v_module)='DBMS_SCHEDULER') or
(upper(v_prog)='DLLHOST.EXE' and upper(v_module)='DLLHOST.EXE')) then
lcUserdetails:=sys_context('userenv','ip_address') ;
Insert into tbltempoperationstatistics (nOpercode,cOperationname) Values (lnUsercode,lcUserdetails) ;
else
lcUserdetails:='USER LOGGED ON' ;
Insert into tbltempoperationstatistics (nOpercode,cOperationname) Values (lnUsercode,lcUserdetails) ;
end if ;
Commit ;
Exception
When NO_DATA_FOUND then
NULL ;
End;
/

if Non DBA user is trying to login into sqlplusw / PlSQL developer or some other tool then it shows error because of splogininformation,tglogin.

if i rename sqlplusw.exe to abc.exe then non DBA user can login into sqlplusw.exe

i think you check v$session.program in SP for that.

you have to check v$session.module.

select program,module from v$session;

abc.exe SQL*Plus

module shows currently executing module

program shows operating system program name

The MODULE column of V$SESSION only gets populated with the DBMS_APPICATION_INFO package.

SQL*Plus automatically uses this package and registers the application with the database.

This is why you can see it in the MODULE column of V$SESSION. Unfortunately, this won't happen until near the end of the SQL*Plus initialization.

You can not set_module before logging. For obvious reason - you are not logged

http://docs.oracle.com/cd/A58617_01/server.804/a53717/ape.htm

PRODUCT_USER_PROFILE is owned by SYSTEM not SYS.

PRODUCT_USER_PROFILE is only work for SQLPLUSW not for other tool

One alternative is to make use of PRODUCT_USER_PROFILE table to restrict the nondba users from firing any command after logging in.

e.g to disallow nondba users from selecting any data from SQL * plus prompt, log in as system and

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','SELECT','DISABLED');

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','UPDATE','DISABLED');

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','DELETE','DISABLED');

insert into product_user_profile (product,userid,attribute,char_value)values ('SQL*Plus','%','INSERT','DISABLED');

commit;

Oracle alert.log to Table

The following script creates two tables: read_alert andread_alert_disk.

read_alert_disk is an external table and contains the content of the alert log.

read_alert will be empty after this script has been executed. It is used by the update_alert_log script, shown further below.

define alert_length="2000"

drop table alert_log;

create table alert_log (
  alert_date date,
  alert_text varchar2(&&alert_length)
)
storage (initial 512k next 512K pctincrease 0);

create index alert_log_idx on alert_log(alert_date)
storage (initial 512k next 512K pctincrease 0);

column db    new_value _DB    noprint;
column bdump new_value _bdump noprint;

select instance_name db from v$instance;

select value bdump from v$parameter 
 where name ='background_dump_dest';


drop   directory BDUMP;
create directory BDUMP as '&&_bdump';

drop table alert_log_disk;

create table alert_log_disk ( text varchar2(&&alert_length) )
organization external (
  type oracle_loader
  default directory BDUMP
      access parameters (
          records delimited by newline nologfile nobadfile
          fields terminated by "&" ltrim
      )
  location('alert_&&_DB..log')
)
reject limit unlimited;

update_alert_log.sql

Now, after the two tables are created, the alert_log table can be filled with the following script. It only loads those records that are greater than the last time it loaded. And it loads the date/time on every line for convienance. It also helps when the alertlogs get rotated. You still keep the history within an Oracle table. Finally, it also strips out all the «crap» that is really not needed to see if you are looking for errors.

update_alert_log.sql

set serveroutput on 

declare
  
  isdate         number := 0;
  start_updating number := 0;
  rows_inserted  number := 0;
  
  alert_date     date;
  max_date       date;
  
  alert_text     alert_log_disk.text%type;

begin
  
  /* find a starting date */
  select max(alert_date) into max_date from alert_log;
  
  if (max_date is null) then
    max_date := to_date('01-jan-1980', 'dd-mon-yyyy');
  end if;
  
  for r in (
    select substr(text,1,180) text from alert_log_disk
     where text not like '%offlining%' 
       and text not like 'ARC_:%' 
       and text not like '%LOG_ARCHIVE_DEST_1%'
       and text not like '%Thread 1 advanced to log sequence%'
       and text not like '%Current log#%seq#%mem#%'
       and text not like '%Undo Segment%lined%'
       and text not like '%alter tablespace%back%'
       and text not like '%Log actively being archived by another process%'
       and text not like '%alter database backup controlfile to trace%'
       and text not like '%Created Undo Segment%'
       and text not like '%started with pid%'
       and text not like '%ORA-12012%'
       and text not like '%ORA-06512%'
       and text not like '%ORA-000060:%'
       and text not like '%coalesce%'
       and text not like '%Beginning log switch checkpoint up to RBA%'
       and text not like '%Completed checkpoint up to RBA%'
       and text not like '%specifies an obsolete parameter%'
       and text not like '%BEGIN BACKUP%'
       and text not like '%END BACKUP%'
  )
  loop
  
    isdate     := 0;
    alert_text := null;
  
    select count(*) into isdate 
      from dual 
     where substr(r.text, 21) in ('2003','2004','2005','2006','2007')
       and r.text not like '%cycle_run_year%';
  
    if (isdate = 1) then  
  
      select to_date(substr(r.text, 5),'Mon dd hh24:mi:ss rrrr') 
        into alert_date 
        from dual;
  
      if (alert_date > max_date) then
        start_updating := 1;
      end if;
  
    else
      alert_text := r.text;
    end if;
  
    if (alert_text is not null) and (start_updating = 1) then
     
      insert into alert_log values (alert_date, substr(alert_text, 1, 180));
      rows_inserted := rows_inserted + 1;
      commit;
  
    end if;
  
  end loop;
  
  sys.dbms_output.put_line('Inserting after date '||to_char(max_date, 'MM/DD/RR HH24:MI:SS'));
  sys.dbms_output.put_line('Rows Inserted: '||rows_inserted);
  
  commit;

end;
/

Let's execute the script:

SQL> @update_alert_log

Inserting after date 01/01/80 00:00:00
Rows Inserted: 17361

PL/SQL procedure successfully completed.

The alert_log table now contains the errors as recorded in thealert.log file:

select alert_date, substr(alert_text,1, 69) 
  from alert_log;

Thursday, August 23, 2012

Restrict Users to access other application exe

create or replace procedure sp_ltmlogin AS
MODULE SYS.V_$SESSION.MODULE%TYPE ;
L_USER NUMBER ;
BEGIN
SELECT MODULE INTO MODULE FROM SYS.V_$SESSION WHERE AUDSID = USERENV('SESSIONID') AND AUDSID != 0 AND ROWNUM = 1;
SELECT COUNT(*) INTO L_USER FROM DBA_USERS DBA_USERS WHERE DBA_USERS.USERNAME=USER AND DBA_USERS.PROFILE='LTM';
IF L_USER=0 THEN
IF UPPER(MODULE) ='LDLIVETRADEMONITOR.EXE'
THEN
RAISE_APPLICATION_ERROR(-20000,'you are not authorized to access LTM software');
END IF;
END IF ;
IF L_USER > 0 THEN
IF UPPER(MODULE) ='LD.EXE'
THEN
RAISE_APPLICATION_ERROR(-20000,'you are not authorized to access LD software');
END IF;
END IF ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL ;
END;
/

create or replace

trigger tg_ltmlogin

after logon on Database

Begin

sp_ltmlogin() ;

End;

To restrict user to login only LTM user, you have to create a LTM profile and move that users to LTM profile

Followings are the steps to implement the same

1 Connect to sysdba user

Create profile for LTM users and move them to LTM profile

CREATE PROFILE LTM

LIMIT PASSWORD_REUSE_MAX DEFAULT

PASSWORD_REUSE_TIME DEFAULT;

ALTER USER LTM PROFILE LTM;

Tuesday, August 21, 2012

Drop User Trigger

create or replace trigger tg_dropuser before drop on database
when (ora_dict_obj_type ='USER')
declare
l_name varchar2(30);
begin
l_name := ORA_DICT_OBJ_NAME;
execute immediate 'delete from ldbo.tbloperatormenurights where coperator = ''' ||ora_dict_obj_name || '''';
end;
/

create or replace trigger trUserDrop after drop on database

when (ora_dict_obj_type ='USER')

begin

dbms_output.put_line('user dropped '|| ora_dict_obj_name);

end;

if you have the privilege granted like Grant Create User, Drop, etc., this is good example:

Use a database BEFORE DROP trigger. Make sure to create this as some other user (then the

user doing the drop, else they can drop the trigger!)

grant create session, create user, drop user to a

identified by a;

Grant succeeded.

grant create session to b identified by b;

Grant succeeded.

create table app_users ( username varchar2(30) );

Table created.

insert into app_users values ( 'B' );

1 row created.

create or replace trigger drop_user_trigger

before drop on database

when ( user = 'A' )

declare

l_cnt number;

l_name varchar2(30);

begin

if ( ora_dict_obj_type = 'USER' )

then

l_name := ORA_DICT_OBJ_NAME;

select count(*) into l_cnt

from dual

where exists ( select null

from app_users

where username = l_name );

if ( l_cnt <> 1 )

then

raise_application_error( -20001, 'You cannot drop that user' );

end if;

end;

Trigger created.

@connect a/a

drop user scott;

drop user scott

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-20001: You cannot drop that user

ORA-06512: at line 15

drop user b;

User dropped.

Tuesday, August 14, 2012

Oracle Fine grained auditing / track select records

In regular object-based auditing, the records can show that a user selected from a specific table, along with other helpful information such as timestamp, client machine name, etc.
What it does not record is what data the user selected from the table.

Audit Trail does not record which particular record was selected. Since reading is not a transaction, the facts are not recorded in Oracle' redo logs, rollback segments or anywhere else.

Also we cannot create trigger on select statement, we can create trigger only on insert / update / delete.

Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table.

Notice that FGA will show the SQL text, regardless of the setting of AUDIT_TRAIL – no “EXTENDED” necessary here

-------------------------

http://www.dba-oracle.com/security/fga_enhancements.htm
http://peerdba.wordpress.com/2011/01/09/fine-grained-auditing-fga/

-------------------------

begin
dbms_fga.drop_policy(
object_schema => 'LDBO',
object_name => 'TRANSACTIONS',
policy_name => 'AUDIT_TRANS'
);
END;
/

Below, I create a policy called AUDIT_TRANS that acts as a trigger for any queries against the TRANSACTIONS where anyone views a TRANSACTIONS row where Quantity>1000000.

begin
dbms_fga.add_policy(
object_schema => 'LDBO',
object_name => 'TRANSACTIONS',
policy_name => 'AUDIT_TRANS',
audit_condition => 'Quantity>1000000',
audit_column => 'QUANTITY',
handler_schema => null,
handler_module => null,
enable => true
);
end;
/

This was used to turn auditing on only for select statements against the table. The same can be now be rewritten as:

begin
dbms_fga.add_policy (
object_schema=>'CLAIM_SCHEMA',
object_name=>'CLAIMS',
policy_name=>'LARGE_CLAIM',
audit_condition=>
'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
audit_column=>
'SSN, PROC_CODE',
statement_types => 'SELECT'

);
end;
/

To audit insert, delete, and update for the same table on the same policy condition and columns, we can use:

begin
dbms_fga.add_policy (
object_schema=>'CLAIM_SCHEMA',
object_name=>'CLAIMS',
policy_name=>'LARGE_CLAIM',
audit_condition=>
'CLAIM_AMOUNT>500 OR PAID_AMOUNT>500',
audit_column=>
'SSN, PROC_CODE',
statement_types => 'SELECT,INSERT,UPDATE,DELETE'
);
end;
/

The above code writes an entry into the table fga_log$ when the table is subjected to insert, update, delete, and select statements; when the auditing condition is satisfied and the audit columns are referenced.

select
timestamp c1,
db_user c2,
os_user c3,
object_schema c4,
object_name c5,
policy_name c6,
sql_text c7
from
dba_fga_audit_trail
order by
timestamp;

--------------------------------------

select count(*) from sys.fga_log$ where dbuid not in ('USER1','USER2');
select dbuid, count(*) from sys.fga_log$ group by dbuid where dbuid not in ('USER1','USER2');
select * from dba_audit_policies;
select * from dba_audit_policies where enabled='NO';
select * from dba_audit_policies where enabled='YES';
select * from dba_fga_audit_trail where db_user not in ('USER1','USER2');
select distinct object_name, policy_name from dba_fga_audit_trail where db_user not in ('USER1','USER2');
SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail;
select * from dba_fga_audit_trail where db_user not in ('USER1','USER2');

---------------------------------–syntax for enable and disable of policy –take below select statements and execute.

select ‘begin dbms_fga.disable_policy(object_schema => ”APP_USER”, object_name => ”’ || object_name || ”’, policy_name => ”’ || policy_name ||”’);end; /’
from dba_fga_audit_trail where db_user not in (‘DBA_USER’);

Reset Oracle Parameter Value

select name,value,isdefault,isses_modifiable,issys_modifiable,
isinstance_modifiable,isdeprecated,
from v$parameter;

alter system reset some_param scope=both sid='*' ;

alter system reset some_param scope=spfile sid='*' ;

alter system reset some_param scope=memory sid='*' ;

scope =BOTH/SPFILE/MEMORY

For string parameters, setting to an empty string will restore the default.

ALTER SYSTEM SET parameter = '' scope=SPfile;

For any parameter the RESET option will restore the default.
ALTER SYSTEM RESET parameter scope=SPfile sid='*' ;

note
When resetting a parameter, you must specify sid=mySid or sid='*' even for non-RAC instances.

ALTER SYSTEM RESET memory_target scope=SPfile sid='*' ;

donot do that with memory parameter, other oracle will not startup then restart oracle services

memory_target will bet set to zero.

then reset memory_target again

Friday, August 10, 2012

Check Constraint on SYSDATE

create table table1 (startdate date,CloseDate date);

ALTER TABLE Table1
ADD (CONSTRAINT GT_Table1_CloseDate
CHECK (CloseDate > SYSDATE),
CONSTRAINT LT_Table1_CloseDate
CHECK (CloseDate <= SYSDATE + 365)),
CONSTRAINT GT_Table1_StartDate
CHECK (StartDate > (CloseDate + (SYSDATE + 730))));

Error report:
SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 - "date or system variable wrongly specified in CHECK constraint"
*Cause: An attempt was made to use a date constant or system variable,
such as USER, in a check constraint that was not completely
specified in a CREATE TABLE or ALTER TABLE statement. For
example, a date was specified without the century.
*Action: Completely specify the date constant or system variable.
Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
which a bug permitted to be created before version 8.

A check constraint, unfortunately, cannot reference a function like SYSDATE. You would need to create a trigger that checked these values when DML occurs, i.e.

CREATE OR REPLACE TRIGGER trg_check_dates
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
BEGIN
IF( :new.CloseDate <= SYSDATE )
THEN
RAISE_APPLICATION_ERROR( -20001,
'Invalid CloseDate: CloseDate must be greater than the current date - value = ' ||
to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
END IF;
IF( :new.CloseDate > add_months(SYSDATE,12) )
THEN
RAISE_APPLICATION_ERROR( -20002,
'Invalid CloseDate: CloseDate must be within the next year - value = ' ||
to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
END IF;
IF( :new.StartDate <= add_months(:new.CloseDate,24) )
THEN
RAISE_APPLICATION_ERROR( -20002,
'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate = ' ||
to_char( :new.StartDate, 'YYYY-MM-DD HH24:MI:SS' ) ||
' CloseDate = ' || to_char( :new.CloseDate , 'YYYY-MM-DD HH24:MI:SS' ) );
END IF;
END;
/

Each and every time the record is updated SYSDATE will have a different value. Therefore the constraint will validate differently each time. Oracle does not allow sysdate in a constraint for that reason.

You may be able to solve your problem with a trigger that checks if CloseDate has actually changed and raise an exception when the new value is not within range.

Excel Security Settings using Command / ASP.NET code

cmd>reg add "HKCU\Software\Microsoft\Office\12.0\Excel\Security" /v AccessVBOM /t reg_dword /d 1 /f

Microsoft.Win32.Registry.SetValue("HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security", "AccessVBOM", "1",RegistryValueKind.dWord)

Windows Regional settings using command or in ASP.NET code

cmd> REG ADD "HKCU\Control Panel\International" /v sShortDate /d "dd/MM/yyyy" /f

Microsoft.win32 Namespace is containing register class; Register class is used to manipulate the system registry. It represents a Key level node in the windows registry.

Microsoft.Win32.Registry.SetValue("HKEY_CURRENT_USER\Control Panel\International", "sShortDate", "dd/MM/yyyy")

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

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Microsoft.Win32.Registry.SetValue("HKEY_CURRENT_USER\Control Panel\International", "sShortDate", "dd/MM/yyyy")

End Sub

Allow your application from Windows Firewall During Installation

1)

void AddToFirewallException(string ApplicationName, string FilePath)
{
// Add exception to windows firewall after installation
string RegPath =
@"SYSTEM\ControlSet001\Services\SharedAccess\Parameters\
FirewallPolicy\StandardProfile\AuthorizedApplications\List";
string KeyValue = FilePath + ":*:Enabled:" + ApplicationName;

RegistryKey Key = Registry.LocalMachine.OpenSubKey(RegPath, true);
Key.SetValue(FilePath, KeyValue);
Key.Close();
Key = null;
}

the above code also works fine when called from a winform, but raise an exception when I called it in the custom action in VS setup project, Actually I want to add my app in the allowed program list during the installation.

2) Method

During the installation of my application, I needed to add it to the Windows firewall as an allowed application and open two ports for another application. This code will function as a custom action during the install to open the firewall on install and close it on uninstall. In trying to keep things as simple as possible, the following C# class library will be called from the setup - openFirewall() and closeFirewall().

First, I generated the FWSetupAction project as a C# class library. After that, I use the properties page to switch the output type to a console application to step through it with the debugger. When it's operational, switch back to the class library for integration with the MSI setup logic and incorporate it as a custom action.

After the initial project creation, rename Class1.cs to Firewall.cs in the Solution Navigator. If you're writing code anew, add the NetFwTypeLib reference first to allow intellisense to help you recognize the terms you'll be coding. This reference will be required for correct compilation, so whether you put it in before coding or after doesn't matter, but it will be needed. To add the reference, right click on References and select Browse. Browse to%windir%\system32\hnetcfg.dll and select it - the NetFwTypeLib will be created.

Edit the Firewall.cs class to have the following code:

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using NetFwTypeLib;
using Microsoft.Win32;
namespace FWSetupAction
{
public class Firewall
{
    protected int[] discoPorts = { 0xD100, 0xD101 };
    protected INetFwProfile fwProfile;

    public void openFirewall()
    {
        ///////////// Firewall Authorize Application ////////////
        String imageFilename = getImageFilename();
        setProfile();
        NetFwAuthorizedApplications apps = fwProfile.AuthorizedApplications;
        INetFwAuthorizedApplication app = 
          ( INetFwAuthorizedApplication ) getInstance( "INetAuthApp" );
        app.Name = "Application Name";
        app.ProcessImageFileName = imageFilename;
        apps.Add( app );
        apps = null;

        //////////////// Open Needed Ports /////////////////
        INetFwOpenPorts openports = fwProfile.GloballyOpenPorts;
        foreach( int port in discoPorts )
        {
            INetFwOpenPort openport = 
              ( INetFwOpenPort ) getInstance( "INetOpenPort" );
            openport.Port = port;
            openport.Protocol = NET_FW_IP_PROTOCOL_.NET_FW_IP_PROTOCOL_UDP;
            openport.Name = "New Open Port";
            openports.Add( openport );
        }
        openports = null;
    } // openFirewall

    public void closeFirewall()
    {
        String imageFilename = getImageFilename();
        setProfile();
        INetFwAuthorizedApplications apps = fwProfile.AuthorizedApplications;
        apps.Remove( imageFilename );
        apps = null;
        INetFwOpenPorts ports = fwProfile.GloballyOpenPorts;
        ports.Remove( discoPorts[ 0 ], NET_FW_IP_PROTOCOL_.NET_FW_IP_PROTOCOL_UDP );
        ports.Remove( discoPorts[ 1 ], NET_FW_IP_PROTOCOL_.NET_FW_IP_PROTOCOL_UDP );
        ports = null;
    }

    protected string getImageFilename()
    {
        // Get install directory from the registry
        RegistryKey pRegKey = Registry.LocalMachine;
        pRegKey = pRegKey.OpenSubKey( "SOFTWARE\\Company Directory\\AppDir" );
        Object insDir = pRegKey.GetValue( "InstallDir" );
        return insDir + "RVP.exe";
    }

    protected void setProfile()
    {
        // Access INetFwMgr
        INetFwMgr fwMgr = ( INetFwMgr ) getInstance( "INetFwMgr" );
        INetFwPolicy fwPolicy = fwMgr.LocalPolicy;
        fwProfile = fwPolicy.CurrentProfile;
        fwMgr = null;
        fwPolicy = null;
    }

    protected Object getInstance( String typeName )
    {
        if( typeName == "INetFwMgr" )
        {
            Type type = Type.GetTypeFromCLSID(
            new Guid( "{304CE942-6E39-40D8-943A-B913C40C9CD4}" ) );
            return Activator.CreateInstance( type );
        }
        else if( typeName == "INetAuthApp" )
        {
            Type type = Type.GetTypeFromCLSID(
            new Guid( "{EC9846B3-2762-4A6B-A214-6ACB603462D2}" ) );
            return Activator.CreateInstance( type );
        }
        else if( typeName == "INetOpenPort" )
        {
            Type type = Type.GetTypeFromCLSID(
            new Guid( "{0CA545C6-37AD-4A6C-BF92-9F7610067EF5}" ) );
            return Activator.CreateInstance( type );
        }
        else return null;
    }

    static void Main( string[] args )
    {
        Firewall fw = new Firewall();
        fw.openFirewall();
        fw.closeFirewall();
    }
}
}

Once compiled, you're ready to test. Set a breakpoint on each of the firewall entry methods - openFirewall()and closeFirewall(), and step through the program. Use a DOS box to verify the operations. The netsh firewall command will verify the operation of the code:

netsh fire show allowed - shows the programs that are allowed
netsh fire show port - shows the ports that are open

Thursday, August 9, 2012

add_months Function Quarter start date and end date

how can i get the start date n end date of quarter?eg. if i pass date as 30/07/2012 then start date should be 01/07/2012 to 30/09/2012,If i pass 21/12/2012 then it should be 01/10/2012 to
31/12/2012

SQL> select TRUNC(sysdate+1, 'Q'),TRUNC(ADD_MONTHS(sysdate, +3), 'Q')-1 from dual;

TRUNC(SYS TRUNC(ADD
--------- ---------
01-JUL-12 30-SEP-12

SQL> select TRUNC(to_date('30-jul-12'), 'Q'),TRUNC(ADD_MONTHS('30-jul-12',+3), 'Q')-1 from dual;

TRUNC(TO_ TRUNC(ADD
--------- ---------
01-JUL-12 30-SEP-12

SQL> select TRUNC(to_date('21-dec-12'),'Q'),TRUNC(ADD_MONTHS('21-dec-12',+3), 'Q')-1 from dual;

TRUNC(TO_ TRUNC(ADD
--------- ---------
01-OCT-12 31-DEC-12

references

http://www.dba-oracle.com/job_scheduling/dates_times.htm

http://www.oracle.com/technetwork/issue-archive/2012/12-jan/o12plsql-1408561.html
http://www.techonthenet.com/oracle/functions/add_months.php

http://www.sql-server-helper.com/functions/get-first-day-of-quarter.aspx

http://www.tek-tips.com/viewthread.cfm?qid=1635253
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3868465700346538981

Wednesday, August 8, 2012

Two User Logon Not Allowed on same Machine

create or replace trigger TG_SINGLELOGIN
after logon on database
declare
cnt pls_integer;
begin
select count(*) into cnt from v$session
where username !=sys_context('userenv','session_user') and terminal =sys_context('userenv','terminal');
if cnt >= 1 then
raise_application_error (-20001, 'You are already connected on another user with this machine');
end if;
exception
when no_data_found then raise;
end;
/

Concurrent User Login Denied from Different Machine

create or replace trigger TG_MULTILOGIN
after logon on database
declare
cnt pls_integer;
begin
select count(*) into cnt from v$session
where username=sys_context('userenv','session_user') and terminal !=sys_context('userenv','terminal') and status like '%ACTIVE%';
if cnt >= 1 then
raise_application_error (-20001, 'You are already connected on another machine');
end if;
exception
when no_data_found then raise;
end;
/

RAISE_APPLICATION_ERROR (-20001, 'You are not allowed to logon');

wont work for a user who has ADMINISTER DATABASE TRIGGER privilege.

The ADMINISTER DATABASE TRIGGER privilege allows you to create database-level triggers (server error, login, and logout triggers). It also allows you to log in regardless of errors thrown by a login trigger as a failsafe. If you inadvertently coded your login trigger to throw an error no matter who was logging in, for example, you need to allow someone to log in to fix the trigger.

if someone is exit from oracle without logout then record is still present in v$session then error occur.

we can use status like '%ACTIVE%'; to avoid killed and sniped session

User Machine Specific Access

suppose you have five machines and five users ( one user per machine)

Note: these users are database users...

e.g

User name	Machine name
User1	WORKGROUP\PC-01
User2	WORKGROUP\PC-02
User3	WORKGROUP\PC-03
User4	WORKGROUP\PC-04
User5	WORKGROUP\PC-05

So according to above table ye create a table in our database as sys user

Create table user_record

(user_name varchar2(25),

Machine_name varhcar2(30));

now insert user info ….