Need help in a shell script to edit a tablespace creation script.

Shell Programming and Scripting
1

Hi,

CREATE TABLESPACE aps_blob_large01
DATAFILE '/c2r6u13/u03/oradata/qnoldv01/aps_blob_large0101.dbf' SIZE X 270532608
REUSE
DEFAULT STORAGE (INITIAL 134217728 NEXT 134217728
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
ONLINE
/

CREATE TABLESPACE aps_data_medium01
DATAFILE '/c2r6u13/u03/oradata/qnoldv01/aps_data_medium0101.dbf' SIZE X 52428800
REUSE
DEFAULT STORAGE (INITIAL 4194304 NEXT 4194304
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
ONLINE
/

CREATE TABLESPACE aps_data_small01
DATAFILE '/c2r6u13/u03/oradata/qnoldv01/aps_data_small0101.dbf' SIZE X 104857600
REUSE
DEFAULT STORAGE (INITIAL 131072 NEXT 131072
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
ONLINE
/

I have about 150 tablespace creation scripts of which I have listed 3.
I need help in writing a shell script to replace the number after SIZE X with 100M in the above mentioned script.

The output should be as follows after running the script into a new file.

CREATE TABLESPACE aps_blob_large01
DATAFILE '/c2r6u13/u03/oradata/qnoldv01/aps_blob_large0101.dbf' SIZE 100M
REUSE
DEFAULT STORAGE (INITIAL 134217728 NEXT 134217728
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
ONLINE
/

CREATE TABLESPACE aps_data_medium01
DATAFILE '/c2r6u13/u03/oradata/qnoldv01/aps_data_medium0101.dbf' SIZE 100M
REUSE
DEFAULT STORAGE (INITIAL 4194304 NEXT 4194304
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
ONLINE
/

CREATE TABLESPACE aps_data_small01
DATAFILE '/c2r6u13/u03/oradata/qnoldv01/aps_data_small0101.dbf' SIZE 100M
REUSE
DEFAULT STORAGE (INITIAL 131072 NEXT 131072
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0)
ONLINE
/

Thanks a lot in advance.

Ram.

2

Open the file in "vi"

:1,$s/SIZE X .*$/SIZE 100M/

3

Hi FlyingSquirrel,

Thanks a lot for all the help.

Attached are the 2 files mentioned below:

create_tablespaces.sql has the original file.
ram_test_tbs.sql has the output from the code provided by you.

If I look at the output file not all the tablespace creation scripts have been changed. Could you please help me figure out what I have done wrong in my original file create_tablespaces.sql ?

Thanks,

Ram.