How to format pen drive in Linux ?

command is

mkfs.vfat -I -n 'USB-name' /dev/sdb



apt-amd-02:/home/anujs/Downloads # umount /dev/sdb1

apt-amd-02:/home/anujs/Downloads # mkfs.vfat -I -n 'anuj' /dev/sdb1
mkfs.vfat 3.0.9 (31 Jan 2010)

now your drive will be

df -h


/dev/sdb1 7.6G 4.0K 7.6G 1% /media/anuj



List of these commands are as follows-

mkfs.bfs
mkfs.ext3
mkfs.ext4dev
mkfs.msdos
mkfs.reiserfs
mkfs.ext2
mkfs.ext4
mkfs.minix
mkfs.ntfs
mkfs.vfat

Oracle RPM Installation

apt-amd-02:/home/anujs/Downloads/Oracle_Linux_rpm # cat rpm_install.txt

13 find /media/cdrom -name RPMS
14 cd /media/cdrom/Enterprise/RPMS
15 ls -lt
16 rpm -Uvh setarch-1*
17 rpm -Uvh compat-libstdc++-33-3*
18 rpm -Uvh make-3*
19 rpm -Uvh glibc-2*
20 rpm -Uvh openmotif-2*
21 rpm -Uvh compat-db-4*
22 rpm -Uvh gcc-3*
23 rpm -Uvh libaio-0*
24 rpm -Uvh rsh-*
25 rpm -Uvh compat-gcc-32-3*
26 rpm -Uvh compat-gcc-32-c++-3*
27 rpm -Uvh compat-gcc-3*
28 rpm -Uvh openmotif21*
29 rpm -Uvh sysstat*
30 rpm -Uvh compat-gcc-32-3*
31 rpm -Uvh glibc-devel-2.3.4-2.43.i386.rpm
32 rpm -Uvh glibc-*
33 rpm -Uvh compat-gcc-32-c++-3*
34 uname -P
35 uname -p
36 rpm -ivh gcc-3.4.6-3.1.i386.rpm
37 pwd
38 rpm -ivh compat-gcc-32-c++-3*
39 rpm -ivh compat-gcc-32-3.2.3-47.3.i386.rpm
40 rpm -ivh glibc-devel-2.3.4-2.43.i386.rpm
41 rpm -ivh glibc-headers-2.3.4-2.43.i386.rpm
42 rpm -ivh glibc-kernheaders-2.4-9.1.103.EL.i386.rpm
43 rpm -ivh compat-gcc-32-3.2.3-47.3.i386.rpm
44 rpm -ivh glibc-devel-2.3.4-2.43.i386.rpm
45 rpm -ivh glibc-headers-2.3.4-2.43.i386.rpm
46 rpm -ivh compat-gcc-32-3.2.3-47.3.i386.rpm
47 rpm -ivh glibc-devel-2.3.4-2.43.i386.rpm
48 rpm -ivh glibc-headers-2.3.4-2.43.i386.rpm
49 rpm -ivh glibc-headers-2.3.4-2.43.i386.rpm
50 rpm -Uvh compat-gcc-32-*
51 rpm -Uvh openmotif21*
52 rpm -Uvh sysstat*
53 pwd
54 vi /etc/sysctl.conf
55 /sbin/sysctl -p
56 vi /etc/security/limits.conf
57 vi /etc/pam.d/login
58 vi /etc/selinux/config
59 vi /etc/modprobe.conf
60 groupadd oinstall
61 groupadd dba
62 groupadd oper
63 useradd -u 500 -g oinstall -G dba oracle
64 passwd oracle
65 df -h
66 mkdir /home/oracle
67 cd /home/oracle
68 ls -lt
69 pwd
70 vi .bash_profile
71 chkconfig nfs on
72 ipconfig
73 ifconfig
74 chkconfig nfs on
75 service nfs restart
76 vi /etc/hosts
77 init 0;exit;
78 ifconfig
79 ifconfig -a
80 service network restart
81 ifconfig a
82 ifconfig -a
83 init 0
84 ifconfig -a
85 service network restart
86 system-config-networkt
87 system-config-network
88 service network restart
89 vi /etc/sysconfig/network-scripts/ifcfg-eth0
90 service network restart
91 vi /etc/sysconfig/network-scripts/ifcfg-eth0
92 system-config-network
93 system-config-network
94 init 0
95 hostname
96 vi /etc/sysconfig/network
97 hostname
98 service network restart
99 hostname
100 hostname rac2
101 service network restart
102 exec /usr/bin/ssh-agent $SHELL
103 /etc/init.d/autofs restart
104 showmount -e 192.168.2.247
105 cd /
106 ls
107 mkdir /u01
108 mount 192.168.2.247:/shared_config /u01
109 df -h
110 umount /u01
111 df -h
112 pwd
113 cd /etc/ssh/ssh_config
114 vi /etc/ssh/ssh_config
115 /etc/init.d/sshd restart
116 vi /etc/ssh/ssh_config
117 /etc/init.d/sshd restart
118 su - oracle
119 vi /etc/ssh/ssh_config
120 /etc/init.d/sshd restart
121 su - oracle
122 vi /etc/fstab
123 df -h
124 mkdir -p /u01/app/crs/product/10.2.0/crs
125 mkdir -p /u01/app/oracle/product/10.2.0/db_1
126 mkdir -p /u01/oradata
127 mkdir -p /u01/shared_config
128 chown -R oracle:oinstall /u01/app /u01/app/oracle /u01/oradata /u01/shared_config
129 chmod -R 775 /u01/app /u01/app/oracle /u01/oradata /u01/shared_config
130 mount -a
131 df -h
132 touch /u01/shared_config/test1
133 ls -lt /u01/shared_config/test1
134 cd /u01/app/oracle/product/10.2.0/db_1
135 ls -lt
136 cd ..
137 ls -lt
138 ping rac2
139 ping rac1
140 df -h
141 cd /media/cdrom
142 ls -lt
143 cd Enterprise/
144 cd RPMS/
145 ls -lt
146 rpm -Uvh gcc-3*
147 pwd
148 cd /home/oracle/
149 ls -lt
150 ls -lt

381 rpm -ivh compat-gcc-c++-7.3-2.96.128.XOS.1.i386.rpm
382 rpm -ivh compat-gcc-c++-7.3-2.96.128.XOS.1.i386.rpm --aid
383 rpm -ivh compat-libstdc++-7.3-2.96.128.i386.rpm
384 rpm -ivh compat-libstdc++-7.3-2.96.128.i386.rpm --force
385 rpm -ivh compat-libstdc++-devel-7.3-2.96.128.XOS.1.i386.rpm
386 wget ftp://ftp.pbone.net/mirror/www.whiteboxlinux.org/whitebox/3.0/en/os/x86_64/RedHat/RPMS/compat-libstdc++-devel-7.3-2.96.128.i386.rpm
387 rpm -ivh compat-libstdc++-devel-7.3-2.96.128.i386.rpm



151 rpm -Uvh compat-gcc-c++-7.3-2.96.128.i386.rpm --force
152 rpm -Uvh compat-gcc-7.3-2.96.128.i386.rpm --force
153 rpm -Uvh compat-gcc-c++-7.3-2.96.128.i386.rpm --force
154 rpm -Uvh compat-libstdc++-devel-7.3-2.96.128.i386.rpm --force
155 rpm -Uvh compat-libstdc++-devel-7.3-2.96.128.i386.rpm --force
156 ls -lt
157 rpm -Uvh compat-libstdc++-7.3-2.96.128.i386.rpm
158 rpm -Uvh compat-libstdc++-7.3-2.96.128.i386.rpm --force
159 rpm -Uvh compat-libstdc++-devel-7.3-2.96.128.i386.rpm --force
160 ls -lt
161 rpm -Uvh compat-gcc-7.3-2.96.128.i386.rpm --force
162 rpm -Uvh compat-gcc-c++-7.3-2.96.128.i386.rpm --force
163 cat /etc/host
164 cat /etc/hosts
165 /u01/app/oracle/oraInventory/orainstRoot.sh
166 /u01/app/oracle/product/10.2.0/db_1/root.sh
167 su - oracle
168 /u01/app/oracle/product/10.2.0/db_1/root.sh
169 su - oracle
170 ifconfig -a
171 system-config-network
172 cat /etc/hosts
173 vi /etc/hosts
174 service network restart
175 hostname
176 system-config-network
177 vi /etc/sysconfig/network-scripts/ifcfg-eth1
178 vi /etc/sysconfig/network-scripts/ifcfg-eth0
179 service network restart
180 system-config-network
181 service network restart
182 ping rac1
183 ipconfig -a
184 ifconfig -a
185 uname -a
186 ifconfig -a
187 cd /u01/app
188 ls -lt
189 rm -rf oracle
190 rm /etc/oracle/*
191 rm -rf /etc/oracle/*
192 rm -f /etc/init.d/init.cssd
193 rm -f /etc/init.d/init.crs
194 rm -f /etc/init.d/init.crsd
195 rm -f /etc/init.d/init.evmd
196 rm -f /etc/rc2.d/K96init.crs
197 rm -f /etc/rc2.d/S96init.crs
198 rm -f /etc/rc3.d/K96init.crs
199 rm -f /etc/rc3.d/S96init.crs
200 rm -f /etc/rc5.d/K96init.crs
201 rm -f /etc/rc5.d/S96init.crs
202 rm -Rf /etc/oracle/scls_scr
203 rm -f /etc/inittab.crs
204 cp /etc/inittab.orig /etc/inittab
205 pwd
206 ls -lt
207 rm -Rf crs
208 rm -Rf crs/
209 init 6
210 history
211 cd /etc/init.d/ssh restart
212 cd /etc/init.d/sshd start
213 /etc/init.d/sshd start
214 service ssh start
215 service sshd restart
216 ifconfig
217 ifconfig -a
218 cat /etc/hosts
219 vi /etc/hosts
220 vi /etc/hosts
221 network restart
222 history
223 init 6
224 ifconfig
225 ifconfig -a
226 ifconfig
227 ifup eth0
228 yast2
229 yast2
230 init 6
231 ifconfig
232 ifup eth0
233 system-config-network
234 kudzu
235 ifconfig
236 service network restart
237 ifconfig
238 kudzu
239 system-config-network
240 kudzu
241 system-config-network
242 service network restart
243 ifconfig
244 ping 192.168.2.222
245 ping 192.168.2.222
246 ping 192.168.2.121
247 ping 192.168.2.121
248 ping -b 192.168.2.222
249 ping -b 192.168.2.
250 ping 192.168.2.122
251 ping 192.168.2.121
252 nslookup
253 ls
254 cd /tmp/
255 ls
256 ifconfig
257 ping 192.168.2.122
258 ping 192.168.2.121
259 ping rac1-vip
260 ethtool
261 ethtool -i
262 ethtool -S
263 ethtool eth0
264 ethtool eth1
265 ethtool -s eth0
266 ethtool -S eth0
267 mii-tool
268 ping rac11-vip
269 ping rac1-vip
270 ls
271 dig
272 dig rac1
273 dig -X rac1
274 dig -x rac1
275 dig rac1.aptus.co.uk
276 cd /usr/lib
277 ls
278 pwd
279 df -h
280 mount
281 df -h
282 cd /u01/shared_config
283 ls -lt
284 cd voting_disk
285 rm -r voting_disk
286 rm ocr_configuration
287 pwd
288 chown -R oracle:oinstall /u01/shared_config
289 chown -R oracle:oinstall /u01/app/crs/product/10.2.0/crs
290 chown -R oracle:oinstall /u01/app/oracle/product/10.2.0/db_1
291 chown -R oracle:oinstall /u01/oradata
292 touch /u01/shared_config/ocr_configuration
293 touch /u01/shared_config/voting_disk
294 chown -R oracle:oinstall /u01/shared_config
295 chown -R oracle:oinstall /u01/app/crs/product/10.2.0/crs
296 chown -R oracle:oinstall /u01/app/oracle/product/10.2.0/db_1
297 chown -R oracle:oinstall /u01/oradata
298 touch /u01/shared_config/ocr_configuration
299 ping rac1-vip
300 ping rac1-vip
301 ping -c 5 rac1-vip
302 ping -c 5 rac2-vip
303 ping -c 5 rac1-vip
304 ifconfig -a
305 vi /ect/hosts
306 vi /etc/hosts
307 vi /etc/hosts
308 cat /etc/hosts
309 hostname
310 /u01/app/crs/product/10.2.0/crs/root.sh
311 ps -ef|grep ora
312 su - oracle
313 ps -ef|grep ora
314 /etc/init.d/init.crs stop
315 ps -ef|grep ora
316 init 0
317 /u01/app/oracle/product/10.2.0/db_1/root.sh
318 ps -ef|grep ora
319 ps -ef|grep ora
320 init 0
321 vi /etc/hosts
322 pwd
323 su - oracle
324 history
325 pwd
326 pwd
327 man ping
328 cd /u01/app/crs/product/10.2.0/crs/bin/
329 crs_stat -t
330 ./crs_stat -t
331 ./crs_stat start
332 ./crs_stat restart
333 ./crs_stat stop
334 ./crstat
335 vi crstat.sh
336 sh crstat.sh
337 sh crstat.sh
338 vi crstat.sh
339 pwd
340 vi crstat.sh
341 sh crstat.sh
342 vi crstat.sh
343 sh crstat.sh
344 ./crs_stat -t
345 history

Is oracle database using a PFILE or SPFILE ?

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter
WHERE name = 'spfile';


Init File Type

------------------
SPFILE


or


SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ -------------- -----------
spfile string /opt/oracle/product/10.2/dbs/spfilecccdb.ora

Oracle connect sqlplus in comand line

sqlplus [user]/[password]@//[database_host]:[port]/[service_name]

sqlplus system/xxxxx@//192.168.2.151:1521/ora

Unix Pid to Sql

run a top command



PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
11149 oracle 1 0 0 3204M 3188M cpu/0 124:11 45.67% oracle ----<<<
11485 oracle 1 32 0 3205M 3188M cpu/0 0:49 18.47% oracle
11489 oracle 1 59 0 2352K 1864K cpu/2 0:00 0.16% top
11496 oracle 1 59 0 3204M 3187M sleep 0:00 0.09% oracle
647 oracle 15 59 0 3216M 3163M sleep 64:19 0.08% oracle
655 oracle 1 59 0 3208M 3166M sleep 60:06 0.08% oracle
847 noaccess 25 59 0 192M 54M sleep 172:23 0.07% java
4048 noaccess 25 59 0 222M 58M sleep 171:54 0.07% java
659 oracle 1 59 0 3203M 3165M sleep 54:39 0.06% oracle
639 oracle 1 59 0 3204M 3170M sleep 36:28 0.05% oracle
740 oracle 11 59 0 3207M 3176M sleep 34:36 0.05% oracle
137 root 15 59 0 8328K 1104K sleep 74:44 0.03% picld
649 oracle 21 59 0 3208M 3166M sleep 29:06 0.03% oracle
645 oracle 258 59 0 3224M 3177M sleep 35:50 0.02% oracle
744 oracle 1 59 0 3204M 3176M sleep 14:24 0.01% oracle

PID 11149 taking lot of cpu i.e. 45.67%

run this sql find out sql text, session sid and serial no


set linesize 200
col SID_serial format a15
select ses.sid||','||ses.SERIAL# SID_serial,sqa.SQL_TEXT SQL
from v$session ses, v$sqlarea sqa, v$process proc
where ses.paddr=proc.addr
and ses.sql_hash_value=sqa.hash_value
and proc.spid=&pid_sql;

if you want to kill this process then run this sql



alter system kill session 'ses.sid,ses.SERIAL# ' ;

calculate size of Undo tablespace and Undo Retention !

Formula:

Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC


set linesize 200
col "OPTIMAL UNDO RETENTION [Sec]" format 99999999999
col "UNDO RETENTION [Sec]" format a20

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/



SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/


====

from Web



set serveroutput on
set feedback off
set heading off
set lines 132

declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "C4"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b,
dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size';

begin

dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :');
dbms_output.put_line('====================================================' || chr(10));

for rec1 in get_undo_stat loop
dbms_output.put_line('A)
Adjust UNDO tablespace size according to UNDO_RETENTION :'|| chr(10));

dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||TO_CHAR(rec1.c1,'999999') || ' MEGS');

dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/60,'999999')) || ' MINUTES) ',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');

dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');

dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS)',65,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');

end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/




select 'Number of "ORA-01555 --Snapshot too old " encountered since the last startup of the instance : ' || sum(ssolderrcnt) from v$undostat;




set linesize 200
col "ACTUAL UNDO SIZE [MByte]" format 99999999
col "UNDO RETENTION [Sec]" format a25


apt-rdbms-01.us.co.uk:PTDB\sys> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
4 g.undo_block_per_sec) / (1024*1024)
5 "NEEDED UNDO SIZE [MByte]"
6 FROM (
7 SELECT SUM(a.bytes) undo_size
8 FROM v$datafile a,
9 v$tablespace b,
10 dba_tablespaces c
11 WHERE c.contents = 'UNDO'
12 AND c.status = 'ONLINE'
13 AND b.name = c.tablespace_name
14 AND a.ts# = b.ts#
15 ) d,
16 v$parameter e,
17 v$parameter f,
18 (
19 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
20 undo_block_per_sec
21 FROM v$undostat
22 ) g
23 WHERE e.name = 'undo_retention'
24 AND f.name = 'db_block_size'
25 /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
800 900 34.2421875

apt-rdbms-01.tus.co.uk:APTDB\sys> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 ROUND((d.undo_size / (to_number(f.value) *
4 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = 'UNDO'
11 AND c.status = 'ONLINE'
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
20 FROM v$undostat
21 ) g
22 WHERE e.name = 'undo_retention'
23 AND f.name = 'db_block_size'
24 /


ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
800 900 21027

we can increase the size of UNDO RETENTION to >> OPTIMAL UNDO RETENTION [Sec]



apt-rdbms-01.us.co.uk:TDB\sys> apt-rdbms-01.us.co.uk:TDB\sys> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE MB"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE MB
------------
800



-- Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;


apt-rdbms-01.tus.co.uk:APTDB\sys> SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 NOGUARANTEE
USERS NOT APPLY
JTIME79 NOT APPLY
TEMP1 NOT APPLY
APTUS_OBJ NOT APPLY

7 rows selected.


DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_ACTIONS
DBA_ADVISOR_OBJECTS


==

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24




Select a.task_id, a.task_name, a.type, a.impact_type, a.impact,
b.benefit_type, c.message
from dba_advisor_findings a, dba_advisor_recommendations b,
dba_advisor_rationale c
where a.task_id=b.task_id
and a.finding_id=b.finding_id
and a.task_id=c.task_id
and a.task_name not like 'SYS_AUTO_SQL_TUNING%'
or a.task_name not like 'ADDM%'
or a.task_name not like '%ADDM%'
or a.task_name not like '%AWR_SNAPSHO%'
-- order by a.task_id, a.task_name, a.finding_id;

Declare
ret_val varchar2(2000);
PROCEDURE Show_Message(p_Msg_in IN CLOB)
IS
BEGIN
IF LENGTH(p_Msg_in) > 255
THEN
DBMS_OUTPUT.Put_Line(SUBSTR(p_Msg_in,1,255));
Show_Message(SUBSTR(p_Msg_in,256,LENGTH(p_Msg_in)));
ELSE
DBMS_OUTPUT.Put_Line(p_Msg_in);
END IF;
END;
begin
dbms_output.enable(1000000);
ret_val:=dbms_sqltune.report_tuning_task('TASK_00004');
show_message(ret_val);
end;
/




-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('937_1135_AWR_SNAPSHOT_UNDO1') AS report FROM dual;
SET PAGESIZE 24




DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 937);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 1135);
DBMS_ADVISOR.execute_task(tname);
end;





SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('937_1135_AWR_SNAPSHOT_UNDO1')
ORDER BY f.task_name, f.impact DESC;



SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id
AND f.task_name = o.task_name
WHERE f.task_name IN ('Undo Advisor')
ORDER BY f.task_name, f.impact DESC;

Oracle 10g Estimate Tablespace growth

set pagesize 200
col tsname format a20

SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days
, ts.tsname
, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB
, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu
, DBA_HIST_TABLESPACE_STAT ts
, DBA_HIST_SNAPSHOT sp
, DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts#
AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
AND ts.tsname NOT IN ('SYSAUX','SYSTEM')
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;