joi, 9 ianuarie 2014

Tired to add privileges for a user, after any poped up error? Give them all from the beginning!

If the security is not a real issue, when you create a user, give all the privileges to him. If they are too many, you're free to edit the list and taylor it to meet your wishes. Be aware of the dba privilege, it could be too much!

 grant create session, create table, create procedure, create sequence, create view, create trigger, create synonym, create materialized view, CREATE PUBLIC DATABASE LINK, CREATE DATABASE LINK, query rewrite, create any directory, create type, aq_administrator_role, javasyspriv,  dba to username;

then:

alter user username quota unlimited  on   users; 

where users is the tablespace name...


miercuri, 8 ianuarie 2014

How to drop and recreate tables when the dependencies and foreign keys are way too complicate

If you noticed the prior post, i will tell you what context i used it in.
When you build a schema, soon you'll be found in the situation when it's too complicate to drop and recreate tables and other objects, because of the dependencies. Unnumbered ORA errors will appear as you step into the problem.
Let's get into the scenario:

- first of all, you need to drop the table, in order to modify it. This is imposible, because, some of his columns are foreign keys for other tables.

drop table app_forms;

- so, you need to disable the constraints. Unluckily, the prior post contains a script that is not aware of the precedence of all the constraints. Here will apply the CASCADE reserved word, the one from the 8th line. This is the reason is there.  

declare
nume_ct varchar2(50);
begin 
for nume_ct in (select constraint_name,u.table_name from user_constraints c, user_tables u where c.table_name = u.table_name)
    loop 
    EXECUTE IMMEDIATE 'alter table ' || nume_ct.table_name || ' disable constraint ' ||nume_ct.constraint_name;
    end loop;                
end;


- next, try to drop the table. Sometimes, i don't know why, the following error will appear: ORA-02449: unique/primary keys in table referenced by foreign keys. Once again, i don't know why, because all the constraints are disabled. Go further, by adding CASCADE CONTRAINTS in you drop command.

drop table app_forms cascade constraints;

- next step, recreate the table with the modified script. Error: ORA-02270: no matching unique or primary key for this column-list. That's because the reinforced foreign key constraints don't find anymore the primary keys referenced. So, run the enabling constraints script, from the prior post, and after that, create the table. We'll work!

marți, 7 ianuarie 2014

What if you want to freeze all the constraints in a schema?

Sometime, you need to recreate tables or another objects, and this action is not posible because of the dependencies, represented by constraints. If you try to overcome this issue, you will find that is a big waste of time, 'cause you're trying on and on to find all the dependencies and tables related one to another.
So, another idea could be the next one:
Disable ALL the constraints in the schema, for a short period of time, but be aware, don't do this while the users who can load some data are connected!

The script that disables the constraints: 

declare
nume_ct varchar2(50);
begin 
for nume_ct in (select constraint_name,u.table_name from user_constraints c, user_tables u where c.table_name = u.table_name)
    loop 
    EXECUTE IMMEDIATE 'alter table ' || nume_ct.table_name || ' disable constraint ' ||nume_ct.constraint_name;
    end loop;                
end;


Script that enables the constraints: 

declare
nume_ct varchar2(50);
begin 
for nume_ct in (select constraint_name,u.table_name from user_constraints c, user_tables u where c.table_name = u.table_name)
    loop 
    EXECUTE IMMEDIATE 'alter table ' || nume_ct.table_name || ' enable constraint ' ||nume_ct.constraint_name;
    end loop;                
end;