Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Script to count the dataflow in a table in oracle database
Top Forums Shell Programming and Scripting Script to count the dataflow in a table in oracle database Post 302605223 by Mohammed Fareed on Wednesday 7th of March 2012 02:50:20 AM
Old 03-07-2012
You may can find with the help of DBA_TABLES (view) the table which is placed in the tablespace.

Calculating the dataflow per hour,day,week?
---Check the logswitches per day with given below query hope it will helpfull.
Note:- It will not give you insertion,deletion,updation of the data.but gives you total transction of the database.
Code:
#####################################################
-- Author : Mohammed Fareed.
-- Script : log_switch.sql
-- Description : Get log switches on total , daily and hourly basis 
set heading off;
select '******************************************************' from dual;
select '****  Redolog Daily and Hourly volume calculated  ****' from dual;
select '******************************************************' from dual;
timing start Redovol;

--#######################################################################################
--##           PL/SQL used here to gather and display average redo volumes             ##
--#######################################################################################
set serveroutput on;
declare
v_log    number;
v_days   number;
v_logsz  number;
v_adsw   number;
V_advol  number;
v_ahsw   number;
v_ahvol  number;

begin
select count(first_time) into v_log from v$log_history;
select count(distinct(to_char(first_time,'dd-mon-rrrr'))) into v_days from v$log_history;
select max(bytes)/1024/1024 into v_logsz from v$log;
v_adsw := round(v_log / v_days);
v_advol := round(v_adsw * v_logsz);
v_ahsw := round(v_adsw / 24);
v_ahvol := round((v_adsw / 24 )) * v_logsz;
dbms_output.put ('Total Switches' || ' '||v_log||'  ==>  ');
dbms_output.put ('Total Days' || ' '|| v_days||'  ==>  ');
dbms_output.put_line ('Redo Size' || ' ' || v_logsz);
dbms_output.put ('Avg Daily Switches' || ' ' || v_adsw||'  ==>  ');
dbms_output.put_line ('Avg Daily Volume in Meg' || ' ' || v_advol);
dbms_output.put ('Avg Hourly Switches' || ' ' || v_ahsw||'  ==>  ');
dbms_output.put_line ('Avg Hourly Volume in Meg' || ' ' || v_ahvol);

end;
/
--#######################################################################################
--##                                                  END of PL/SQL routine            ##
--#######################################################################################
 
timing stop Redovol;
 
###########################################################

Last edited by Franklin52; 03-07-2012 at 05:12 AM.. Reason: Please use code tags for code and data samples, thank you
Mohammed Fareed
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

export table from oracle database

i would like to export a particular table in my oracle database installed in a hpux box. i would like to determine the filesize of the output before performing these action so i can assess if my harddisk can still handle it. thanks as usuall :rolleyes: (1 Reply)
Discussion started by: inquirer
1 Replies

2. Shell Programming and Scripting

update a oracle table using shell script

Hi, I would like to know how to update a table in Oracle database, if a command in one shell script either successfully completes or it fails.(like Y if its success or N if its a failure) While the command is running,I am able to view the log file created in the Unix machine.After the command... (2 Replies)
Discussion started by: ann_124
2 Replies

3. UNIX for Advanced & Expert Users

Delete database table based on months using script.

HI All, Newbie here. I have a script which generates the tables in teradata database with time stamp like below.I want to add some more code to delete the archive tables which are 2months old. . $HOME/.profile t_name=$1 procdate=`date +%y%m%d` log_dir=$Folder/log tab=`echo... (0 Replies)
Discussion started by: Maverick79
0 Replies

4. HP-UX

to create a oracle table in perl script

Hi all, I have to create table for each month inside a perl script. tablename_monthnameyear. megh_aug2008 for august 2008. megh_sep2008 for september 2008. just like the logfiles created on date basis. thanks megh (1 Reply)
Discussion started by: megh
1 Replies

5. Shell Programming and Scripting

Check the record count in table (table in oracle)

I have requirement: 1) Check the record count in table (table in oracle) 2) If records exists generate the file for existing records and wait for some time (Go to sleep mode) and Again check the record count after 10 min.......... (Loop this process if record count >0). 3) Generate touch... (1 Reply)
Discussion started by: kamineni
1 Replies

6. Shell Programming and Scripting

Check the record count in table (table in oracle)

I have requirement: 1) Check the record count in table (table in oracle) 2) If records exists generate the file for existing records and wait for some time then Go to sleep mode and Again check the record count after 10 min.......... (Loop this process if record count >0). 3) Generate touch... (1 Reply)
Discussion started by: kamineni
1 Replies

7. Shell Programming and Scripting

Sample ksh script for copy the data from excel to database table ?

Hi All, I need to convert the data from excel to database table in sybase. Please provide some sample script.. thanks, Royal. (1 Reply)
Discussion started by: royal9482
1 Replies

8. Shell Programming and Scripting

Insert script result into Oracle Table

Hi All, I want to insert STAT and ENDTIME values for each job in joblist into TBL_DAILY_STATUS table. Eg: insert into tbl_daily_status values(STAT,ENDTIME); Please help me on this. #!/bin/ksh joblist="com_abc_job com_abc_dot_job com_abc_seq com_abc_det" for i in $joblist do... (8 Replies)
Discussion started by: vichuelaa
8 Replies

9. Shell Programming and Scripting

UNIX Script required for count the records in table

Hi Friends, I looking for the script for the count of the records in table. and then it's containg the zero records then should get abort. and should notify us through mail. Can you please help me out in this area i am lacking. (5 Replies)
Discussion started by: victory
5 Replies

10. Shell Programming and Scripting

Intelligent Script to Insert Records in Database Table

Hello All Members, I am new to this forum and to the shell scripting. I want to write a shell script to do the following: Scenario: I have a pipe delimited .txt file with multiple fields in it. The very first row of the file contains the name of the column which resembles the name of the... (18 Replies)
Discussion started by: chetanojha
18 Replies

LEARN ABOUT REDHAT

clusterdb

CLUSTERDB(1)						  PostgreSQL Client Applications					      CLUSTERDB(1)

NAME

       clusterdb - cluster a PostgreSQL database

SYNOPSIS

       clusterdb [ connection-options... ] [ --table | -t table  ] [ dbname ]

       clusterdb [ connection-options... ] [ --all | -a ]

DESCRIPTION

       clusterdb  is a utility for reclustering tables in a PostgreSQL database. It finds tables that have previously been clustered, and clusters
       them again on the same index that was last used. Tables that have never been clustered are not touched.

       clusterdb is a shell script wrapper around the backend command CLUSTER [cluster(7)] via the PostgreSQL interactive terminal psql(1).  There
       is  no effective difference between clustering databases via this or other methods.  psql must be found by the script and a database server
       must be running at the targeted host. Also, any default settings and environment variables  available  to  psql	and  the  libpq  front-end
       library do apply.

       clusterdb  might  need  to  connect  several  times  to	the PostgreSQL server, asking for a password each time. It is convenient to have a
       $HOME/.pgpass file in such cases.

OPTIONS

       clusterdb accepts the following command-line arguments:

       -a

       --all  Cluster all databases.

       [-d] dbname

       [--dbname] dbname
	      Specifies the name of the database to be clustered.  If this is not specified and -a (or --all) is not used, the	database  name	is
	      read from the environment variable PGDATABASE. If that is not set, the user name specified for the connection is used.

       -e

       --echo Echo the commands that clusterdb generates and sends to the server.

       -q

       --quiet
	      Do not display a response.

       -t table

       --table table
	      Clusters table only.

       clusterdb also accepts the following command-line arguments for connection parameters:

       -h host

       --host host
	      Specifies  the host name of the machine on which the server is running. If host begins with a slash, it is used as the directory for
	      the Unix domain socket.

       -p port

       --port port
	      Specifies the Internet TCP/IP port or local Unix domain socket file extension on which the server is listening for connections.

       -U username

       --username username
	      User name to connect as

       -W

       --password
	      Force password prompt.

DIAGNOSTICS

       CLUSTER
	      Everything went well.

       clusterdb: Cluster failed.
	      Something went wrong. clusterdb is only a wrapper script. See CLUSTER [cluster(7)] and psql(1) for a detailed  discussion  of  error
	      messages and potential problems. Note that this message may appear once per table to be clustered.

ENVIRONMENT

       PGDATABASE

       PGHOST

       PGPORT

       PGUSER Default connection parameters.

EXAMPLES

       To cluster the database test:

       $ clusterdb test

       To cluster a single table foo in a database named xyzzy:

       $ clusterdb --table foo xyzzy

SEE ALSO

       CLUSTER [cluster(7)]

Application							    2002-11-22							      CLUSTERDB(1)
All times are GMT -4. The time now is 11:54 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy