Oracle Database Daily Growth check Script

Oracle Database Daily Growth check

As a DBA, it was often asked me how the database grows and when we'll full up the disc space.
Now, when i create a database, i automatically add a job to follow the space occupation.

I give you my tip. It's in three parts:
1- a table, to record the statistics
2- a Procedure 
3- a job which execute the procedure, run every day at midnight.


1) the table
===========
CREATE TABLE watch_file (
heure DATE,
tablespace_name VARCHAR2(30),
file_name VARCHAR2(513),
total_bytes NUMBER,
used_bytes NUMBER,
max_bytes NUMBER,
ok NUMBER(1)
)
/
CREATE INDEX watch_file_idx1 ON watch_file (tablespace_name, heure)
/
CREATE INDEX watch_file_idx3 ON watch_file (heure)
/



grant select on dba_extents to halim
grant select on dba_tablespaces to halim
grant select on dba_data_files to halim



2) the procedure
=================

create or replace procedure DB_growth_check_daily is

CURSOR curs IS
SELECT ts.tablespace_name, f.file_name, f.BYTES,
NVL (ext.BYTES, 0) + f.BYTES - f.user_bytes used_bytes,
DECODE (f.autoextensible,
'YES', f.maxbytes,
f.BYTES
) max_bytes
FROM dba_tablespaces ts,
dba_data_files f,
(SELECT file_id, SUM (BYTES) BYTES
FROM dba_extents
GROUP BY file_id) ext
WHERE f.tablespace_name = ts.tablespace_name AND ext.file_id(+) =
f.file_id
ORDER BY 1, 2;

depart DATE;
s_ok number:=0;
BEGIN

depart := SYSDATE;

FOR rec IN curs LOOP
s_ok:=s_ok+1 ;
INSERT INTO watch_file
(heure, tablespace_name, file_name, total_bytes,
used_bytes, max_bytes, ok
)
VALUES (depart, rec.tablespace_name, rec.file_name, rec.BYTES,
rec.used_bytes, rec.max_bytes,s_ok
);
END LOOP;

COMMIT;

END;



4)   run or (make a job) with the procedure
================================

SQL> exec DB_growth_check_daily ;

How to increase site traffic/hits

How to increase site traffic/hits
---------------------------------
Website traffic or visitor is an important thing for webmasters. It’s not hard to derive traffic if you follow some simple tips and tricks. Those tips will help you to derive massive traffic in your site within few days. I will discuss those tips in the following.

1. Original Content: Write always original content and avoid duplicate content for your site. Search engine will penalize your duplicate contents. Write yourself whatever you know from reading articles or searching sites. Try to keep your article’s words minimum 200 words. It will take 15 to 30 minutes to write a 200 words article. Write daily what interests you. But best will be chose a specific niche such as sports, health, relation, food, travel etc. So remember original content is king to derive website traffic.

2. Keywords: Write keywords on your article’s title and first paragraph of your article. Also try to write those keywords at the last paragraph of your article.

3. Inbound link: Keep inbound link in your content. Inbound link is to link some keywords with your other article or any other site.

4. Submit sitemap to search engine: It’s very important to submit your sitemap to popular search engines like google, yahoo and msn. You can know the way of submitting site map from searching web.

5. Submit to Article directory: Write an article according to your niche and submit it to some number of article directories. Those article directories will allow you to keep your site link and after reading your article visitor will come to your site clicking the link. Some popular article directories are Ezine, ArticlesBase, Isnare etc.

6. Join Forums: Join some forum site related to your niche and participate conversation with the members of the forum site. Keep your site or article link at the end of your speech.

7. Join Answer site: It easy to derive traffic by joining answering site. On those site you can find to many topics and members are asking questions for an specific topic. Answer the topic of your niche and keep your site link as source of your answer.

Those are the most effective and basic tips to derive traffic and if you follow those tips you will not have to wait for traffic.

Oracle Forums

Some Oracle Forums
--------------------

http://forums.oracle.com/forums/main.jspa?categoryID=84
http://asktom.oracle.com/pls/asktom/f?p=100:1:0::NO
http://www.orafaq.com/forum
http://www.dba-village.com/village/dvp_base.main
http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l?reftrk=no&cid=780360
http://www.club-oracle.com/forums/

Gather statistics on table and schema

SELECT * FROM dba_tab_modifications


SELECT * FROM user_tab_modifications


CREATE OR REPLACE PROCEDURE HALIM_GATHER_STAT_SCHEMA
IS
BEGIN
begin
dbms_stats.unlock_schema_stats(ownname=> '"TEST"');
end;

dbms_stats.gather_schema_stats (
ownname => 'TEST',
options => 'GATHER',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade => true,
degree => 5
);

begin
dbms_stats.lock_schema_stats( ownname=> '"TEST"' );
end;

END;



EXEC dbms_stats.gather_schema_stats (
ownname => 'MYBANK',
options => 'GATHER',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size auto',
cascade => true,
degree => 5
);



SELECT a.owner, a.table_name, a.avg_row_len, a.blocks, a.last_analyzed,
a.num_rows, a.tablespace_name, a.row_movement, a.sample_size
FROM all_tables a
WHERE last_analyzed IS NOT NULL AND owner = 'TEST'
ORDER BY a.last_analyzed DESC


SELECT a.owner, a.table_name, a.index_name, a.last_analyzed, a.num_rows,
a.tablespace_name, a.sample_size
FROM all_indexes a
WHERE last_analyzed IS NOT NULL AND owner = 'TEST'
ORDER BY a.last_analyzed DESC

TO ABOID EXP-00091 MESSAGE

-------TO ABOID EXP-00091 MESSAGE (QUESTIONABLE STATISTICS)
SET NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

exp userid=basel2/basel2@report owner=basel2 file=D:\basel_dump\basel2_30072009.dmp