Revoke object granted to PUBLIC for a specific USER

SELECT ' revoke all on '||owner||'.'||table_name||' from public;'
FROM table_privileges
WHERE grantee = 'PUBLIC'
AND OWNER='UM'
ORDER BY owner, table_name;

When a new table is created , a SELECT priveleges on that TABLE is added to a ROLE.

When a new table is created , a SELECT priveleges on that TABLE is added to a ROLE.
====================================================================================
--Create the tigger and the hr_s role under sys.

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
DECLARE
main_sql_text varchar2(100);
job_name_ varchar2(30);
job_type_ varchar2(30);
job_action_ varchar2(4000);
sql_text varchar2(400);
job_id_ varchar2(30);
start_date_ timestamp;
BEGIN

if (ora_sysevent='CREATE' and ora_dict_obj_type='TABLE' and ora_dict_obj_owner='HR' )then
sql_text := 'grant select on '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' to hr_s';
select to_char(systimestamp,'DDMMYYYYHH24MISS') into job_id_ from dual;
select SYSTIMESTAMP + interval '1' minute into start_date_ from dual;
main_sql_text:=''''||sql_text||'''';
job_name_:='job'||job_id_;
job_type_:='PLSQL_BLOCK';
job_action_:='begin execute immediate('||main_sql_text||'); end;';
execute immediate ('begin DBMS_SCHEDULER.create_job (job_name=> :a,job_type=> :b,job_action => :c,start_date=> :d,repeat_interval => NULL,end_date=> NULL,enabled=>TRUE,comments=> NULL); end;') using job_name_,job_type_,job_action_,start_date_;
end if;
END ddl_trig;
/


--Grant the role on a privelges users.

Prepare Oracle Linux 6 for Installing Oracle 11gR2 (rpm packages))

Prepare Oracle Linux 6 for Installing Oracle 11gR2
by Michael Georgiou
============================================================
mpfr-2.4.1-6
cpp-4.4.4-13
ppl-0.10.2-11
gcc-4.4.4-13
cloog-ppl-0.15.7-1.2
binutils-2
glibc-2
nss-softokn-freebl-3
glibc-2
nss-softokn-freebl-3
compat-libstdc++-33
glibc-common-2
glibc-devel-2
glibc-headers-2
elfutils-libelf-0
elfutils-libelf-devel-0
gcc-4
gcc-c++-4
ksh-
libaio-0
libaio-devel-0
libgcc-4
libstdc++-4
libstdc++-devel-4
make-3.81mpfr-2.4.1-6
cpp-4.4.4-13
ppl-0.10.2-11
gcc-4.4.4-13
cloog-ppl-0.15.7-1.2
numactl-devel-2
sysstat-9
compat-libstdc++-33
compat-libcap


All packages are available :/media/OL6.0 i386 Disc 1 20110203/Server/Packages

SQL Performance

DECLARE
TYPE rc IS REF CURSOR;
l_rc rc;
l_dummy all_objects.object_name%TYPE;
l_start number DEFAULT dbms_utility.get_time;
BEGIN
FOR i IN 1 .. 1000
LOOP
OPEN l_rc FOR
'select object_name
from all_objects
where object_id = ' || i;
FETCH l_rc INTO l_dummy;
CLOSE l_rc;
dbms_output.put_line(l_dummy);
END LOOP;
dbms_output.put_line(ROUND((dbms_utility.get_time-l_start)/100, 2) ||' Seconds...' );
END;
/

Create schema statistics using DBMS_STATS

dbms_stats.gather_schema_stats
(
ownname=>'HR',
cascade=>TRUE, ---Indexes are included
method_opt=>'FOR ALL INDEXED COLUMNS',--Create Histograms
estimate_percent => dbms_stats.auto_sample_size, --Takes Sample size for the tables and Indexes
degree => 2 --Parallel degree 2
);

Create Service in a single Instance database

exec DBMS_SERVICE.CREATE_SERVICE('TPCH','TPCH');
exec DBMS_SERVICE.START_SERVICE('TPCH','ORCL');


TPCH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TPCH)
)
)


sqlplus system/xxxxxx@TPCH