Oracle Create User with custom tablespace and datafile

Hi all,

Through I have explained the procedure to delete/drop the user with tablespace and datafiles. I though of giving information about creating a user with custom tablespace and datafile.

Pre-requisites

1. Oracle database
2. Login as sys as sysdba

Procedure

Create tablespace

Syntax: CREATE TABLESPACE DATAFILE SIZE ;
Example: CREATE TABLESPACE sample_tablespace DATAFILE 'C:\\samplets.dbf' SIZE 100M;
Example: CREATE TABLESPACE sample_tablespace DATAFILE '\usr\local\datafiles\samplets.dbf' SIZE 100M;

User creation

Alter session
ALTER SESSION SET "_ORACLE_SCRIPT"=true;

Create User
Syntax: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE ;
Example: CREATE USER sampleuser IDENTIFIED BY samplepwd DEFAULT TABLESPACE sample_tablespace;

Grant privilieges
Syntax: GRANT ALL PRIVILEGES to ;
Example: GRANT ALL PRIVILEGES to sampleuser;

Commit the commands
commit;

Now, you should be able to create the tablespace and made that as default tablespace for the newly created user.

Hope, this information helps you.

Please send your comments and feedback to psrdotcom@gmail.com

Oracle Drop/Delete User with All tables and files

Hi All,

Today I am going to explain the procedure to delete/drop a user in Oracle database along with tables and mapped files for that user tablespace.

Pre-requisites

Oracle database with the following

1. User
2. Tablespace of the user
3. Datafile of the tablespace

Procedure

Drop the user

Syntax: DROP USER CASCADE;
Example: DROP USER sampleuser CASCADE;

Make the tablespace offline

Syntax: ALTER TABLESPACE OFFLINE;
Example: ALTER TABLESPACE sampletablespace OFFLINE;

Drop the tablespace with mapped datafile[s]

Syntax: DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES
Example: DROP TABLESPACE sampletablespace INCLUDING CONTENTS AND DATAFILES

With the above 3 commands, you can make sure that, user and user related data has been completed removed from database.

Hope, it will help to resolve your issue.

Please send your feedback and comments to psrdotcom@gmail.com

Connect to remote Oracle database server from RedHat Linux 7 using Oracle InstaClient

Hi friends,

I have come across a situation, where I need to connect to remote Oracle database server from RedHat Enterprise Linux (RHEL) 7. I found a way of using the Oracle InstaClient instead of installing the full Oracle DB which internally consists of Client.

Pre-requesites

1. Linux mahcine (I tried with RHEL 7) with following softwares
• Java (JAVA_HOME should be available)
• Development Tools (RHEL 7 tools like gcc etc.)
2. Another machine where Oracle server is installed with the following config
• Database schema should be available
• Users should be available instead of system/sysdba users
3. Both machines shall be reachable to each other via ping.

Note: You should know the database server SID/service name.

Procedure

Download

1. Download the insta client from Oracle Official Page
2. In my case, I have downloaded the "Instant Client for Linux x86-64"
3. Accept the license agreement
4. Click "oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm" to download
5. Optionally, you can download the "oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm" to have sqlplus command line utility for testing
6. Alternatively, you can download the zip files and download the same to install.
7. In my case, I am going with RPMs.

Install

• Install the downloaded RPMs
• #> yum install oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64.rpm
• #> yum install oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64.rpm
• While installing, it asks for locations, accept the defaults by pressing the "Enter" button

Note: If any dependencies are there, please install those.

Environment Variables

• Create a oracle_env.sh file and setup your variables
• #> gedit /etc/profile.d/oracle_env.sh

export ORACLE_HOME=/usr/lib/oracle//client/
export TNS_ADMIN=/usr/lib/oracle//client/
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

• Save your file
• Source your file with following command
• #> source /etc/profile.d/oracle_env.sh

tnsnames.ora

• Create tnsnames.ora file in your $ORACLE_HOME with following syntax

localsid = (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = ))
    (CONNECT_DATA =
      (SID = )
    )
  )

• Instead of SID, you can use SERVICE_NAME also.

Connect

• You can test the connection
• #> sqlplus @

You should be able to connect to your remote database.

Hope, it is useful for you.

Please send your comments and feedback to psrdotcom@gmail.com

Completely uninstall openjdk in RedHat Enterprise Linux (RHEL)

Hi all,

Today, I'll explain how to uninstall completely the openjdk from the RedHat Enterprise Linux (RHEL) machine.

Some of the softwares requires only the Oracle Java and having the openjdk, sometimes causes an issue. So, some users wants to remove the openjdk from the linux machine.

Pre-requisites

• openjdk installed on the machine
• Check for java alternatives, when multiple java versions installed
• #> /usr/sbin/alternatives --config java
• Result with + is the currently selected java for the machine

Procedure

1. Find out the installed openjdk versions

#> rpm -qa | grep java
#> rpm -qa | grep jdk

Sample Output:

java-1.8.0-openjdk-1.8.0.191.b12-1.el7_6.x86_64

java-1.8.0-openjdk-headless-1.8.0.191.b12-1.el7_6.x86_64

2. Remove the all openjdk versions

#> yum remove <>

Example:
#> yum remove java-1.8.0-openjdk-1.8.0.191.b12-1.el7_6.x86_64

#> yum remove java-1.8.0-openjdk-headless-1.8.0.191.b12-1.el7_6.x86_64

Hope, it is useful for you to configure java on your linux machine.

Please send your feedback and comments to psrdotcom@gmail.com

Microsoft Visual Studio Code with Debugger for Chrome for Static Website Pages

Hi all,

Have you used the Microsoft's free VS Code editor for you web development. I think, you should try it.

Today I'll discuss about how to debug from the VS Code without placing breakpoints on Chrome browser.

Pre-requisites

1. VS Code should be installed
2. Debugger for Chrome extension in VS Code

Configuring the debugger

• Once you open your website in the VS Code
• In the activity bar, you will find the debugger icon OR you can use the keyboard shortcut Ctrl+Shift+D
• Now, we will "Add Configuration" to our project
• On top of the debug sidebar, you will find an option "Add Configuration"
• Click on "Add Configuration"
• It will prompt you to select out of box provided options like "Chrome, NodeJS"
• Choose Chrome
• Now, launch.json file will be created
• Configure the launch.json file by specifying URL with port number
• Since, our project is of static webpages type, instead of webRoot, we will use "file"

Here is the sample launch.json file

{

// Use IntelliSense to learn about possible attributes.

// Hover to view descriptions of existing attributes.

// For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387

"version": "0.2.0",

"configurations": [

{

"type": "chrome",

"request": "launch",

"name": "Launch Chrome for my website",

"url": "http://localhost:3001",

"file": "${workspaceFolder}/index.html"

}

]

}

Debug

• Click on the "Start Debugging" button OR press F5
• A new chrome window with debugging enabled will be launched
• Place your breakpoints in VS Code, just by clicking left to the line number in the editor
• Perform the action and your debug will be hit
• Note: You can add conditional debugging by editing the break point

Happy debugging.

Please send your review and comments to psrdotcom@gmail.com