Mir Sayeed Hassan – Oracle Blog

Oracle DBA – Tips & Techniques | Learn with real-time examples

Resize Redo log files in Oracle database 11gr2

Posted by Mir Sayeed Hassan on December 31st, 2017

Resize Redo log files in Oracle database 11gr2

 Note:

  • We cannot resize the redo log files in Oracle database as we need to drop them & re-create it, Hence its the only way to resize the redo log files
  • Therefore database require at least 2 groups of redo log files regardless the no of...
    Read more..

Posted in DBA Administration | Comments Off on Resize Redo log files in Oracle database 11gr2

Set up your own SID name by connecting Sql*Plus in Oracle database

Posted by Mir Sayeed Hassan on December 31st, 2017

Set up your own SID name by connecting Sql*Plus in Oracle database

Below example will change the Sql*Plus view & display your DB Name (SID) by connecting username instead of word “SQL”

By default in all the Oracle database as shown below:

SQL> show user;
USER is “SYS”

You need...

Read more..

Posted in DBA Administration | Comments Off on Set up your own SID name by connecting Sql*Plus in Oracle database

Monitoring any table operation from your own schema in Oracle database

Posted by Mir Sayeed Hassan on December 31st, 2017

Monitoring any table operation from your own schema in Oracle database

SQL> conn mir/mir123;
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------
TEST1
SQL> conn sys/sys as sysdba
Connected.
SQL> alter table mir.test1 monitoring;
Table... 
Read more..

Posted in DBA Administration | Comments Off on Monitoring any table operation from your own schema in Oracle database

How to release deleted file size without reboot of server

Posted by Mir Sayeed Hassan on December 31st, 2017

How to release deleted file size without reboot of server

Check the size of the disks:

[root@DB ~]# df –h

Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_db-root 22G   16G  5.0G  76% /
tmpfs                  20G  ... 
Read more..

Posted in MISCELLANEOUS | Comments Off on How to release deleted file size without reboot of server

How to delete duplicate rows from the table in oracle database

Posted by Mir Sayeed Hassan on December 25th, 2017

How to delete duplicate rows from the table in oracle database

Now create new table & insert some records init with duplicate:

SQL> create table test_del_dup_rows(eno number(20),name varchar2(20));
Table created.
SQL> insert into test_del_dup_rows values(1,'mir');
1 row created.
Read more..

Posted in DBA Administration | Comments Off on How to delete duplicate rows from the table in oracle database

Change the table with Read only in oracle database by using trigger

Posted by Mir Sayeed Hassan on December 25th, 2017

Change the table with Read only in oracle database by using trigger

In this below scenario, Create a table & insert some data in it before restrict then create a trigger to restrict any insert/update/delete statement on table

SQL> conn mir/mir123;
Connected
SQL> create table test_read_only(eno... 
Read more..

Posted in DBA Administration | Comments Off on Change the table with Read only in oracle database by using trigger