SQL styling and formatting !!!!!

-- Increase the size for long DDL outputs

SET LONG 10000;           -- Allows displaying long DDL statements

SET LONGCHUNKSIZE 1000;   -- Controls how LONG data is fetched and displayed

-- Formatting options

SET LINESIZE 200;         -- Adjusts output width for better readability

SET PAGESIZE 100;         -- Controls the number of lines per page (0 removes headers)

SET TRIMOUT ON;           -- Removes trailing spaces from output

SET TRIMSPOOL ON;         -- Removes trailing spaces when spooling output

SET WRAP OFF;             -- Prevents line wrapping in output

-- Improve output structure

SET COLSEP '|';           -- Adds a column separator for better visibility

SET UNDERLINE '-';        -- Adds an underline separator under column headers

-- Display settings

SET SERVEROUTPUT ON;      -- Enables DBMS_OUTPUT messages

SET VERIFY OFF;           -- Suppresses display of substitution variable values

SET FEEDBACK OFF;         -- Removes row count messages from the output

SET HEADING ON;           -- Ensures column headers are displayed

SET ECHO OFF;             -- Hides command execution in the output

-- If using spooling (saving output to a file)

SPOOL my_output.txt;

-- Your SQL queries here...

SPOOL OFF;

--For DDL Queries:

SET LONG 10000;

SET PAGESIZE 0;

SET LINESIZE 200;

SET TRIMOUT ON;

SET WRAP OFF;

--For Tabular query result

SET PAGESIZE 100;

SET LINESIZE 200;

SET COLSEP '|';

SET UNDERLINE = '-';

------------------------------------------

/* For exporting result to a file */

SET LONG 10000;

SET PAGESIZE 0;

SET LINESIZE 200;

SET TRIMOUT ON;

SET WRAP OFF;

SPOOL my_output.txt;

-- Your queries here...

SPOOL OFF;

--------------------------------------------

2025-03-12 00:11:11 ERROR OGG-01169 Encountered an update where all key columns for target table SCHEMA.TABLE_NAME are not present

 To find primary key of a table:

SQL>

select cols.column_name

from dba_constraints cons

join dba_cons_columns cols

on cons.sonstraint_name = cols.constraint_name

where cons.constraint_type in ('U','P')

and cons.owner = '&SCHEMA'

and cons.table_name = '&TABLE_NAME';

to get ddl for indexes for a table on source :

SQL>

set long 5000;

set pages 0;

select dbms_metadata.get_ddl('INDEX',u.index_name,'SCHEMA') from dba_indexes u where

table_name='&table_name' ;

STEPS TO PERFORME ON TARGET DB AND SOURCE DB:

1) target gg db:

GGSCI > sh cat dirrpt/REPLICAT_NAME.rpt

2025-03-12 00:11:11 ERROR OGG-01169 Encountered an update where all key columns for

target table SCHEMA.TABLE_NAME are not present

2) GGSCI > edit param REPLICAT_NAME

...

2.1 on target db checked for primary key and unique key columns

    then take the ddl for the indexes on the table SCHEMA.TABLE_NAME on source db

2.2 create source indexes on  target

2.3 then add below lines in REPLICAT_NAME.prm

    map SCHEMA.TABLE_NAME, target SCHEMA.TABLE_NAME, keycols (column_pk,column_uk) ;

2.4 start target abended REPLICAT REPLICAT_NAME

    GGSCI > start REPLICAT_NAME

INDEX REBUILD TIME SQL

select sql_text,sql_id from gv$sqlstats where sql_text like '%SCHEMA.INDEX_NAME%';

abfxhr2v2bqgf 

select sid,serial# from gv$session_longops where sql_id='abfxhr2v2bqgf ';

SID             SERIAL#

----------------------------

2200           44444

select round(time_remaining/60) time_remaining_mins from gv$session_longops where sid='2200' and serial# = '44444';

TIME_REMAINING_MINS

----------------------------------

33

DBMS_METADATA package to extract objects DDL

-----Always set below for getting full query result

set long 5000;

set pages 0;

-- 1. Get DDL for all Tables in the HR schema

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, 'HR')

FROM ALL_TABLES

WHERE OWNER = 'HR';

-- 2. Get DDL for all Indexes in the HR schema

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, 'HR')

FROM ALL_INDEXES

WHERE OWNER = 'HR';

-- 3. Get DDL for all Constraints in the HR schema

(Primary Key, Unique, Foreign Key, Check)

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', CONSTRAINT_NAME, 'HR')

FROM ALL_CONSTRAINTS

WHERE OWNER = 'HR' AND CONSTRAINT_TYPE IN ('P', 'U', 'R', 'C');

-- 4. Get DDL for all Sequences in the HR schema

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', SEQUENCE_NAME, 'HR')

FROM ALL_SEQUENCES

WHERE SEQUENCE_OWNER = 'HR';

-- 5. Get DDL for all Views in the HR schema

SELECT DBMS_METADATA.GET_DDL('VIEW', VIEW_NAME, 'HR')

FROM ALL_VIEWS

WHERE OWNER = 'HR';

-- 6. Get DDL for all Synonyms in the HR schema

SELECT DBMS_METADATA.GET_DDL('SYNONYM', SYNONYM_NAME, 'HR')

FROM ALL_SYNONYMS

WHERE OWNER = 'HR';

-- 7. Get all Roles assigned to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'HR') FROM DUAL;

-- 8. Get all System Privileges granted to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'HR') FROM DUAL;

-- 9. Get all Object Privileges granted to HR user

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'HR') FROM DUAL;

-- 10. Remove storage, tablespace, and segment attributes from DDL output

BEGIN

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);

  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', TRUE);

END;

/

-- DDL for Tables

SELECT DBMS_METADATA.GET_DDL('TABLE', u.table_name, 'SCHEMA')  

FROM dba_tables u  

WHERE owner = 'SCHEMA'

AND u.table_name = 'OBJECT';  

-- DDL for Indexes

SELECT DBMS_METADATA.GET_DDL('INDEX', u.index_name, 'SCHEMA')  

FROM dba_indexes u

WHERE owner = 'SCHEMA'

AND u.index_name = 'OBJECT';  

-- DDL for Views

SELECT DBMS_METADATA.GET_DDL('VIEW', u.view_name, 'SCHEMA')  

FROM dba_views u

WHERE owner = 'SCHEMA'

AND u.view_name = 'OBJECT';  

-- DDL for Sequences

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', u.sequence_name, 'SCHEMA')  

FROM dba_sequences u

WHERE owner = 'SCHEMA'

AND u.sequence_name = 'OBJECT';  

-- DDL for Procedures

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PROCEDURE'

AND u.object_name = 'OBJECT';  

-- DDL for Functions

SELECT DBMS_METADATA.GET_DDL('FUNCTION', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'FUNCTION'

AND u.object_name = 'OBJECT';  

-- DDL for Packages

SELECT DBMS_METADATA.GET_DDL('PACKAGE', u.object_name, 'SCHEMA')  

FROM dba_procedures u

WHERE owner = 'SCHEMA'  

AND object_type = 'PACKAGE'

AND u.object_name = 'OBJECT';  

-- DDL for Triggers

SELECT DBMS_METADATA.GET_DDL('TRIGGER', u.trigger_name, 'SCHEMA')  

FROM dba_triggers u

WHERE owner = 'SCHEMA'

AND u.trigger_name = 'OBJECT';  

-- DDL for Synonyms

SELECT DBMS_METADATA.GET_DDL('SYNONYM', u.synonym_name, 'SCHEMA')  

FROM dba_synonyms u

WHERE owner = 'SCHEMA'

AND u.synonym_name = 'OBJECT';  

-- DDL for Materialized Views

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', u.mview_name, 'SCHEMA')  

FROM dba_mviews u

WHERE owner = 'SCHEMA'

AND u.mview_name = 'OBJECT';  

-- DDL for Constraints

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', u.constraint_name, 'SCHEMA')  

FROM dba_constraints u

WHERE owner = 'SCHEMA'

AND u.constraint_name = 'OBJECT';  

--Get DDL commands for all objects in database for a Schema --

SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, 'SCHEMA') FROM

dba_tables WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, 'SCHEMA') FROM

dba_indexes WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('VIEW', view_name, 'SCHEMA') FROM

dba_views WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SEQUENCE', sequence_name, 'SCHEMA') FROM

dba_sequences WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PROCEDURE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('FUNCTION', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'FUNCTION'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('PACKAGE', object_name, 'SCHEMA') FROM

dba_procedures WHERE owner = 'SCHEMA' AND object_type = 'PACKAGE'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, 'SCHEMA') FROM

dba_triggers WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('SYNONYM', synonym_name, 'SCHEMA') FROM

dba_synonyms WHERE owner = 'SCHEMA'

UNION ALL

SELECT DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', mview_name, 'SCHEMA')

FROM dba_mviews WHERE owner = 'SCHEMA';

DBMS_STATS.GATHER_STATS for tables schemas indexes columns

 

Action	Full Command	Description
Update statistics for a specific table	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');	Updates statistics for the EMPLOYEES table in the HR schema.
Update statistics for a table and its indexes	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', cascade => TRUE);	Updates statistics for the EMPLOYEES table and all its indexes.
Update statistics for a specific index	EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_SALARY_IDX');	Updates statistics for the EMP_SALARY_IDX index in the HR schema.
Update statistics for all tables in a schema	EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');	Updates statistics for all tables and indexes in the HR schema.
Update statistics for the entire database	EXEC DBMS_STATS.GATHER_DATABASE_STATS;	Updates statistics for all schemas and tables in the database. (Time-consuming on large DBs)
Check last statistics update for a table	SELECT table_name, last_analyzed FROM user_tables WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for the EMPLOYEES table.
Check last statistics update for an index	SELECT index_name, last_analyzed FROM user_indexes WHERE table_name = 'EMPLOYEES';	Shows the last time statistics were gathered for indexes of EMPLOYEES.
Update statistics in parallel for faster execution	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', degree => 4);	Runs statistics gathering in parallel for faster performance (using 4 CPU threads).
Update statistics for specific columns	EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', columns => ('SALARY'));	Updates statistics only for the SALARY column.

---

Best Practices:

✔ Use cascade => TRUE to update both table and indexes.
✔ Use degree => 4 for faster execution on large tables.
✔ Schedule statistics updates during off-peak hours.

Would you like a PL/SQL script to automate this for multiple tables? 😊

Flush shared_pool & buffer_cache

 Manually flush  buffer cache & shared pool cache without bouncing the database:-

For Standalone Databases 

alter system flush buffer_cache;

alter system flush shared_pool;

For RAC Databases 

alter system flush buffer_cache global;

alter system flush shared_pool global;

------- only do in case of system performance issue or maintenance issue ----

Flush shared pool meaning flushing cached execution plan and sql Quries from memory.

Flush buffer cache meaning flushing cached data from memory.

Database restart which internally flush both 

DML TRIGGERS IN ORACLE DB

 -- Table for update trigger 

create table student_update_info (
id number primary key,
old_name varchar2(200),
new_name varchar2(200),
time_stamp varchar2(100),
message varchar2(200)
);

-- Table for insert and delete trigger 

create table student_info (
log_info varchar2(200), 
time_stamp varchar2(100)
)

-- AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER student_insert_trigger
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    -- Code to be executed after insert
    -- For example, logging the insertion along with timestamp
    INSERT INTO student_info (log_info , time_stamp) 
    VALUES ('New row inserted', TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS AM'));
END;
/

-- After update trigger 

CREATE OR REPLACE TRIGGER student_update_trigger
AFTER UPDATE ON student
FOR EACH ROW
BEGIN
    IF :OLD.name <> :NEW.name THEN
        INSERT INTO student_update_info (id, old_name, new_name, update_date)
        VALUES (:OLD.id, :OLD.name, :NEW.name, TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS AM'));
    END IF;
END;
/

-- AFTER DELETE Trigger

CREATE OR REPLACE TRIGGER after_delete_trigger
AFTER DELETE ON student
FOR EACH ROW
BEGIN
    -- Code to be executed after delete
    -- For example, logging the deletion
    INSERT INTO student_info (log_info, time_stamp) VALUES ('Row deleted', TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MI:SS AM'));
END;
/

                                       

Load data from oracle db to csv or excel sheet

 declare
csv_fh  utl_file.file_type;
begin
    csv_fh := utl_file.fopen('/tmp', 'data_export.csv', 'W');
    for r in   ( select id,name from mydb) loop
    utl_file.put_line(csv_fh, r.id||'|'||r.name);
    end loop;
    utl_file.fclose(csv_fh);
end;  

 

CREATE DIRECTORY test_dir AS '/var/opt/oracle/lstest';

-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE
  fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('/tmp', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/

utl_file_dir                         string      /tmp

SQL> DECLARE
    fileHandler UTL_FILE.FILE_TYPE;
BEGIN
  fileHandler := UTL_FILE.FOPEN('/tmp', 'test_file.txt', 'W');
  UTL_FILE.PUTF(fileHandler, 'Writing TO a file\n');
  UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
  WHEN utl_file.invalid_path THEN
     raise_application_error(-20000, 'ERROR: Invalid PATH FOR file.');
END;
/  

PL/SQL procedure successfully completed.

SQL> ls -lrt /tmp

SP2-0734: unknown command beginning "ls -lrt /t..." - rest of line ignored.

SQL> ! cat /tmp/test_file.txt

Writing TO a file

SQL> 

declare
   csv_fh  utl_file.file_type;
begin
    csv_fh := utl_file.fopen('/tmp', 'data_export.csv', 'W');
    for r in   ( select id,name from mydb) loop
    utl_file.put_line(csv_fh, r.id||'|'||r.name);
    end loop;
    utl_file.fclose(csv_fh);
end;  

/

PL/SQL procedure successfully completed.

rm command importance

 2. To delete all files with the exception of filename1 and filename2:

$ rm -v !("filename1"|"filename2") 

**********************************************************

    *(pattern-list) – matches zero or more occurrences of the specified patterns

    ?(pattern-list) – matches zero or one occurrence of the specified patterns

    +(pattern-list) – matches one or more occurrences of the specified patterns

    @(pattern-list) – matches one of the specified patterns

    !(pattern-list) – matches anything except one of the given patterns

*************************************************************

    * – matches zero or more characters

    ? – matches any single character

    [seq] – matches any character in seq

    [!seq] – matches any character not in seq

****************************************************************

3. The example below shows how to remove all files other than all .zip files interactively:

$ rm -i !(*.zip)

=====================================================================

4. Next, you can delete all files in a directory apart from all .zip and .odt files as follows, while displaying what is being done:

$ rm -v !(*.zip|*.odt)

=========================================================================

. Using a pipeline and xargs, you can modify the case above as follows:

$ find . -type f -not -name '*gz' -print0 | xargs -0  -I {} rm -v {}

**********************************************************

diag_alert_500.log

you can do = rm diag_alert_*.log

********************************************************

--------------------------------------### Mostly successful command ###-------------------------------

find /path/to/folder -mtime +90 -exec rm {} +

find /path/to/folder -mtime +90 -delete

find /path/to/folder -mtime +90 -print

Restore Point in Oracle 19c

 RESTORE POINT 

Note:- Before creating GRP make sure below 

1) db should be in archive log mode and flashback must be on

>> beacuse of fra get full if restore point not drop

A restore point is a name assigned to a system change number (SCN) in Oracle.

An SCN is a number that uniquely identifies each new change to an Oracle database; 

this number is incremented whenever users commit a new transaction to the database.

**************************************************************************

 To Enable Flashback Database 

shutdown immediate;

startup nomount;

alter database mount;

archive log list;

alter database archivelog;

change FRA size and then dest :-

alter system set db_flashback_retention_target=2880; ----> 2880 minutes 

alter system set db_recovery_file_dest_size=10G;

alter system set db_recovery_file_dest='/path/to/FRA';

alter system set undo_retention=86400; -----------> 86400 seconds

alter database flashback on;

alter database open;

if not open then do "alter database open resetlogs" or "alter database open noresetlogs" 

SQL >

select name, value from gv$parameter where name in ('db_flashback_retention_target','db_recovery_file_dest_size','db_recovery_file_dest','undo_retention');

To Check Flashback on/off :-

select flashback_on from gv$database;

***********************************************************

SQL> CREATE RESTORE POINT before_upgrade;

Restore point created.

====================================================================================

Guarantee Restore Point:

--------------------------

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

==========================================================================================

List out restore points:

------------------------------

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE

        FROM V$RESTORE_POINT;

========================================================================================

Only Guarantee Restore Points:

--------------------------------------

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,

        GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

        FROM V$RESTORE_POINT

      WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

=========================================================================================

Droping Restore Points:

-------------------------------

SQL> DROP RESTORE POINT before_app_upgrade;

Restore point dropped.

==========================================================================================

5.2.5 Monitoring Space Usage For Guaranteed Restore Points

When guaranteed restore points are defined on your database,

 you should monitor the amount of space used in your 

flash recovery area for files required to meet the guarantee. 

Use the query for viewing guaranteed restore points in

 "Listing Restore Points" and refer to the STORAGE_SIZE 

column to determine the space required for files related to 

each guaranteed restore point. To see the total usage of your

 flash recovery area, use the query provided in 

"Using

 V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE".

======================================================================================

 SQL>  select name,FLASHBACK_ON,open_mode from v$database;

NAME      FLASHBACK_ON     OPEN_MODE

--------- ------------     --------------------------

TECHNO_DB  NO              READ WRITE

============================================================================

 SQL> alter database flashback on;

Database altered.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

------------------

YES

Check for Fast Recovery Area size and location

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------

db_recovery_file_dest                string      +FLASH

db_recovery_file_dest_size           big integer 100G

SQL>

Check for any restore point using v$restore_point view

set lines 200

col TIME for a40

col NAME for a40

select inst_id,NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

no rows selected

Create actual restore point using CREATE RESTORE POINT command

SQL> create restore point RESTORE_POINT_20FEB2019 guarantee flashback database;

Restore point created.

Verify if restore point is created

set lines 200

col TIME for a40

col NAME for a40

select NAME,TIME,GUARANTEE_FLASHBACK_DATABASE,storage_size/1024/1024/1024 from v$restore_point;

    NAME                 TIME           GUA STORAGE_SIZE/1024/1024/1024

-------------         ------------ -------------------------- --- ----------------

RESTORE_POINT_20FEB2019 20-FEB-19 06.20.40.000000000 AM    YES        2

DROP Restore Point

Drop restore point can be performed using DROP RESTORE POINT command and we need turn off flashback_on option

SQL> DROP RESTORE POINT RESTORE_POINT_20FEB2019;

Restore point dropped.

SQL> alter database flashback on;

Database altered.

DAILY SHELL SCRIPTS

 

if count of files > 1lac then use below command to delete them from diag/trace

cd ~trace 

for i in $(ls -lrt | head -n 400000 | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | awk '{print $4}'); do rm -f  $i; done;

touch freethespace.sh

chmod 777 freethespace.sh

vim freethespace.sh

------------------------------------

#!/bin/bash

echo "Enter year:"

read year

echo "Enter month:"

read month

echo "Enter day:"

read day

rm -rf ${year}_${month}_${day}_*

#rm -rf $(date --date='1 days ago' '+%Y_%m_%d')*g

================================================

To check file exists in current directory :

------------------------------------------------

#!/bin/bash

echo "Enter your file to be search:"

read file

if test -f $file 

then

echo "file does exists !"

else

echo "no such file found !"

fi

===============================================

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Oct 18" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}'

redo_551326936_2_657440.arc

redo_551326936_2_657441.arc

redo_551326936_1_758270.arc

redo_551326936_1_758271.arc

redo_551326936_1_758272.arc

redo_551326936_2_657442.arc

redo_551326936_1_758273.arc

redo_551326936_1_758274.arc

redo_551326936_1_758275.arc

redo_551326936_2_657443.arc

redo_551326936_1_758276.arc

redo_551326936_1_758277.arc

redo_551326936_2_657444.arc

redo_551326936_1_758278.arc

redo_551326936_1_758279.arc

ls -lrt | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 22" | awk '{for(j=4;j<=NF;j++) printf("%s ",$j); printf("\n");}' | while read i; 

do rm -rf  $i ; done 

or 

ls -lrt *.arc | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | grep "Dec 23" | awk '{print $4}' | while read i; do rm -rf $i; done 

ls -lrt | head -n 500 | awk '{for(i=6;i<=NF;i++) printf("%s ",$i); printf("\n");}' | awk '{print $4}' | while read n; do rm -rf $n; done