How to Modify / Set SQLPlus Prompt With Connected Username and SID?

It is always better have the username and the database SID to be set in the sqlplus prompt to avoid confusions. It will be very useful to identify the database where you connected especially your work involved in multiple databases.

        1.   Setting the prompt temporarily with the current connected session.

set sqlprompt "_CONNECT_IDENTIFIER'-'_USER> "

esbtst-SYS>

How to Avoid / Stop /Restrict the Dropping of User Schema by Mistake from Oracle Database?

Suppose multiple DBAs are working on simultaneously in different databases, there are chances of dropping the user or schema by mistake. Eg: Wrongly dropping the user schemas in similarity of the names etc. Suppose if the schema is having objects it will throw the error if you are not using the cascade option. If you use cascade option with drop command it will delete the user without asking for any kind of confirmation. It can be avoided by using the following trigger which can be created under sys/system schema.

How to Exclude MLOG$ Materialized View Log Tables from the Data Pump IMPDP

Generally if you use exclude parameter with materialized view, materialized view log, it will not exclude the mlog$ tables from the import. You have to useTABLE also with the exclude parameter. Please see an example here.

Example

I am going to create a materialized view and a log for the same.

SQL> create materialized view log on dept;

Oracle dbms_utility.get_dependency to Get Dependent Objects or Dependencies of a Table

It is a common requirement for a DBA or developer to find out the dependent objects or dependencies for a table. You can use dbms_utility .get_dependencypackage for this purpose.

Syntax:

exec dbms_utility.get_dependency  ('TABLE', 'TABLEOWNER', 'TABLENAME');

See some examples here.

SQL> SET SERVEROUTPUT ON

SQL> CREATE SYNONYM SYN_EMP FOR EMP;

Synonym created.

How to Kill or Resume [1]+ Stopped Unix Jobs

When you press ctrl-z on actively running job or command it will suspend the job and release the prompt. But the job will be stopped and remain idle in the back ground. If you try to logout the window or session you will get a message saying “There are stopped jobs.” You can stop or resume the jobs using following commands.

jobs –p command will give the process id for the suspended job.

jobs –l command will list the details of the suspended job

See examples below.

How to Add or Insert Current Log Time and Date to a Notepad File Or Whenever You Open the file by Default

Suppose if you have habit of logging all tasks to be performed or already performed with time and date, then you can use windows notepad easily for this purpose. This trick works with all major versions of the Windows.

There are two options

1. Insert time stamp whenever and wherever you needed.

Open the notepad file and move the cursor to the location where you need the timestamp and just press Function Key 5(F5).

Oracle 12c Data Pump EXPDP IMPDP Logtime Option Parameter

One new parameter introduced in Oracle 12c data pump which is called logtime. This is a command-line parameter which will display the messages or status in log with time stamped format. This parameter helps the DBA to identify the time taken for each granule of export job. There are 4 possible options available for logtime parameter.