VKTM detected a time drift. Please check trace file for more details.

Issue: Frequent warning message in alert log
VKTM detected a time drift. Please check trace file for more details.

Error in trace file->
ksesethighpri: (ksb.c:4178) Failed to elevate VKTM's priority from 0 to 1
Error Info: Category(-2), Opname(skgdism_create), Loc(sp.c:1553), ErrMsg(Error 0) Dism(-1038804382)
*** SESSION ID:(1.1) 2014-06-10 20:15:51.823
*** SERVICE NAME:() 2014-06-10 20:15:51.823

kstmmainvktm: failed in setting elevated priority
Verify: SETUID is set on ORADISM and restart the instance
highres_enabled
VKTM running at (100ms) precision
kstmrmtickcntkeeper: param _dbrm_quantum will not be effective

*** 2014-06-10 20:15:51.823
[Start] HighResTick = 1402445751823794
kstmrmtickcnt = 0, ksudbrmseccnt[0] = 1402445751
kstmchkdrift (kstmrmtickcntkeeper:lowres): Time jumped forward by (8000000)usec at (1402445759) whereas (5000000) is allowed


Cause:
OSDBA has the wrong permissions on /etc/privgroup.


Solution:
1.Log in as the root user.
2.Using any text editor, open the /etc/privgroup file, or create it if
necessary.
3.Add or edit the following line, which begins with the name of the OSDBA
group, specifying the privileges RTPRIO and RTSCHED that you want to grant to
this group every time the system restarts (here dba is the OSDBA group):
dba RTPRIO RTSCHED
4.Save the file, and quit the text editor.
5.Enter the following command to grant the privileges to the OSDBA group:
# /usr/sbin/setprivgrp -f /etc/privgroup
6.Enter the following command to verify that the privileges are set
correctly:
# /usr/sbin/getprivgrp dba

How to Reorganization Tables in oracle

Table reorganization

|After many changes to table data, |logically sequential data may be on non-sequential physical data |pages so that the database manager must perform additional |read operations to access data. Additional read |operations are also required if a significant number of rows |have been deleted. In such a case, you might consider |reorganizing the table to match the index and to reclaim space. |

A) Identify Tables needs to reorg:
1) table1
2) table2
3) table3
4) table4

B) Check free space
Free Space requied=10GB


Steps:

1)Take the Backup
  a)Export backup
or
  b)Full Backup 


2)Take the Dump of Tables
  select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,sum(BYTES/1024/1024)"size MB" from dba_segments where SEGMENT_NAME in('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR'
group by OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME
order by OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME


3)Take the Dump of Indexes
select owner,segment_name,segment_type,tablespace_name,BYTES/1024/1024 from dba_segments where segment_name in (select INDEX_NAME from dba_indexes where table_name in ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR') and owner='HR' order by segment_name


4)Check the available space in the Tablespaces
set pages 200
set lines 200
col n_extent format 999,999,999
ttitle 'Free Space by Tablespace'
col col1    format a28  heading "Tablespace"
col col2    format 999,999,999,999    heading "Bytes(KB)"
col col3    format 999,999,999    heading "Used(KB)"
col col4    format 999,999,999    heading "Free(KB)"
col col5    format 999.9    heading "Free(%)"
break on report
compute sum of col2 col3 col4 FREE_MAX  on report
select
-- b.file_id "File #",
    b.tablespace_name col1,
        b.bytes / 1024 col2,
    ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 col3,
    (sum(nvl(a.bytes,0))) / 1024 col4,
    (sum(nvl(a.bytes,0)) / (b.bytes)) * 100 col5,
    max(a.bytes)/1024 FREE_MAX, count(*)
from sys.dba_free_space a,
     (select  tablespace_name, sum(bytes) bytes from sys.dba_data_files
         group by tablespace_name ) b
-- where a.file_id(+) = b.file_id
where a.tablespace_name = b.tablespace_name
group by b.tablespace_name, b.bytes
 order by 5;

SQL> set lines 200
SQL> col file_name for a60
SQL> col tablespace_name for a25
SQL> select file_name,tablespace_name,bytes/1024/1024,autoextensible from dba_data_files where tablespace_name='TEST';

!bdf | grep /data1

Check the availablity of Space and Manage the Space as per the requirement



5)Check the status of the objects
SELECT COUNT(*) FROM DBA_OBJECTS;
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='VALID';
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';
select owner,object_type,status,count(*) from dba_objects where owner='HR' group by owner,object_type,status order by object_type;
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM dba_objects where status='INVALID' AND OWNER='HR';


6)Check the Count of the objects
select count(*) from HR.table1;
select count(*) from HR.table2;
select count(*) from HR.table3;
select count(*) from HR.table4;
select count(*) from HR.table4;


7) Prepare the Script to move the TABLES
    a) Non-Partition
select ' alter table ' ||OWNER||'.'||TABLE_NAME||' move tablespace GLOBAL_data1;'
from dba_tables where TABLE_NAME in ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR' and PARTITIONED='NO'


   b)Partition

select 'alter table ' ||table_owner||'.'||table_name || ' move  partition ' || partition_name || ' tablespace GLOBAL_data1;' from dba_tab_partitions where table_name in('table1',
'table2',
'table3',
'table4',
'table4')
and TABLE_OWNER='HR'

Execute the script and generate the spool file.
Check the spool file for errors.


8)Prepare the Script to move the Indexes
   a) Non-Partition
   select 'alter index ' ||owner||'.'||index_name || ' rebuild TABLESPACE GLOBAL_data1;'
from dba_indexes where table_name in ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR'

   b)Partition
  select 'alter index ' ||index_owner||'.'||index_name || ' rebuild  partition ' || partition_name || ' tablespace GLOBAL_data1;' 
from dba_ind_partitions 
where index_name in (select INDEX_NAME from dba_indexes where table_name in ('table1',
'table2',
'table3',
'table4',
'table4')
and owner='HR' and PARTITIONED='YES')

Execute the script and generate the spool file.
Check the spool file for errors.


10) Confirm the Status 
 Confirm the Status and count of the objects using Step 4)and 5
   If it is same then activity is successfull


11)RollBack Plan
Restore the Table from the Export Backup

Analyze Parameter - Oracle

Degree: When taking statistics you can perform parallel queries using the  degree parameter,this way your procedure will perform faster,the basic rule is to set the degree parameter equal to the number of CPUs (or cores) minus 1.

Estimate_percent: It is not easy to select the best size for the estimate_percent  parameter.If you set it too high.It will take a long time to collect the statistics.If you set it too low,you can gather the statistics quickly all right.but those statistics can very well be inaccurate.We can set value between 0 to 100.A rule of thumb here is that the more uniform the table's data,the smaller the sample size.On the other hand,if a tables data is highly skewed,you should use higher sample size.Of course,setting the parameter to value 100 means that the database is not doing an estimation.If you think the data is uniformaly distributed even a 1% - 2% sample size wil get you very accurate statistics and save you a bunch of time.By default the database uses the constant DBMS_STATS.AUTO_SAMPLE_SIZE TO DETERMINE THE BEST SAMPLE SIZE.