historical information of Oracle Instance startup time

SQL> SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC;
STARTUP_TIME
---------------------------------------------------------------------------
12-JUL-21 09.10.09.000 AM
16-APR-21 07.53.43.000 AM
16-APR-21 07.53.43.000 AM
12-JAN-20 12.36.43.000 AM
12-JAN-20 12.30.40.000 AM
12-JAN-20 12.28.10.000 AM
12-JAN-20 12.23.46.000 AM
12-JAN-20 12.13.55.000 AM

Move SQL Profiles from One Database to Another

SQL Profile is a collection of information stored in the data dictionary that enables the query optimizer to create an optimal execution plan for a SQL statement.The SQL profile contains corrections for poor optimizer estimates discovered during Automatic SQL Tuning. This information can improve optimizer cardinality and selectivity estimates, which in turn leads the optimizer to select better plans..

Steps to copy profile from one DB to Another:

1.Creating a staging table to store the SQL Profiles

EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF(table_name=>'SQLPROFILE_STAGE_TABLE',schema_name=>'SCOTT');

PL/SQL procedure successfully completed.

2.Pack the SQL Profiles into the Staging Table

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (profile_category => '%',staging_table_name =>'SQLPROFILE_STAGE_TABLE',staging_schema_owner=>'SCOTT');

PL/SQL procedure successfully completed.

3. Take backup of staging table and copy dump to target db host

 expdp username/password dumpfile=EXPDP_SqlProfiles.dmp logfile=EXPDP_SqlProfiles.log DIRECTORY=ORACLE_BASE

4.Import staging table on target db 

 impdp username/password dumpfile=EXPDP_SqlProfiles.dmp logfile=IMPDP_SqlProfiles.log DIRECTORY=ORACLE_BASE

5.Unpack Staging Table

If importing to the same schema, schema owner does not need to be specified:

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'SQLPROFILE_STAGE_TABLE');

However, if importing to different schema, the staging schema owner needs to be changed:|

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'SQLPROFILE_STAGE_TABLE',staging_schema_owner => 'SCOTT');

PL/SQL procedure successfully completed.

6.Validate profiles on target side

select * from from DBA_SQL_PROFILES;

 

Find Rollback transaction in oracle

Rollback transaction Status:

select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;

Find rollback session details:

select s.sid, s.username, t.xidusn, t.used_urec, t.used_ublk
from gv$session s, gv$transaction t
where s.saddr=t.ses_addr
order by 2;

Delete the last 10 files in a directory in Linux

To delete the last 10 files in a directory using a command in Linux, you can use a combination of ls, tail, xargs, and rm. 

ls -ltr | tail -n 10 | awk '{print $9}' | xargs rm

Breakdown of the Command:
 ls -ltr: Lists files in long format (-l), sorted by modification time, with the newest files last (-t), and in reverse order (-r).
 tail -n 10: Selects the last 10 lines from the ls output. Note that tail -n 9 would give you only 9 files; -n 10 ensures you are selecting the last 10 files.
 awk '{print $9}': Extracts the filenames from the ls output. This assumes filenames are in the 9th column. This might need adjustments based on the actual output format, especially if filenames have spaces.
 xargs rm: Passes the extracted filenames to rm for deletion.