Loading...

Follow DBA Genesis on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Access the free SQL Developer video course here

It's always a great idea to store your SQL worksheet in a particular location. SQL developer also allows you to store your queries in the form of reports inside SQL developer itself. This makes your life easy because all your queries are stored inside the SQL developer and you can execute them as and when you want.

In order to save a query as a report first we need to execute the query in the SQL worksheet area

Right click on the query and choose create report

Give a name to your report and click on apply

Your report has been created successfully and you can find the user defined reports under the reports section

A report is nothing but a saved SQL Query. Double click the report name and SQL developer will ask you the connection name against which the query must Run

Once you click on the ok but me it will directly give you the output of the Stored query inside the report


Access the free SQL Developer video course here

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Access the free SQL Developer video course here

In this blog we will understand how to export data from a Table into Excel csv format. In real time most of the applications accept the data in a csv format. The csv format is nothing but a comma separated value file. This means that all the values inside the excel file or the csv file will be separated by a comma.

Click on any table name, then choose the data tab and then click on the action button.

Now select the export option

In the new dialogue box that opens up you will have to provide the file format and the file location.

Once the export is completed if you open the export file using Notepad then you must see all the column values separated by comma because this is a csv file.

Access the free SQL Developer video course here

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Access the free SQL Developer video course here

In this blog post we will understand how to create a new table inside SQL developer using the GUI interface.

Right click on the table name and you can see that you will have an option of new table. Click on the option.

This will open up a new dialogue box that allows you to create a new table. Enter the table name, click on the plus icon to add new columns and change the column data type as per your requirement.

You can also click on the DDL tab to see the exact SQL statement that will be executed against the database.

Once you click on the ok but the table will be created inside the database. you can expand the table section under the connections to view the table.

As you click on the students table name it will open up a new tab with the table details. Click on the data tab and in this tab you can actually add records inside the new table.

Once you have added all the records inside the table click on the commit button to commit the data insert.

Access the free SQL Developer video course here

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Access the free SQL Developer video course here

In this blog post we will be understanding about how to navigate the tables inside the schema that we have connected to.

In the below image you can see that I have two connections inside the SQL developer

If you double click the connection name it will try to connect to the database with the connection details that we have provided earlier. You can see when you double click the connection name it will take some time to connect to the database.

Whenever you are connected to the database you will see that a new SQL worksheet will open.

Inside the connections tab you can see we have so many objects like tables, views, procedures, packages, triggers, functions and many more.

Let us go ahead and navigate the tables inside the HR schema. If you expand the tables section you will see all the tables that are owned by the HR schema.

If you click on any of the table names it will open a new tab and you can see all the table details in the new tab

In the table data you will see that there are more tabs where you find more details about the table. By default we will be other columns tab.

Lat us click on the data tab and see the data inside the table.

Sorting of data inside SQL developer is very simple. You can double click any column name and it will start sorting your data.

The next most important tab that you'll be using is constraints. This tab will list all the table level constraints.

You can click on the edit button in order to edit the constraints

This will open up a new dialogue box where you will be able to create, edit or modify the table level constraints.

The above method is one way of navigating a table. Let us discuss about another way of navigating the tables. If you click on the + sign beside the table name it will show you all the columns inside the table.

Now you might have this doubt like how do we access the table using SQL queries. You will have to use the SQL worksheet area in order to write your SQL queries. once you write the SQL Query, highlight the SQL query and click on the green Run button. You will see the query output as query result below the SQL worksheet.

Note: make sure to highlight the only query you would like to execute in the SQL worksheet.

The next important thing that you must know is how to filter the table column. If you take your mouse cursor on a column name it will show a small filter icon. Click on the filter icon and it will open up a new small window where you can write your filter condition.

You can choose the filter value from the dropdown or you can also type your own value in the textbox.

If you want to remove filter, click on the filter icon and choose the Remove option.

Access the free SQL Developer video course here

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Access the free SQL Developer video course here

Once you have downloaded the Oracle SQL Developer application, its a good idea to create a shortcut on the desktop for easy access to the application. just right click, select send to and create the shortcut on your desktop.

In this lesson you will be learning about how do you connect to a server using SQL Developer. When I say server it can be physical linux server, remote linux server, Linux server that is hosted on cloud or any server in the world.

If I have a database on my system, you can connect to my system database using SQL Developer because all it needs is a network. If you can ping my system IP from your system, you can technically connect to the database.

First of all remove this doubt from your mind that SQL Developer is used to connect to a database that is hosted on a physical box. Not at all true because you will see in this lesson that I will be connecting to a database which is hosted on Amazon RDS!

You can use SQL Developer to connect to database which is technically hosted anywhere in the world!

Access the free SQL Developer video course here

Details You Need to Connect SQL Developer Database

Even before we start connecting to the RDS database that I have, let us talk about some details that you would need.

  1. Connection Name: you need to have a connection name. It's a name given by you to easily identify username and database connected to. This is for your understanding and connection identification. Example connection name: sys@prod, scott@devdb, hr@testdb etc.
  2. Username: You would need the database username or the schema name that you want to connect like sys, scott or HR.
  3. Password: Password for the database user / schema you would like to connect.
  4. Hostname: Server hostname or IP address where the database is hosted.
  5. Port No: We need to have the port number where your listener is running.
  6. Database SID: Finally we need to have the database SID where we are going to connect or which database we are going to connect.

Note: For the demo purpose, I have connected to Amazon RDS Oracle database. In case you want to practice SQL Developer on Amazon, you can purchase cloud lab through our website here (For this eBook, I am using Oracle SQL Cloud lab): https://dbagenesis.com/p/cloud-labs/


How to create a new database connection?

  1. Under the connections box, click the green + symbol

  1. Enter all the connection details as discussed in the previous section into the dialogue box. Click on test and you must Status: Success

  1. Click on Connect to save the new connection into the sql developer. You new saved connection will appear under the connections box

Once you click on the connection name, there will be a new SQL worksheet opens. This worksheet is the area where you write SQL queries and execute it.

Access the free SQL Developer video course here


Benefits of connection naming conventions

If you look at above new connection name, it says aksgolu@TESTDB. I always use this naming convention because its very easy to recognize the username and database connected. I always recommend to use below naming convention:

<username>@<SID>

Examples:

  • hr@PRODDB: Connected as HR user to PRODDB database
  • scott@DEVDB: Connected as Scott user to DEVDB database
  • sys@TESTDB: Connected as sys user to TESTDB

Makes life easy!

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Access the free SQL Developer video course here

In this blog post we'll be understanding about how to download the Oracle SQL Developer. We will be downloading SQL Developer 18.2 for windows. The process is more or less same for Linux and Mac OS.

SQL Developer System Requirements

This section describes the recommended minimum values for CPU, memory, display, disk storage, and other resources on the supported systems.

Recommended for Windows System

  • Operating System - Minimum windows XP and above
  • CPU (Processor) Type - Pentium IV 2 GHz MHz or faster
  • Memory - 1 GB RAM or above
  • Hard Disk Space - 110 MB

Recommended for Linux Systems

  • Operating System - Red Hat 3 and above, Fedora 4 and above
  • CPU (Processor) Type - Pentium IV 2 GHz MHz or faster
  • Memory - 1 GB RAM or above
  • Hard Disk Space - 110 MB

Recommended for MAC OS X Systems

  • Operating System - Apple Mac OS X Version 10.4.x
  • CPU (Processor) Type - Dual 1.25 GHz G4/G5 (1 GHz G4 minimum)
  • Memory - 1.5 GB RAM or above
  • Hard Disk Space - 110 MB

Access the free SQL Developer video course here

Steps to Download Oracle SQL Developer 18.2

First of all go to google and type download SQL Developer. The first result must be from oracle.com


Click on the first result and this will take you to oracle.com website. Make sure you have an account to download SQL Developer. Accept the License Agreement


Now you can proceed to download Oracle SQL Developer. The latest version of SQL Developer that is available is 18.2 and make sure you are downloading the first one that is windows 64-bit with JDK nine included.


Note: the previous versions of SQL Developer required you to download the JDK separately, install it and then you link the JDK with SQL Developer. But now what Oracle does is, it is giving you JDK included inside the installation files of the SQL developer so you don't need to download JDK 9/8/7 version separately you just download one file, install it and done. So you are not performing those extra steps of downloading JDK separately and then integrating both the products.

Simply click on the Download button and the download should start. Once download is completed, you must get a zip file


Right click and extract the zip file to a folder. You need to unzip utility to perform this action


Open the unzipped folder, you must have sqldeveloper folder. Double click it again and then you must see SQL Developer application.


Double click on sqldeveloper application and this should start the oracle SQL developer version 18.2


So if you are someone who tried installing SQL Developer in the past, you might remember that there was an additional step of downloading JDK. Separately installing the JDK first, then integrating JDK into SQL Developer and then only your installation would complete.

In the latest Oracle SQL Developer 18.2 version, we just downloaded the SQL Developer file extracted it and started the SQL Developer. So we are not bothered about the JDK, it is already integrated and it comes with your installation files.

This is how the SQL Developer interface looks like:


In this blog post we have downloaded the SQL Developer for Windows machine. We extracted it and install the SQL developer perfectly in the machine.

Access the free SQL Developer video course here

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

I was working on a database connected via SQL developer and could not find HR schema. I wanted to create the HR schema using SQL queries. I used below scripts one by one and create the schema with the SYS user:

STEP 1: SCRIPT TO CREATE THE HR USER

Execute below script using sys user

SET ECHO OFF
SET VERIFY OFF
PROMPT 
PROMPT specify password for HR as parameter 1:
DEFINE pass     = &1
PROMPT 
PROMPT specify default tablespeace for HR as parameter 2:
DEFINE tbs      = &2
PROMPT 
PROMPT specify temporary tablespace for HR as parameter 3:
DEFINE ttbs     = &3
PROMPT 
PROMPT specify password for SYS as parameter 4:
DEFINE pass_sys = &4
PROMPT 
PROMPT specify log path as parameter 5:
DEFINE log_path = &5
PROMPT
PROMPT specify connect string as parameter 6:
DEFINE connect_string     = &6
PROMPT
-- The first dot in the spool command below is 
-- the SQL*Plus concatenation character
DEFINE spool_file = &log_path.hr_main.log
SPOOL &spool_file
REM =======================================================
REM cleanup section
REM =======================================================
DROP USER hr CASCADE;
REM =======================================================
REM create user
REM three separate commands, so the create user command 
REM will succeed regardless of the existence of the 
REM DEMO and TEMP tablespaces 
REM =======================================================
CREATE USER hr IDENTIFIED BY &pass;
ALTER USER hr DEFAULT TABLESPACE &tbs
              QUOTA UNLIMITED ON &tbs;
ALTER USER hr TEMPORARY TABLESPACE &ttbs;
GRANT CREATE SESSION, CREATE VIEW, ALTER SESSION, CREATE SEQUENCE TO hr;
GRANT CREATE SYNONYM, CREATE DATABASE LINK, RESOURCE , UNLIMITED TABLESPACE TO hr;
GRANT execute ON sys.dbms_stats TO hr;


STEP 2: CREATE HR SCHEMA OBJECTS

Connect as hr user and execute below script to create HR schema tables

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
SET ECHO OFF 
REM ********************************************************************
REM Create the REGIONS table to hold region information for locations
REM HR.LOCATIONS table has a foreign key to this table.
Prompt ******  Creating REGIONS table ....
CREATE TABLE regions
    ( region_id      NUMBER 
       CONSTRAINT  region_id_nn NOT NULL 
    , region_name    VARCHAR2(25) 
    );
CREATE UNIQUE INDEX reg_id_pk
ON regions (region_id);
ALTER TABLE regions
ADD ( CONSTRAINT reg_id_pk
                PRIMARY KEY (region_id)
    ) ;
REM ********************************************************************
REM Create the COUNTRIES table to hold country information for customers
REM and company locations. 
REM OE.CUSTOMERS table and HR.LOCATIONS have a foreign key to this table.
Prompt ******  Creating COUNTRIES table ....
CREATE TABLE countries 
    ( country_id      CHAR(2) 
       CONSTRAINT  country_id_nn NOT NULL 
    , country_name    VARCHAR2(40) 
    , region_id       NUMBER 
    , CONSTRAINT     country_c_id_pk 
                 PRIMARY KEY (country_id) 
    ) 
    ORGANIZATION INDEX; 
ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
             FOREIGN KEY (region_id)
                REFERENCES regions(region_id) 
    ) ;
REM ********************************************************************
REM Create the LOCATIONS table to hold address information for company departments.
REM HR.DEPARTMENTS has a foreign key to this table.
Prompt ******  Creating LOCATIONS table ....
CREATE TABLE locations
    ( location_id    NUMBER(4)
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city       VARCHAR2(30)
    CONSTRAINT     loc_city_nn  NOT NULL
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    ) ;
CREATE UNIQUE INDEX loc_id_pk
ON locations (location_id) ;
ALTER TABLE locations
ADD ( CONSTRAINT loc_id_pk
                PRIMARY KEY (location_id)
    , CONSTRAINT loc_c_id_fk
                FOREIGN KEY (country_id)
              REFERENCES countries(country_id) 
    ) ;
Rem     Useful for any subsequent addition of rows to locations table
Rem     Starts with 3300
CREATE SEQUENCE locations_seq
 START WITH     3300
 INCREMENT BY   100
 MAXVALUE       9900
 NOCACHE
 NOCYCLE;
REM ********************************************************************
REM Create the DEPARTMENTS table to hold company department information.
REM HR.EMPLOYEES and HR.JOB_HISTORY have a foreign key to this table.
Prompt ******  Creating DEPARTMENTS table ....
CREATE TABLE departments
    ( department_id    NUMBER(4)
    , department_name  VARCHAR2(30)
    CONSTRAINT  dept_name_nn  NOT NULL
    , manager_id       NUMBER(6)
    , location_id      NUMBER(4)
    ) ;
CREATE UNIQUE INDEX dept_id_pk
ON departments (department_id) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
                PRIMARY KEY (department_id)
    , CONSTRAINT dept_loc_fk
                FOREIGN KEY (location_id)
              REFERENCES locations (location_id)
     ) ;
Rem     Useful for any subsequent addition of rows to departments table
Rem     Starts with 280 
CREATE SEQUENCE departments_seq
 START WITH     280
 INCREMENT BY   10
 MAXVALUE       9990
 NOCACHE
 NOCYCLE;
REM ********************************************************************
REM Create the JOBS table to hold the different names of job roles within the company.
REM HR.EMPLOYEES has a foreign key to this table.
Prompt ******  Creating JOBS table ....
CREATE TABLE jobs
    ( job_id         VARCHAR2(10)
    , job_title      VARCHAR2(35)
    CONSTRAINT     job_title_nn  NOT NULL
    , min_salary     NUMBER(6)
    , max_salary     NUMBER(6)
    ) ;
CREATE UNIQUE INDEX job_id_pk 
ON jobs (job_id) ;
ALTER TABLE jobs
ADD ( CONSTRAINT job_id_pk
               PRIMARY KEY(job_id)
    ) ;
REM ********************************************************************
REM Create the EMPLOYEES table to hold the employee personnel 
REM information for the company.
REM HR.EMPLOYEES has a self referencing foreign key to this table.
Prompt ******  Creating EMPLOYEES table ....
CREATE TABLE employees
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
     CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
    CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
    CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
    CONSTRAINT     emp_job_nn  NOT NULL
    , salary         NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0) 
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;
CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;
ALTER TABLE employees
ADD ( CONSTRAINT     emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES departments
    , CONSTRAINT     emp_job_fk
                     FOREIGN KEY (job_id)
                      REFERENCES jobs (job_id)
    , CONSTRAINT     emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES employees
    ) ;
ALTER TABLE departments
ADD ( CONSTRAINT dept_mgr_fk
               FOREIGN KEY (manager_id)
                REFERENCES employees (employee_id)
    ) ;
Rem     Useful for any subsequent addition of rows to employees table
Rem     Starts with 207 
CREATE SEQUENCE employees_seq
 START WITH     207
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;
REM ********************************************************************
REM Create the JOB_HISTORY table to hold the history of jobs that 
REM employees have held in the past.
REM HR.JOBS, HR_DEPARTMENTS, and HR.EMPLOYEES have a foreign key to this table.
Prompt ******  Creating JOB_HISTORY table ....
CREATE TABLE job_history
    ( employee_id   NUMBER(6)
     CONSTRAINT    jhist_employee_nn  NOT NULL
    , start_date    DATE
    CONSTRAINT    jhist_start_date_nn  NOT NULL
    , end_date      DATE
    CONSTRAINT    jhist_end_date_nn  NOT NULL
    , job_id        VARCHAR2(10)
    CONSTRAINT    jhist_job_nn  NOT NULL
    , department_id NUMBER(4)
    , CONSTRAINT    jhist_date_interval
                    CHECK (end_date > start_date)
    ) ;
CREATE UNIQUE INDEX jhist_emp_id_st_date_pk 
ON job_history (employee_id, start_date) ;
ALTER TABLE job_history
ADD ( CONSTRAINT jhist_emp_id_st_date_pk
      PRIMARY KEY (employee_id, start_date)
    , CONSTRAINT     jhist_job_fk
                     FOREIGN KEY (job_id)
                     REFERENCES jobs
    , CONSTRAINT     jhist_emp_fk
                     FOREIGN KEY (employee_id)
                     REFERENCES employees
    , CONSTRAINT     jhist_dept_fk
                     FOREIGN KEY (department_id)
                     REFERENCES departments
    ) ;
REM ********************************************************************
REM Create the EMP_DETAILS_VIEW that joins the employees, jobs, 
REM departments, jobs, countries, and locations table to provide details
REM about employees.
Prompt ******  Creating EMP_DETAILS_VIEW view ...
CREATE OR REPLACE VIEW emp_details_view
  (employee_id,
   job_id,
   manager_id,
   department_id,
   location_id,
   country_id,
   first_name,
   last_name,
   salary,
   commission_pct,
   department_name,
   job_title,
   city,
   state_province,
   country_name,
   region_name)
AS SELECT
  e.employee_id, 
  e.job_id, 
  e.manager_id, 
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id 
WITH READ ONLY;
COMMIT;


STEP 3: INSERT DATA INTO HR TABLES

Execute below script to insert rows into tables created in previous step

SET VERIFY OFF
ALTER SESSION SET NLS_LANGUAGE=American; 
REM ***************************insert data into the REGIONS table
Prompt ******  Populating REGIONS table ....
INSERT INTO regions VALUES 
        ( 1
        , 'Europe' 
        );
INSERT INTO regions VALUES 
        ( 2
        , 'Americas' 
        );
INSERT INTO regions VALUES 
        ( 3
        , 'Asia' 
        );
INSERT INTO regions VALUES 
        ( 4
        , 'Middle East and Africa' 
        );
REM ***************************insert data into the COUNTRIES table
Prompt ******  Populating COUNTIRES table ....
INSERT INTO countries VALUES 
        ( 'IT'
        , 'Italy'
        , 1 
        );
INSERT INTO countries VALUES 
        ( 'JP'
        , 'Japan'
    , 3 
        );
INSERT INTO countries VALUES 
        ( 'US'
        , 'United States of America'
        , 2 
        );
INSERT INTO countries VALUES 
        ( 'CA'
        , 'Canada'
        , 2 
        );
INSERT INTO countries VALUES 
        ( 'CN'
        , 'China'
        , 3 
        );
INSERT INTO countries VALUES 
        ( 'IN'
        , 'India'
        , 3 
        );
INSERT INTO countries VALUES 
        ( 'AU'
        , 'Australia'
        , 3 
        );
INSERT INTO countries VALUES 
        ( 'ZW'
        , 'Zimbabwe'
        , 4 
        );
INSERT INTO countries VALUES 
        ( 'SG'
        , 'Singapore'
        , 3 
        );
INSERT INTO countries VALUES 
        ( 'UK'
        , 'United Kingdom'
        , 1 
        );
INSERT INTO countries VALUES 
        ( 'FR'
        , 'France'
        , 1 
        );
INSERT INTO countries VALUES 
        ( 'DE'
        , 'Germany'
        , 1 
        );
INSERT INTO countries VALUES 
        ( 'ZM'
        , 'Zambia'
        , 4 
        );
INSERT INTO countries VALUES 
        ( 'EG'
        , 'Egypt'
        , 4 
        );
INSERT INTO countries VALUES 
        ( 'BR'
        , 'Brazil'
        , 2 
        );
INSERT INTO countries VALUES 
        ( 'CH'
        , 'Switzerland'
        , 1 
        );
INSERT INTO countries VALUES 
        ( 'NL'
        , 'Netherlands'
        , 1 
        );
INSERT INTO countries VALUES 
        ( 'MX'
        , 'Mexico'
        , 2 
        );
INSERT INTO countries VALUES 
        ( 'KW'
        , 'Kuwait'
        , 4 
        );
INSERT INTO countries VALUES 
        ( 'IL'
        , 'Israel'
        , 4 
        );
INSERT INTO countries VALUES 
        ( 'DK'
        , 'Denmark'
        , 1 
        );
INSERT INTO countries VALUES 
        ( 'ML'
        , 'Malaysia'
        , 3 
        );
INSERT INTO countries VALUES 
        ( 'NG'
        , 'Nigeria'
        , 4 
        );
INSERT INTO countries VALUES 
        ( 'AR'
        , 'Argentina'
        , 2 
        );
INSERT INTO countries VALUES 
        ( 'BE'
        , 'Belgium'
        , 1 
        );
REM ***************************insert data into the LOCATIONS table
Prompt ******  Populating LOCATIONS table ....
INSERT INTO locations VALUES 
        ( 1000 
        , '1297 Via Cola di Rie'
        , '00989'
        , 'Roma'
        , NULL
        , 'IT'
        );
INSERT INTO locations VALUES 
        ( 1100 
        , '93091 Calle della Testa'
        , '10934'
        , 'Venice'
        , NULL
        , 'IT'
        );
INSERT INTO locations VALUES 
        ( 1200 
        , '2017 Shinjuku-ku'
        , '1689'
        , 'Tokyo'
        , 'Tokyo Prefecture'
        , 'JP'
        );
INSERT INTO locations VALUES 
        ( 1300 
        , '9450 Kamiya-cho'
        , '6823'
        , 'Hiroshima'
        , NULL
        , 'JP'
        );
INSERT INTO locations VALUES 
        ( 1400 
        , '2014 Jabberwocky Rd'
        , '26192'
        , 'Southlake'
        , 'Texas'
        , 'US'
        );
INSERT INTO locations VALUES 
        ( 1500 
        , '2011 Interiors Blvd'
        , '99236'
        , 'South San Francisco'
        , 'California'
        , 'US'
        );
INSERT INTO locations VALUES 
        ( 1600 
        , '2007 Zagora St'
        , '50090'
        , 'South Brunswick'
        , 'New Jersey'
        , 'US'
        );
INSERT INTO locations VALUES 
        ( 1700 
        , '2004 Charade Rd'
        , '98199'
        , 'Seattle'
        , 'Washington'
        , 'US'
        );
INSERT INTO locations VALUES 
        ( 1800 
        , '147 Spadina Ave'
        , 'M5V 2L7'
        , 'Toronto'
        , 'Ontario'
        , 'CA'
        );
INSERT INTO locations VALUES 
        ( 1900 
        , '6092 Boxwood St'
        , 'YSW 9T2'
        , 'Whitehorse'
        , 'Yukon'
        , 'CA'
        );
INSERT INTO locations VALUES 
        ( 2000 
        , '40-5-12 Laogianggen'
        , '190518'
        , 'Beijing'
        , NULL
        , 'CN'
        );
INSERT INTO locations VALUES 
        ( 2100 
        , '1298 Vileparle (E)'
        , '490231'
        , 'Bombay'
        , 'Maharashtra'
        , 'IN'
        );
INSERT INTO locations VALUES 
        ( 2200 
        , '12-98 Victoria Street'
        , '2901'
        , 'Sydney'
        , 'New South Wales'
        , 'AU'
        );
INSERT INTO locations VALUES 
        ( 2300 
        , '198 Clementi North'
        , '540198'
        , 'Singapore'
        , NULL
        , 'SG'
        );
INSERT INTO locations VALUES 
        ( 2400 
        , '8204 Arthur St'
        , NULL
        , 'London'
        , NULL
        , 'UK'
        );
INSERT INTO locations VALUES 
        ( 2500 
        , 'Magdalen Centre, The Oxford Science Park'
        , 'OX9 9ZB'
        , 'Oxford'
        , 'Oxford'
        , 'UK'
        );
INSERT INTO locations VALUES 
        ( 2600 
        , '9702 Chester Road'
        , '09629850293'
        , 'Stretford'
        , 'Manchester'
        , 'UK'
        );
INSERT INTO locations VALUES 
        ( 2700 
        , 'Schwanthalerstr. 7031'
        , '80925'
        , 'Munich'
        , 'Bavaria'
        , 'DE'
        );
INSERT INTO locations VALUES 
        ( 2800 
        , 'Rua Frei Caneca 1360 '
        , '01307-002'
        , 'Sao Paulo'
        , 'Sao Paulo'
        , 'BR'
        );
INSERT INTO locations VALUES 
        ( 2900 
        , '20 Rue des Corps-Saints'
        , '1730'
        , 'Geneva'
        , 'Geneve'
        , 'CH'
        );
INSERT INTO locations VALUES 
        ( 3000 
        , 'Murtenstrasse 921'
        , '3095'
        , 'Bern'
        , 'BE'
        , 'CH'
        );
INSERT INTO locations VALUES 
        ( 3100 
        , 'Pieter Breughelstraat 837'
        , '3029SK'
        , 'Utrecht'
        , 'Utrecht'
        , 'NL'
        );
INSERT INTO locations VALUES 
        ( 3200 
        , 'Mariano Escobedo 9991'
        , '11932'
        , 'Mexico City'
        , 'Distrito Federal,'
        , 'MX'
        );
REM ****************************insert data into the DEPARTMENTS table
Prompt ******  Populating DEPARTMENTS table ....
REM disable integrity constraint to EMPLOYEES to load data
ALTER TABLE departments 
  DISABLE CONSTRAINT dept_mgr_fk;
INSERT INTO departments VALUES 
        ( 10
        , 'Administration'
        , 200
        , 1700
        );
INSERT INTO departments VALUES 
        ( 20
        , 'Marketing'
        , 201
        , 1800
        );
INSERT INTO departments VALUES 
        ( 30
        , 'Purchasing'
        , 114
        , 1700
    );
INSERT INTO departments VALUES 
        ( 40
        , 'Human Resources'
        , 203
        , 2400
        );
INSERT INTO departments VALUES 
        ( 50
        , 'Shipping'
        , 121
        , 1500
        );
INSERT INTO departments VALUES 
        ( 60 
        , 'IT'
        , 103
        , 1400
        );
INSERT INTO departments VALUES 
        ( 70 
        , 'Public Relations'
        , 204
        , 2700
        );
INSERT INTO departments VALUES 
        ( 80 
        , 'Sales'
        , 145
        , 2500
        );
INSERT INTO departments VALUES 
        ( 90 
        , 'Executive'
        , 100
        , 1700
        );
INSERT INTO departments VALUES 
        ( 100 
        , 'Finance'
        , 108
        , 1700
        );
INSERT INTO departments VALUES 
        ( 110 
        , 'Accounting'
        , 205
        , 1700
        );
INSERT INTO departments VALUES 
        ( 120 
        , 'Treasury'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 130 
        , 'Corporate Tax'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 140 
        , 'Control And Credit'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 150 
        , 'Shareholder Services'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 160 
        , 'Benefits'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 170 
        , 'Manufacturing'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 180 
        , 'Construction'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 190 
        , 'Contracting'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 200 
        , 'Operations'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 210 
        , 'IT Support'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 220 
        , 'NOC'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 230 
        , 'IT Helpdesk'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 240 
        , 'Government Sales'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 250 
        , 'Retail Sales'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 260 
        , 'Recruiting'
        , NULL
        , 1700
        );
INSERT INTO departments VALUES 
        ( 270 
        , 'Payroll'
        , NULL
        , 1700
        );
REM ***************************insert data into the JOBS table
Prompt ******  Populating JOBS table ....
INSERT INTO jobs VALUES 
        ( 'AD_PRES'
        , 'President'
        , 20080
        , 40000
        );
INSERT INTO jobs VALUES 
        ( 'AD_VP'
        , 'Administration Vice President'
        , 15000
        , 30000
        );
INSERT INTO jobs VALUES 
        ( 'AD_ASST'
        , 'Administration Assistant'
        , 3000
        , 6000
        );
INSERT INTO jobs VALUES 
        ( 'FI_MGR'
        , 'Finance Manager'
        , 8200
        , 16000
        );
INSERT INTO jobs VALUES 
        ( 'FI_ACCOUNT'
        , 'Accountant'
        , 4200
        , 9000
        );
INSERT INTO jobs VALUES 
        ( 'AC_MGR'
        , 'Accounting Manager'
        , 8200
        , 16000
        );
INSERT INTO jobs VALUES 
        ( 'AC_ACCOUNT'
        , 'Public Accountant'
        , 4200
        , 9000
        );
INSERT INTO jobs VALUES 
        ( 'SA_MAN'
        , 'Sales Manager'
        , 10000
        , 20080
        );
INSERT INTO jobs VALUES 
        ( 'SA_REP'
        , 'Sales Representative'
        , 6000
        , 12008
        );
INSERT INTO jobs VALUES 
        ( 'PU_MAN'
        , 'Purchasing Manager'
        , 8000
        , 15000
        );
INSERT INTO jobs VALUES 
        ( 'PU_CLERK'
        , 'Purchasing Clerk'
        , 2500
        , 5500
        );
INSERT INTO jobs VALUES 
        ( 'ST_MAN'
        , 'Stock Manager'
        , 5500
        , 8500
        );
INSERT INTO jobs VALUES 
        ( 'ST_CLERK'
        , 'Stock Clerk'
        , 2008
        , 5000
        );
INSERT INTO jobs VALUES 
        ( 'SH_CLERK'
        , 'Shipping Clerk'
        , 2500
        , 5500
        );
INSERT INTO jobs VALUES 
        ( 'IT_PROG'
        , 'Programmer'
        , 4000
        , 10000
        );
INSERT INTO jobs VALUES 
        ( 'MK_MAN'
        , 'Marketing Manager'
        , 9000
        , 15000
        );
INSERT INTO jobs VALUES 
        ( 'MK_REP'
        , 'Marketing Representative'
        , 4000
        , 9000
        );
INSERT INTO jobs VALUES 
        ( 'HR_REP'
        , 'Human Resources Representative'
        , 4000
        , 9000
        );
INSERT INTO jobs VALUES 
        ( 'PR_REP'
        , 'Public Relations Representative'
        , 4500
        , 10500
        );
REM ***************************insert data into the EMPLOYEES table
Prompt ******  Populating EMPLOYEES table ....
INSERT INTO employees VALUES 
        ( 100
        , 'Steven'
        , 'King'
        , 'SKING'
        , '515.123.4567'
        , TO_DATE('17-06-2003', 'dd-MM-yyyy')
        , 'AD_PRES'
        , 24000
        , NULL
        , NULL
        , 90
        );
INSERT INTO employees VALUES 
        ( 101
        , 'Neena'
        , 'Kochhar'
        , 'NKOCHHAR'
        , '515.123.4568'
        , TO_DATE('21-09-2005', 'dd-MM-yyyy')
        , 'AD_VP'
        , 17000
        , NULL
        , 100
        , 90
        );
INSERT INTO employees VALUES 
        ( 102
        , 'Lex'
        , 'De Haan'
        , 'LDEHAAN'
        , '515.123.4569'
        , TO_DATE('13-01-2001', 'dd-MM-yyyy')
        , 'AD_VP'
        , 17000
        , NULL
        , 100
        , 90
        );
INSERT INTO employees VALUES 
        ( 103
        , 'Alexander'
        , 'Hunold'
        , 'AHUNOLD'
        , '590.423.4567'
        , TO_DATE('03-01-2006', 'dd-MM-yyyy')
        , 'IT_PROG'
        , 9000
        , NULL
        , 102
        , 60
        );
INSERT INTO employees VALUES 
        ( 104
        , 'Bruce'
        , 'Ernst'
        , 'BERNST'
        , '590.423.4568'
        , TO_DATE('21-05-2007', 'dd-MM-yyyy')
        , 'IT_PROG'
        , 6000
        , NULL
        , 103
        , 60
        );
INSERT INTO employees VALUES 
        ( 105
        , 'David'
        , 'Austin'
        , 'DAUSTIN'
        , '590.423.4569'
        , TO_DATE('25-06-2005', 'dd-MM-yyyy')
        , 'IT_PROG'
        , 4800
        , NULL
        , 103
        , 60
        );
INSERT INTO employees VALUES 
        ( 106
        , 'Valli'
        , 'Pataballa'
        , 'VPATABAL'
        , '590.423.4560'
        , TO_DATE('05-02-2006', 'dd-MM-yyyy')
        , 'IT_PROG'
        , 4800
        , NULL
        , 103
        , 60
        );
INSERT INTO employees VALUES 
        ( 107
        , 'Diana'
        , 'Lorentz'
        , 'DLORENTZ'
        , '590.423.5567'
        , TO_DATE('07-02-2007', 'dd-MM-yyyy')
        , 'IT_PROG'
        , 4200
        , NULL
        , 103
        , 60
        );
INSERT INTO employees VALUES 
        ( 108
        , 'Nancy'
        , 'Greenberg'
        , 'NGREENBE'
        , '515.124.4569'
        , TO_DATE('17-08-2002', 'dd-MM-yyyy')
        , 'FI_MGR'
        , 12008
        , NULL
        , 101
        , 100
       ..
Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

This blog post is a quick summary of How to become a DBA course - A 100% free course which speaks about everything you need to become an Oracle DBA.

Learning SQL is just not enough to become an Oracle DBA. You need much more skills and learn other things to crack your first job interview. In this blog post, I would like to talk about 15 things you must know as a Junior (Fresher) DBA even before you would like to start your career as database administrator.

1. Basic Linux Commands and Scripting

98% of the servers in all the MNCs out there use Unix or Linux operating system (or some flavor of Unix of Linux OS). It is obvious that the Oracle database you will be working on would also be hosted on some Linux server. And, not having good hands-on knowledge on Linux will be one of the biggest road blocks in your path to become a DBA.

We have Linux Administration course that covers all the aspects of Linux commands right from the Linux basics and gradually providing an overview of shell scripting in Linux. The course further advances by equipping you with Linux networking.

2. Basic Virtualization Knowledge

Now that you know the importance of Linux operating system, the next big question is how to practice it. In order to learn Linux, you need to install it on your laptop (or desktop) to master it. In order to do so, you must install virtualization software like VMWare or Oracle Virtual Box.

A virtualization software allows you to run another operating system on top of your Windows system. This will allow you to run both Windows and Linux operating system on one PC.

I have published Foundation for DBA course, a 100% free course (I mean it ;), which will help you setup your own Linux machine. Go do it now!

3. SQL Fundamentals Knowledge

SQL is the standard language to read and write data from Oracle database. To be very frank, SQL is the standard language to read and write data from any database. The syntax might be different for different databases but overall SQL query structure will be same.

So why do you need to learn SQL in order to become a database administrator. Good question! The thing is when you work with application team, they might need your help in order to write more efficient queries and quick response time.

As a DBA, you must be very good at SQL language (not because application team will approach you ;) because you will be the owner of the database server. You will be responsible for analyzing lot SQL queries which are run inside the database.

4. Knowledge on Database Normalization

Database normalization is a technique to organize data in the form of tables (rows and columns) or most appropriately relational tables (coz we work on RDBMS). This involves taking raw data and convert it into meaningful tables. The process involves you to push data from Three Normal Forms (there are more but three is mostly enough).

One of the beauty of database normalization is, you become familiar with unorganized data. You will be able to identify data dependency, column dependency, table dependency and also remove unnecessary columns or rows.

Pro Tip: You must an expert when it comes to database normalization because it helps you to work with database architect in order to design table structures which are easy to query with less response time.

5. Overview of PL/SQL language

So the application or development team ran a PL/SQL code which has some issues, they will straight away contact DBA team. And! you cannot put blank face saying "I just love SQL and have no idea about PL/SQL" :)

Its a good idea to have an overview of PL/SQL language. No really in depth because it is used by Oracle developers to write programs. As a DBA, just having an understanding of how PL/SQL works and basic PL/SQL programs should be good enough.

6. Installing and Configuring Oracle Database

As a DBA, you are responsible for setting up databases on different servers. This includes performing operating system level pre-requisites, downloading proper database software version, installing oracle software, create database, configure network and much more.

Installing and configuring Oracle database is one of the primary jobs of a DBA.

7. Excellent Understanding of Oracle Database Architecture

When I take any DBA interview, I always look for strong foundation in database architecture. Having a perfect understanding of Oracle database architecture will give mileage to your DBA career. It allows you to understand Oracle performance tuning concepts easily.

Do you want to master Oracle database administration? Come, join me and master Oracle database architecture along with real-time database administration course. You get to learn database administration within a month!

8. How to Plan Your Database Creation

I know when you practice database creation in your lab machine, you fire DBACA and boom - database created. But its not the case in real time. You need to check many things even before you can think of firing DBCA to create database.

Check out my blog post on how to plan your database creation here.

9. Database Startup and Shutdown Modes

If you are not yet a DBA, there are different stages database passes through when you start and shutdown the database. Each stage or phase has a significant use. For example, you put database in Mount mode to perform major administrative tasks.

When you perform database recovery, you will realize the importance of each database file and different database startup modes. Like, in order to take your database to no-mount stage, you need to have SPFILE. To take your database to Mount stage, you need control files and it goes on.

Its very important to know every details in which database starts and how database shutdown happens.

10. Database Health Checkup and Files Multiplexing

With so many production databases in any given environment, its very critical to check database health. Whenever there is some error inside database, you troubleshoot the issue and then perform a quick database health checkup. This allows you to confirm that database is running fine and its ready to accept user connections.

Some of the database health checkup includes:

  • Checking database alert log for ORA errors
  • Checking database open mode and listener status
  • Checking tablespace utilization
  • Checking latest database backup

and few more tasks.

11. Capacity planning & tablespace management

A DBA must maintain adequate space in database tablespaces in order to have smooth functioning. All the tablespaces must be below threshold value and they all must be online (In case if no tablespace is put offline).

Check out one of my blog posts on Query to Check Tablespace Utilization.

I have also published one YouTube video on Tablespace Administration - How to work with Tablespaces in Real-Time?

12. User, profile and role management

Whenever application team adds new members in their team, they might reach out DBA to add new uses inside the database. A database user has permission to access the database server over a network and run queries inside the database.

Different database users have different permissions. For example, a reporting user will only have SELECT permissions on tables. Same way different users will have different access permissions over tables and other objects inside the database.

A DBA is responsible for creating new users, adding or modifying profiles, granting permissions, creating roles and assigning it to users, dropping users and lot of other user management tasks.

13. Database networking

Database networking is key to access database server from client machine. An application connects to database server over a network to access the database. Oracle listener runs on database server where all the client's connections land first and then handed over to the database.

Database networking involves configuring listener, configuring TNS entries, checking remote database connectivity, registering database with the listener and making sure application is able to connect to the database.

14. Data Export and Import between databases

One of the constant activities that are performed by DBA is export and import of data. In many projects, DBA has to perform exports from production database and import into the test or development databases. The activity frequency is generally weekly but in some projects, it is done daily as well.

There are different database utilities which are used to perform table/schema/entire database export and import. You even use RMAN refresh to perform entire database cloning or duplication.

15. Database backup and recovery

I cannot stress on how important database backup and recovery is. Understand this, if you are not able to recover a database after crash, then there is no point in being a DBA :P

Database backups include logical backups and physical backups. The logical backups are nothing but backups of database objects which you take via Export Import or Data Pump utility. The physical backups include cold backup, hot backup and RMAN backups.

Database recovery is where a DBA must be very good at. There are 1000 of database crash scenarios but you do not have to learn all the scenario as you will not encounter all those in your job (until you are most unlucky guy :). You must know the recover concepts well so that in any type of crash, you are still able to bring up the database using backups you have.

Quick Summary

All in all, in order for you to become a DBA, you must have proper hands on experience on all the 15 things mentioned above. You can even checkout my Oracle DBA for Beginners course where you learn to become a database administrator within a month !!

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Database creation process involves several operating system files to work together. You create database only once and later on keep on adding more data files.

Before you can simply go ahead and start creating your database in real-time, you must know the database planning tasks.

Recommended Database Planning Tasks
  1. Plan the database tables and indexes and estimate the amount of space they will require.
  2. Plan the layout of the underlying operating system files your database will comprise.
    • Proper distribution of files can improve database performance dramatically by distributing the I/O during file access. Example, put redo log files and data files on separate physical HDD to improve I/O and response time.
  3. Select the global database name. Create the global database name by setting both the DB_NAME and DB_DOMAIN initialization parameters.
  4. Familiarize yourself with the initialization parameters contained in the initialization parameter file
  5. Select the database character set
  6. Consider which time zones your database must support
  7. Select the standard database block size. This is specified at database creation by the DB_BLOCK_SIZE initialization parameter and cannot be changed after the database is created
  8. Determine the appropriate initial sizing for the SYSAUX tablespace
  9. Plan to use a default tablespace for non-SYSTEM users to prevent inadvertently saving database objects in the SYSTEM tablespace
  10. Plan to use an undo tablespace to manage your undo data
  11. Develop a backup and recovery strategy to protect the database from failure

Prepare to create the database by research and careful planning.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 


One of the best part about being an Oracle DBA is - Database ownership! As a DBA, you actually own the database. That being said, one big question arises - which company would handover critical databases to a fresher?

To be honest with you, there are not enough opportunities for fresher DBAs. But, do not worry. I will give you 5 ideas from my experience to get a job as a fresher DBA!

Understand this, if you own a company, will you hand over your company database in the hands of inexperienced DBAs? Database being the backbone of a company, you as a fresher needs to prove yourself to be trusted and hired → I will talk more about it in next lecture but before that, let me give my 5 ideas to get a job as a fresher DBA:

FIRST IDEA - CAMPUS RECRUITMENTS

If you are into your final SEM or final year of college and if you have campus recruitments, it's a very smooth entry for freshers to become a DBA. Based on your aptitude, interest and attitude, you will easily be hired as a software trainee.

Once you join the company, you can always share your interest in database with your training manager. The company will invest in you to learn database administration and absorbe you in one of their projects

So, for people who are about to graduate and have campus recruitments in their college, this should be your first place to start as a fresher DBA

SECOND IDEA - APPLY IN STARTUPS

21st century is all about rapid innovation and the new age entrepreneurs are coming up with innovative solutions to simplify our problems. Entrepreneurs are constantly looking for fresh talent out of college and it is very easy for them to mould fresh candidates into their startup culture.

One more reason why entrepreneurs would choose fresher DBAs is → they are constantly looking at minimizing the operational costs. Its very easy for entrepreneurs to hire fresher DBAs and train fresher DBAs for low salaries when compared to experienced DBAs.

So, as a fresher DBA, the second place you can try to apply should be startups

THIRD IDEA - GO FOR DBA INTERNSHIPS

Another way to become a DBA is to apply for DBA internships. There are lot of companies including startups which offer internships. The biggest benefit that you get from internships is → you get real-time experience along with internship certificate. Once you spend good enough time in an internship, you can apply for experienced DBA position depending upon your internship duration.

So, as a fresher DBA, you can apply for internships and then use the internship as a launch pad to apply for experienced DBA job!

FOURTH IDEA - ASK YOUR DBA FRIENDS TO REFER YOU

Check your phonebook, facebook, linkedin friends who are currently working in MNC. All the MNCs have an employee referral program. Also, the company employees get to know about open positions much earlier than job sites.

Connect to all such friends, send in your resume and ask them to keep an eye on fresher DBA openings.

FIFTH IDEA - KEEP LEARNING AND KEEP HUNTING

Once you are ready for fresher DBA interviews, you won’t get job immediately. Hence, the best way is to still keep on learning new topics in Oracle Database administration. Try to post your resume on as many job sites as possible.

Ultimately, I gave you my 5 ideas which you can try to get a job as a fresher DBA. Join my How to become a DBA course which is 100% free and it talks about everything you need to know before starting Oracle career!


Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview