How switch to a different schema as default in Oracle

Often we asked to run a script and we don't want to have the schema name as prefix inside our script.
We can set the schema of the current session to a schema different from our own:

SQL>ALTER SESSION SET CURRENT_SCHEMA = <schema name>;

Oracle Database will use this schema as a schema qualifier when it's omitted.
And also database will use the temporary tablespace of the specified schema for sorts.
But we have to remember if our own user doesn't have access to the new schema objects still we will not have access . It means by altering your session to set to a new schema you'll still need to have right privilege to that schema objects to do any action otherwise it will not elevate your access.

Like:

CONNECT scott/tiger
ALTER SESSION SET CURRENT_SCHEMA = AMIN;

SELECT * FROM emp;

Because emp is not schema qualified, the table name will be resolved under schema Amin.
If Amin doesn't have privilege to select scot.emp table the above select will be failed.


You can do this through system trigger as below and any time Amin connects to the database will have this ability :

CREATE OR REPLACE TRIGGER SYS.AMIN_LOGIN
AFTER LOGON
ON DATABASE
ENABLE
declare
v_user varchar2(30):=user;
sql_stmt1 varchar2(256) :='ALTER SESSION SET current_schema =AMIN';
begin
   if (v_user in () THEN
        execute immediate sql_stmt1;
   end if;
end;

/
Use the query below to get your current_schema info:

SQL>SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from DUAL;

Don’t forget to change back your current schema settings after your operations on other schema.

How to remove an internal job in Oracle?

In order to stop job you need to find job name and description (what).
You can find all columns from dba_jobs view below:

SQL> desc dba_jobs
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 JOB                                       NOT NULL NUMBER
 LOG_USER                                  NOT NULL VARCHAR2(30)
 PRIV_USER                                 NOT NULL VARCHAR2(30)
 SCHEMA_USER                               NOT NULL VARCHAR2(30)
 LAST_DATE                                          DATE
 LAST_SEC                                           VARCHAR2(8)
 THIS_DATE                                          DATE
 THIS_SEC                                           VARCHAR2(8)
 NEXT_DATE                                 NOT NULL DATE
 NEXT_SEC                                           VARCHAR2(8)
 TOTAL_TIME                                         NUMBER
 BROKEN                                             VARCHAR2(1)
 INTERVAL                                  NOT NULL VARCHAR2(200)
 FAILURES                                           NUMBER
 WHAT                                               VARCHAR2(4000)
 NLS_ENV                                            VARCHAR2(4000)
 MISC_ENV                                           RAW(32)
 INSTANCE                                           NUMBER

SQL>
SQL> select job from dba_jobs;
JOB
----------
40
22
65
41
If you need to stop all jobs temporary you can do the following:

SQL> Show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
job_queue_processes                  integer     10

SQL>alter system set job_queue_processes = 0 scope=both;

Reset the parameter to what it was later.


If a job fails to execute after 16 attempts Oracle will mark it as broken and stop running it.
This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view.
If you want to tell Oracle to not run an specific job you can manually mark it as broken by executing the following:

BEGIN
DBMS_JOB.BROKEN(JOB => 22, BROKEN => TRUE);
END;
/

It will be remained broken and will not be run until you either force it to run or mark it to not broken.
When marking it as not broken you must also specify the next date for the job to run.
The following will mark job 22 as not broken and have it execute at 10:00pm on Feb 20.

BEGIN
DBMS_JOB.BROKEN(JOB => 22, BROKEN => FALSE, NEXT_DATE => TO_DATE('22:00 02/20/12', 'HH24:MI MM/DD/YY'));
END;
/
Or 
you jut can start the job based on it's schedule:
EXECUTE DBMS_JOB.BROKEN(JOB => 22, BROKEN => FALSE);

commit;

Or

You asked to kill whole session:

First, find the session number and sid id from v$session then:

alter system kill session 'sid, serial#' 


To remove a job from Oracle job queue you  need the job number.
If you’re not sure what to do, you should be able to find it by querying from dba_jobs view.

When you have the job number, run the following command:

BEGIN
DBMS_JOB.REMOVE(JOB => 22);
END;
/

Or
To completely remove a job:

SQL> execute dbms_job.remove(22); 

If you asked to kill a session in windows you can find the  tread first and then kill by orakill command:

First find the sid and thread you want to kill by using the following  query:

SELECT sid, spid as thread, osuser, s.program

FROM sys.v_$process p, sys.v_$session s 

WHERE p.addr = s.paddr

Then run the following for Oracle on Windows :

C:\>orakill sid thread

For example:
C:\>orakill ORCL 4760
Kill of thread id 4760 in instance ORCL successfully signalled.

Attention: the sid here is the name  given to database.

As we see above, you might ask why Oracle provide orakill utility when you can kill a session by alter system kill.

1) The alter system will not clear any lock and the session will be remained until

it times out. But orakill kill the session instantly.

2)If a DBA can't have access to Sql Plus because all resource is in used then the session can be killed without longing to database. 

Unlike the Unix, windows is thread-based which means for each instance all background processes and sessions has oracle.exe process, then you need to find the thread number to kill specific session. On the other hand orakill serves the action "kill -9 " does in Unix.

Related link:

http://www.oracleutilities.com/OSUtil/orakill.html

How do switch to another user or reset that user password temporary in Oracle

As DBA, there are situations you need to upgrade an Oracle application or schema by log-in as that user but you might don't know the password. The best way to get around this is to change that user password temporary and change it back to that password again after you finish your task.
The problem is always that encrypted password  cannot be de-crypted so that password can be reset to its original one easy.We have to use VALUES keyboard which is same as what import or data pump does when it creates users.
For instance, if you as DBA you don't know SCOTT password, first you can save the password and do your task and then change it back to what it was.
You can follow the steps below to accomplish this:


Sqlplus system/password

In 10g and before:

SQL>select password from dba_users where username = 'SCOTT';

PASSWORD

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

F894844C34402B67

For 11g and above:

SQL>select password from sys.user$ where name='SCOTT';

PASSWORD

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

F894844C34402B67

SQL>alter user scott identified by temporary;

Now you can connect to scott with new password and do the job you need and then change the password back to what it was after you done as below:

SQL>alter user SCOTT identified by VALUES 'F894844C34402B67';

How to change dynamic port of SQL Server instance to static ?

One of the requirements for named instance of SQL Server compare to default instance is using static port.
In default instance of SQL Server there is a listener, listing to port 1433. When we create named instance the port is randomly dynamic as default behaviour of instance when it's not configured.In default instance, connection goes to TCP port 1433 and in named instance, connection goes through service browser and it goes to UDP port 1434 and then SQL browser responds by port number.The default instance of SQL Server regularly just support static port but named instance can support dynamic and also static. For named instance you might need to change dynamic port to static. For example, in SharePoint secure farm environment, the recommendation is to block UDP port and use named instance with a port different from 1433.
In order to change SQL Server port you can go to the SQL Server Configuration Manager:
Start=>Program Files=> Microsoft sql server =>Configuration Tools=> SQL server configuration manager

In Configuration Manager,on the left panel side, expand Sql Server Network configuration.Click on protocol for instance name then in right hand panel, right click and go to properties on the TCP/IP.

As you see below, click on IP Address tab then scroll down in IPALL section blank  for TCP Dynamic Port row and your desired port in TCP Port row.

After this change you have to restart SQL Server service to make this change happens.

Note1:Remember if you doing this change in SharePoint instance you have to create alias for each application server.
SQL Server client aliases are necessary on all computers that communicate with the computer running SQL Server
Note 2:For changing port in clustered named instance do following:
Bring the SQL Server service offline from cluster administrator.
Disable the checkpointing to the quorum.
Change the Dynamic port of the sql server to static in all the nodes .
Enable the checkpointing to the quorum.
Bring SQL Server online.

How to find which port SQL Server instance currently listening?

There are different ways to find the current port that SQL Server instance using (compare to default port:1433) and I listed some of them in below:
1)Go to SQl Server error log and find thr entry for instance:
SQL server listening on 127.0.0.1: 1433.
2)Open the Sql Server Network Utility(in 2000) or SQL configuration manager(2005 and higher) and click on properties for TCP/IP in instance
3)Issue netstat -an from an MS-DOS command window produces the following results showing three established connections for SQL Server
This example uses 157.54.178.40 as the IP address of SQL Server and 157.54.178.30 as the client IP address. The ports opened by the client are 1749, 1750, and 1751 respectively.

Proto   Local Address          Foreign Address                State
 TCP    157.54.178.40:1433     0.0.0.0:0                       LISTENING
 TCP    157.54.178.40:1433     157.54.178.30:1749     ESTABLISHED
 TCP    157.54.178.40:1433     157.54.178.30:1750     ESTABLISHED
 TCP    157.54.178.40:1433     157.54.178.30:1751     ESTABLISHED
4) Check Registry entry: HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

and you will see TCP Port as one of the entries and it’s value represents the port number for that instance.
In case you are using a named instance, then the registry entry will be
: HKLM\Software\Microsoft\Microsoft SQL Server\
<name of the instance>\MSSQLServer\SuperSocketNetLib\TCP

About me

Senior Consultant with a wide range of experience in architecture,database, data warehouse ,application integration,Ecommers and ERP. particularly interested in area of migration to Cloud, Cloud Automation, SAP Architect,Business Intelligence,data architect and application deployment & solution.
Expert in migration specifically migration to cloud , as well as close interaction with DBMS and ERP manufacturer,telecommunication , and Oil & Gas.
-I have been working as IT consultant, Architect , and Advisor for different companies.In my current role, part of my job for our clients is providing solution for business ,implement the latest IT technology , resolve performance & tuning issue ,and support migration to cloud .
-Expert in setting up data warehouse and ETL solutions.
-Extensive experience on cloud technology like SAP Cloud, Amazon AWS,Microsoft Azure, and Google Cloud .
Specialties: Recent deploy, implement,support, and upgrade : SAP Hybris, SAP BPC, Remedy, Primavera, Meridium, Altiris , SAP ERP, Prism Vanguard, Visions ,Smart Plant, Intools,Traccess,Surfcontrol ,PAS,Citrix,BizTalk,Livelink,SharePoint,Maximo,RightAngle,SMO,JDE,Oracle RAC,Oracle Data Guard ,Golden Gate ,Informatica,SAP HANA, Puppet, Dell Boomi, Docker and kubernetes,Vm vSphire,Dynatrace,Opsview HANA,Azure and AWS.

I have my own company "Fast IT Consulting LTD." and provide solution for different projects in North America.

Thanks for visiting this blog!

Amin