Hi ,
Can any one help me out how to remove space from below line
select file_name from dba_data_files where tablespace_name='SYSTEM ----space---- ';
i want as :
select file_name from dba_data_files where tablespace_name='SYSTEM';
my code i use
file=/u01/script/latest/tbs.temp
while IFS= read -r LINE; do
echo "select file_name from dba_data_files where tablespace_name='$LINE';" >> /u01/script/latest/tbs.sql
done < "$file"
Please use code tags for code fragments or data samples.
How about using trim function?
select file_name from dba_data_files where tablespace_name = trim('SYSTEM ');
OR remove it in the script:-
echo "select file_name from dba_data_files where tablespace_name = '${LINE%% *}';" >> /u01/script/latest/tbs.sql
Hi Boss
Thanks a lot
but still issue is same
[oracle@localhost latest]$ cat tbs.sql
select file_name from dba_data_files where tablespace_name = SYSTEM---space--- ;
select file_name from dba_data_files where tablespace_name = SYSAUX----space---- ;
Note that you are appending to tbs.sql , hence check last lines in this file:-
tail tbs.sql | cat -evt
If last lines data is also with spaces, post what exactly you did including code and data.
cd /u01/script/latest/
rm -rf check_tablespace.temp datafiles.log sql.log tbs.sql tbs.temp
sqlplus -s / as sysdba << eof > /u01/script/latest/check_tablespace.temp
set pages 0
Select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from (select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
Select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;
exit
eof
cd /u01/script/latest/
chmod ugo+rw /u01/script/latest/check_tablespace.temp
counter=`cat check_tablespace.temp | sed '/^$/d'| wc -l`
echo "count="$counter
#imaintbs=`awk '{print $1}' check_tablespace.temp`
maintbs=$(cat /u01/script/latest/check_tablespace.temp | cut -d' ' -f1 | sed '/^\s*$/d' >>/u01/script/latest/tbs.temp)
#echo $maintbs >> /u01/script/latest/tbs.temp
echo $maintbs
echo "THESE ARE THE SQL FILES PRESENT IN THE GIVEN PATCH:"
var1=$1
echo "Do You Want To APPLY Sql FILE????"
RESPONSE1=1
while :
do
read RESPONSE
if [ "$RESPONSE" == "YES" -o "$RESPONSE" == "yes" ]
then
file=/u01/script/latest/tbs.temp
while IFS= read -r LINE; do
#echo "select file_name from dba_data_files where tablespace_name='$LINE';" >> /u01/script/latest/tbs.sql
echo "select file_name from dba_data_files where tablespace_name = ${LINE%%};" >> /u01/script/latest/tbs.sql
done < "$file"
sqlplus -s / as sysdba << eof > /u01/script/latest/datafiles.log
@/u01/script/latest/tbs.sql
eof
exit
rm -rf /u01/script/latest/tbs.temp
rm -rf /u01/script/latest/tbs.sql
exit
RESPONSE=$?
echo "SQL DEPLOYMENT COMPLETED"
elif [ "$RESPONSE" == "NO" -o "$RESPONSE" == "no" ]
then
echo "sql not applied"
exit
RESPONSE1=$?
else
echo "Please Enter correct choice"
fi
if [ $RESPONSE1 -eq 0 ]
then
break
fi
done
This is not what I posted:-
echo "select file_name from dba_data_files where tablespace_name = ${LINE%%};" >> /u01/script/latest/tbs.sql
Include the blank space followed by asterisk:-
echo "select file_name from dba_data_files where tablespace_name = '${LINE%% *}';" >> /u01/script/latest/tbs.sql
facing same error
Post the output of below after running the modified script:-
tail /u01/script/latest/tbs.sql | cat -evt
select file_name from dba_data_files where tablespace_name = 'SYSTEM^I^I^I^I';$
select file_name from dba_data_files where tablespace_name = 'SYSAUX^I^I^I^I';$
Ok, replace below line in your code:-
while IFS= read -r LINE; do
to
while read LINE; do
Thanks boss
working thanks a lot. salute to you
got out from one issue will face many more.
thanks for help
---------- Post updated at 12:17 AM ---------- Previous update was at 12:04 AM ----------
Hi,
can u please tell me :
i want to cut only path
eg:
/u01/app/oracle/oradata/DB11G/system01.dbf
and i want only path not file name whatever may be length of path. it should only give path not file name.
it should give
/u01/app/oracle/oradata/DB11G/
man dirname
Assuming it is stored in variable pathstring , the following does it
${pathstring%/*}/
[oracle@localhost latest]$ cat check_tablespace.temp
USERS 5 960 18.75 0
SYSAUX 630 130688 20.2579365 -38
EXAMPLE 100 21760 21.25 -7
SYSTEM 890 209984 23.0407303 -84
UNDOTBS1 95 55744 57.3026316 -47
NOONE 1024 1047552 99.9023438 -1022
no when cut and get output from check_tablespace.temp
i get
USERS 5
SYSAUX
EXAMPLE
SYSTEM
UNDOTBS1
NOONE
6
i dont want 5 and 6
---------- Post updated at 12:08 PM ---------- Previous update was at 12:07 PM ----------
$(cat /u01/script/latest/check_tablespace.temp | cut -d' ' -f1 | sed '/^\s*$/d' >>/u01/script/latest/tbs.temp)
this is what i use to trim my log file
With an input file as shown in post#14, how far would
while read TSNAME TOT PCT MIN; do . . .
get you?
But, why don't you adapt your sql query in the first place, like
select 'select file_name from dba_data_files where tablespace_name = ''' a.tablespace_name ''';'
(my sql has become somewhat rusty, so you may need to fiddle around a bit, e.g. with the single quotes...)