November 26, 2018

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

November 21, 2018

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

    November 20, 2018

    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

    November 01, 2018

    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

    August 10, 2018

    Microsoft Excel Copy row values to another sheet next available row when the value changes

    Hi friends,

    My friend had given me a problem in excel of copy row whenever the values changed to another sheet next available row.

    I have come-up with this following code

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Application.Intersect(Target, Target.ActiveSheet.Range("A1:D1")) Is Nothing Then
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            MsgBox ("Hi")
            Call ValueChange
            Application.EnableEvents = True
        End If
    End Sub
    Sub ValueChange()
            Dim lastrow As Long
            
            lastrow = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1
            Sheets("Sheet2").Range("A" & lastrow & ": D" & lastrow).Value = Sheets("Sheet1").Range("A1:D1").Value

    End Sub

    Hope it will help you.

    Note:
    Please share your valuable comments and feedback to psrdotcom@gmail.com

    April 07, 2018

    Android and Java latest integration changes for AVDManager and SDKManager non-working issues

    Hi all,

    If you haven't used your Android skills in recent times with latest Java then, you might come across a situation where the SDK Manager and AVD Manager are not working.

    Issue

    SDK Manager and AVD Manager are not working.

    Environment

    Java 9 with Android SDK 23

    Note

    Though, I am able to perform development using Android Studio but when it come to update the SDK build tools and others, I was not able to update with SDK Manager.

    Solution


    1. Now, Android SDK Manager have moved to sdk/tools/bin with filename sdkmanager.bat
    2. Edit the sdkmanager.bat file with any text editor
    3. Go to "@rem Execute sdkmanager" command
    4. Update the command with options: -XX:+IgnoreUnrecognizedVMOptions --add-modules java.se.ee 
    5. It should look like below after you add the arguments
      • @rem Execute sdkmanager
        "%JAVA_EXE%" %DEFAULT_JVM_OPTS% -XX:+IgnoreUnrecognizedVMOptions --add-modules java.se.ee %JAVA_OPTS% %SDKMANAGER_OPTS%  -classpath "%CLASSPATH%" com.android.sdklib.tool.sdkmanager.SdkManagerCli %CMD_LINE_ARGS%
    6. If you run android-sdk/tools/bin/sdkmanager.bat, it should be able to run

    Note

    There is nothing like platform-tools folder now in Android SDK.

    So, make sure that you have specified ANDROID_HOME/SDK in your environment variables with your system/pc Android SDK folder

    In PATH environment variable, you can specify %ANDROID_HOME%/tools/bin

    Thanks for reading my blog. Feel free to post your comments below. Contact me on psrdotcom@gmail.com

    Featured Post

    Java Introdcution

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