cancel
Showing results for 
Search instead for 
Did you mean: 

shell script help needed.

SOLVED
Go to solution
Rajkumar_3
Regular Advisor

shell script help needed.

Hai all,

I have some calculation of creating a tablespaces.

The details are like this:
###################
I have a table "users_data".I have assigned a tablespace named "user_tbl_data" while creating a table.The row size for this table is exactly "106 bytes" .If i insert the 1000000 rows the tablesize will be 106*1000000=106 MB.I have hardcoded the 106 MB to the tablespace size.Now i need to do it as parameterized.

I used a create a complete database by running a file "install.sh" which will be calling all the database scripts to create a database.

Suppose if i run the "install.sh" file it should as
"How many rows that you want to insert into the table ? "

if i enter a value of "2000000" this value should be multipied with " 106 " and that OUTPUT value should be repaced for the tablespace size which is in another script(create_tablespace.sh) for creating a tablespaces.

The following are the contents in the following files.

#Install.sh
-----------------
./create_database.sh
./create_dict.sh
./create_tablespaces.sh etc...........

#create_tablespace.sh
----------------------------
CREATE TABLESPACE USERS_TBL_DATA
DATAFILE '/oracle/invent/users_tbl_data.dbf' SIZE <106> M
DEFAULT STORAGE (INITIAL 512K NEXT 512K MINEXTENTS 1
MAXEXTENTS 500 PCTINCREASE 1);
-------------------------------------------------------------------------

The value in the "< >" should be relaced with the result value.

Is it possible of doing that way???

Can any one can help me??

regards
ra
Oracle DBA
11 REPLIES
Robin Wakefield
Honored Contributor

Re: shell script help needed.

Hi Rajkumar,

In install.sh, have something like:

"How many rows do you want to insert into the table ? "
read NROWS
NROWS=$((NROWS * 106))
export NROWS



and in your create_tablespace.sh:

DATAFILE '/oracle/invent/users_tbl_data.dbf' SIZE <$NROWS> M

Rgds, Robin
Steven Sim Kok Leong
Honored Contributor

Re: shell script help needed.

Hi,

install.sh:
========================================
echo "How many rows that you want to insert into the table ? \c"
read total_rows
./create_tablespaces.sh $total_rows
========================================


create_tablespaces.sh
========================================
total_size=$(expr 2000000 \* $1)

svrmgrl <CREATE TABLESPACE USERS_TBL_DATA
DATAFILE '/oracle/invent/users_tbl_data.dbf' SIZE $total_size M
DEFAULT STORAGE (INITIAL 512K NEXT 512K MINEXTENTS 1
MAXEXTENTS 500 PCTINCREASE 1);
EOF
========================================

Is this what you are looking for?

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
Steven Sim Kok Leong
Honored Contributor

Re: shell script help needed.

Hi,

Typo error.

Replace: total_size=$(expr 2000000 \* $1)

With: total_size=$(expr 106 \* $1)

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
Rajkumar_3
Regular Advisor

Re: shell script help needed.

hai all,

Thank you for your replies.The solution which you have given is correct but there is some problem in that also.

When i run the script i have entered 1000000 rows .
and the output in the $NROWS=53000000.when this value is substituting at the run time I got an oracle error saying that :INVALID FILE SIZE.

Because the oracle datablock size=4194303 blocks.
now the output is more than that.So when we enter the value it should convert the size in MB.so the problem will be solved.
I mean $NROWS=53000000 bytes.If we divide it by 1000000 we will get the $NROWS=53

can you please tell me how to How to convert it through script.

Ra
Oracle DBA
Steven Sim Kok Leong
Honored Contributor

Re: shell script help needed.

Hi,

Replace:

total_size=$(expr 106 \* $1)

With:

total_size=${expr 106 \* $1)
total_size=$(expr $total_size / 1048576)

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
Rajkumar_3
Regular Advisor

Re: shell script help needed.

Hi sim Kok,

I executed as said above but i am getting syntax error.
I am just testing it one script.

Script:
-------
echo "How many rows that you want to insert into the table ? \c"
read total_size
total_size=$(expr 106 \* $1)
total_size=$(expr $total_size / 1048576)
echo $total_size

Can you please solve it.

Raj
Oracle DBA
Steven Sim Kok Leong
Honored Contributor

Re: shell script help needed.

Hi,

Notice that I use $1 in createtablespaces.sh. If you use it in the parent script, you need to use the variable total_size instead.

Hope this helps. Regards.

Test output:
=====================================
$ cat ./test.sh
echo "How many rows that you want to insert into the table ? \c"
read total_size
total_size=$(expr 106 \* $total_size)
total_size=$(expr $total_size / 1048576)
echo $total_size

$ ./test.sh
How many rows that you want to insert into the table ? 4000000
404
=====================================
Email: steven@beepz.com. Homepage: https://www.beepz.com
Rajkumar_3
Regular Advisor

Re: shell script help needed.

Hi Kok,

Its creating now ,But only problem in the calculation only.

what is this value "1048576" used for? From where you got this value??

If i enter 10000 instead of 1 million its creating with 10 MB ,but as per the calculation it should create with 1 MB.

Allways the input may not be 1 million it may more or very less.According to that is it possible to make it out.

Raj
Oracle DBA
Steven Sim Kok Leong
Honored Contributor

Re: shell script help needed.

Hi,

1048476 is 1 MB.

You mentioned that

>> So when we enter the value it should convert the size in MB.so the problem will be solved.
>> I mean $NROWS=53000000 bytes.If we divide it by 1000000 we will get the $NROWS=53

It depends on your definition of 1 MB, replace 1048476 with 1000000 if appropriate.

Hope this helps. Regards.

Steven Sim Kok Leong
Email: steven@beepz.com. Homepage: https://www.beepz.com
Rajkumar_3
Regular Advisor

Re: shell script help needed.

Hi Kok,

Thank you for the information.
I have done some modifications in the scripts.Its working now.

#I used the following script:

echo "How many rows do you want to insert into the table ? "
read NROWS
NROWS=$((NROWS * 106))
NROWS=$((NROWS / 2 ))
export NROWS
echo $NROWS

I divided NROWS /2 because i assigned 2 tablespaces while creating that table which contains HASH PARTITIONED tablespaces.The output result should be divided into 2 tablespaces,I havent specified MB there.I removed the (MB) while creating a tablespace.

#The following is the tablespace scipt which i used.

CREATE TABLESPACE GATE_DATA_PARTITION
DATAFILE '/inventory/gate_data_partition.dbf' SIZE $NROWS
DEFAULT STORAGE (INITIAL 20K NEXT 20K MINEXTENTS 2 MAXEXTENTS 100 PCTINCREASE 1)
;

CREATE TABLESPACE GATE_DATA_PARTITION1
DATAFILE '/inventory/gate_data_partition1.dbf' SIZE $NROWS
DEFAULT STORAGE (INITIAL 20K NEXT 20K MINEXTENTS 2 MAXEXTENTS 100 PCTINCREASE 1)
;
##########

If i havent entered any values while it prompts its returning an error.
Is it possible to terminate the entire script if i wont enter any thing????

R
Oracle DBA
Steven Sim Kok Leong
Honored Contributor
Solution

Re: shell script help needed.

Hi,

Add an error checking condition.

=========================================
echo "How many rows that you want to insert into the table ? \c"
read total_size
if [ "$total_size" != "" ]
then
total_size=$(expr $total_size / 1048576)
echo $total_size
else
echo No values entered...exiting
fi
=========================================
Hope this helps. Regards.

Steven Sim Kok Leong

Email: steven@beepz.com. Homepage: https://www.beepz.com