Require help to sort string

Shell Programming and Scripting
1

Hi Folks,

Currently am working with 10g db. I want to sort the below o/p.
Current output:

SQL> select partition_name from user_tab_partitions where table_name='USER_AUDIT';

PARTITION_NAME
------------------------------
PARTMAX
PART_AUDIT_NOV02
PART_AUDIT_NOV08
PART_AUDIT_NOV14
PART_AUDIT_OCT27

Expected output:

PART_AUDIT_OCT27
PART_AUDIT_NOV02
PART_AUDIT_NOV08
PART_AUDIT_NOV14
PARTMAX

becasue am writing a script to drop a oldest partition automatically through a shell script. Can anybody help me?

2

If this is to be a regular process it is very complex because of the alphabetic month abbreviations and the absence of the year in the data.
Surely this would be better as a full blown SQL program which could deduce the year, translate the data into a sortable format, sort to order and run the required SQL.

3

Hi methyl,

or I can select another one column also with that partition name as below.

SQL> select partition_name,high_value from user_tab_partitions where table_name='USER_AUDIT';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
PARTMAX MAXVALUE
SAS_AUDIT_NOV02 TO_DATE(' 2009-11-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV08 TO_DATE(' 2009-11-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV14 TO_DATE(' 2009-11-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_OCT27 TO_DATE(' 2009-10-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

is there any way to sort with the help of high value column?

---------- Post updated 11-11-09 at 02:21 AM ---------- Previous update was 11-10-09 at 01:26 PM ----------

hi Folks,

Can anybody help me to sort the below output?

PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SAS_AUDIT_NOV02 TO_DATE(' 2009-11-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV08 TO_DATE(' 2009-11-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV14 TO_DATE(' 2009-11-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_OCT27 TO_DATE(' 2009-10-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

This output will be stored in a file. From that I can print the date only as below by using cat output.lst|awk '{print $3}'|sort -n

2009-10-27
2009-11-02
2009-11-08
2009-11-14

Now I need to take the partition_ name of oldest date. In this scenario, the oldest date is 2009-10-27 and corresponding partition_name is SAS_AUDIT_OCT27. So I need the output as below.

Required output:

SAS_AUDIT_OCT27

4

Maybe try keeping the date with the partition name during the sort?

cat output.lst|awk '{print $3,$1}' | sort -n | head -1 | awk '{print $2}'
5

Thanks methyl. I selected the partition name through the way as below.

partition_name=sed '/^$/d' partition_name.lst|awk '{print $3,$1}'|sort -n|head -1|awk '{print $2}'
echo $partition_name

The output I got SAS_AUDIT_OCT27

Now I need to drop this partition.

to connect oracle and execute a query, am using the following way.

export ORACLE_SID=test
export ORACLE_HOME=/oracle/product/10.1.0.5
export PATH=$PATH:/oracle/product/10.1.0.5/bin
sqlplus -s username/pwd << EOF
alter table table_name drop partition partition_name;
exit;
EOF

now how can I bring the partition name(partition name stored in $partition_name) into oracle?

Pls help