shell script help needed.

[Rajkumar_3]

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

[Robin Wakefield]

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]

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

[Steven Sim Kok Leong]

Hi,

Typo error.

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

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

Hope this helps. Regards.

Steven Sim Kok Leong

[Rajkumar_3]

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

[Steven Sim Kok Leong]

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

[Rajkumar_3]

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

[Steven Sim Kok Leong]

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
=====================================

[Rajkumar_3]

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

[Steven Sim Kok Leong]

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