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

Windows Oracle startup issue

Hi friends,

When your Oracle is unable to connect, do the following procedure

Open command prompt
Connect as sysdba
cmd> sqlplus "/ as sysdba"
sql> startup
Once the command executed successfully, then you should be able to login to Oracle

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

Oracel Database: Delete all user objects (Tables, Views, Procedures, Functions, Sequences, Types, Packages)

Hi friends,

I have come across a situation, where I need to delete all the user objects i.e. Tables, Views, Procedures, Functions, Sequences, Types, Packages.

Please find the link below for the SQL script, which does the job for you.

It's my public GitHubGist URL, where anyone can share the code snippets.

https://gist.github.com/psrdotcom/b61f8b401d4992428ecd8fb8225436c4

Advantage:
You can delete the user after deleting all user objects by logging into SYSTEM user.

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