How to create a user and grant permissions in Oracle

As always, begin by connecting to your server where Oracle is hosted, then connect to Oracle itself as the SYSTEM account. The SYSTEM account is one of a handful of predefined administrative accounts generated automatically when Oracle is installed. SYSTEM is capable of most administrative tasks, but the task we’re particularly interested in is account management. Creating a user Once connected as SYSTEM, simply issue the CREATE USER command to generate a new account. Here we’re simply creating a books_admin account that is IDENTIFIED or authenticated by the specified password. The Grant statement…

Read More

Oracle PL/SQL – SQL Plus Startup Script with glogin.sql or login.sql

This article shows about how to configure the environment variable when SqlPlus – SqlPlus Command start up in order to have always a good behavior in the formatting of the result. You have to setup the Windows – Environment Variable (SQL Plus|SQL Developer)- SQLPATH with a directory. Copy then the file login.sql described below in it. This file is execute each time that you connect to a database with SQLPlus. SQLPATH in Windows environment (Windows Oracle server):  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\ SQLPATH in…

Read More

Oracle – Create Table – Using Select Statement With Data or No Data

Here is a sample script: WITH data   CREATE TABLE test3 AS SELECT table_name, tablespace_name FROM all_tables;   ———————————   Without data   CREATE TABLE ctas AS SELECT table_name, tablespace_name FROM all_tables WHERE 1=2;     — For example, create a table named EMPLOYEE3 that includes all — of the column definitions from EMPLOYEE where the DEPTNO = D11.   CREATE TABLE EMPLOYEE3 AS (SELECT PROJNO, PROJNAME, DEPTNO FROM EMPLOYEE WHERE DEPTNO = ‘D11’) WITH NO DATA —

Read More

Posted in Oracle Tagged , Comments Off on Oracle – Create Table – Using Select Statement With Data or No Data
Oracle: Using the AFTER INSERT and AFTER UPDATE triggers

A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has occurred and an ‘AFTER UPDATE’ trigger after an update event has occurred. Let’s see an example for ‘AFTER INSERT’ trigger. Syntax: CREATE or REPLACE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW DECLARE variable declarations BEGIN trigger statement END; First problem: We want to insert a record…

Read More

Posted in Oracle Tagged , Comments Off on Oracle: Using the AFTER INSERT and AFTER UPDATE triggers