Oracle 11g basics,part 1,Oracle Teacher Sourav,Kolkata 09748184075

set ORACLE_SID=sysdba

echo %ORACLE_SID%

set ORACLE_HOME=E:\app\sourav\product\11.2.0\dbhome_1

echo %ORACLE_HOME%

Now connect to oracle

start sqlplus

sqlplus /nolog

connect as sysdba

connect / as sysdba;

run a sample query and find the name of the databse you are connected to


select name from v$database;

to see the instance name which you are connected with

select * from global_name;


exit

Now we like to use the sql developer



****************************************************


shut down the database


log in as sysdba

shutdown immediate;

exit


start the database

log in as sysdba

startup;



***********************************************************
Data types supported by oracle

char
varchar2
number
date
timestamp
BLOB
CLOB
User Defined data types


*****************************************************************

Find the number of rows in a table

select count(*) from dba_tablespaces;

(dba_tablespaces give information about tablespaces,during the installation oracle create some tablespaces by default,those tablespaces hold all the data)
to find all the tablespaces currently existing in the database


select * from dba_tablespaces;


we can select particular columns using select such as

select TABLESPACE_NAME,STATUS from DBA_TABLESPACES;

we can also change the order by changing the name of columns







****************************************************************


to know the structure of the table use describe

describe dba_tablespaces;

desc dba_tablespaces;


***************************************************************


to find the total number of rows in a table

select count(*) from dba_tablespaces;


***************************************************************


filtering data using where clause


select * from dba_roles where role='CONNECT';


*****************************************************************


To make your pc faster

go to services.msc

and change these services startup from automatic to manual as written below

(My oracle instance name is sysdba instead of orcl)

Oracledbconsolesysdba

oraclemtsrecoveryservice

oracleoradb11g_home1tnslistener

oracleservicesysdba


you have to start these services manually every time you want to use oracle



*********************************************************************

Find the user list for your oracle database;

select username from dba_users;

let's see if the user scott is enabled or disabled

SELECT username, account_status   from dba_users;

if you find the username scott is locked let's try to enable/unlock that user

alter user scott account unlock;

let's logout and try to use scott

exit


sqlplus /nolog

if you use this command to connect connect using scott and the password tiger it
will prompt you to change the password as it is expired


you could also change the user status and password in a same line such as

alter user hr identified by 123456 account unlock;

after loggin in you can verify it by using this sql command

select * from global_name

then go to sql developer and create another connect using hr and password 123456 and see the tables created for the hr schema




*****************************************************************************************

directly login as scott


sqlplus scott/tiger@orcl


show the date

SELECT SYSDATE FROM dual;


login as sysdba and run the storeschema.sql

To end SQL*Plus, you enter EXIT. To reconnect to the store schema
in SQL*Plus, you enter store as the user name with a password
of store_password.


connect using sqlplus like this

sqlplus store@sysdba

enter the password



****************************************************************************************************

creating the database user


CREATE USER sourav IDENTIFIED BY 123456;

If you want the user to be able to work in the database, the user must be granted the
necessary permissions to do that work. In the case of store, this user must be able to log onto
the database (which requires the connect permission) and create items like database tables
(which requires the resource permission). Permissions are granted by a privileged user (for
example, the system user) using the GRANT statement.



GRANT connect, resource TO sourav;


*************************************************************************************************************


Oracle data types explanation


Format Number Supplied Number Stored
NUMBER 1234.567 1234.567
NUMBER(6, 2) 123.4567 123.46
NUMBER(6, 2) 12345.67 Number exceeds the specified precision and
is therefore rejected by the database.


CHAR(length) Stores strings of a fixed length. The length parameter specifies
the length of the string. If a string of a smaller length is stored, it
is padded with spaces at the end. For example, CHAR(2) may
be used to store a fixed-length string of two characters; if 'C' is
stored in a CHAR(2), then a single space is added at the end;
'CA' is stored as is, with no padding.


VARCHAR2(length) Stores strings of a variable length. The length parameter specifies
the maximum length of the string. For example, VARCHAR2(20)
may be used to store a string of up to 20 characters in length. No
padding is used at the end of a smaller string.


DATE Stores dates and times. The DATE type stores the century, all four
digits of a year, the month, the day, the hour (in 24-hour format),
the minute, and the second. The DATE type may be used to store
dates and times between January 1, 4712 B.C. and December 31,
4712 A.D.


INTEGER Stores integers. An integer doesn’t contain a floating point: it is a
whole number, such as 1, 10, and 115.


NUMBER(precision,scale)
Stores floating point numbers, but may also be used to store
integers. The precision is the maximum number of digits
(left and right of a decimal point, if used) that may be used for
the number. The maximum precision supported by the Oracle
database is 38. The scale is the maximum number of digits to
the right of a decimal point (if used). If neither precision nor
scale is specified, any number may be stored up to a precision
of 38 digits. Any attempt to store a number that exceeds the
precision is rejected by the database.


BINARY_FLOAT Introduced in Oracle Database 10g, stores a single precision
32-bit floating point number. You’ll learn more about BINARY_
FLOAT later in the section “The BINARY_FLOAT and BINARY_
DOUBLE Types.”


BINARY_DOUBLE Introduced in Oracle Database 10g, stores a double precision
64-bit floating point number.

using sql developer if you connect using store to enter the store schema you will see

The customers table holds the details of the customers. The
following items are held in this table:
First name
Last name
Date of birth (dob)
Phone number

to create a table like in this schema the sql command is this


CREATE TABLE customers (
customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
dob DATE,
phone VARCHAR2(12)
);



As you can see, the customers table contains five columns,

one for each item in the

previous list, and an extra column named customer_id. The columns are
customer_id Contains a unique integer for each row in the table. Each table should
have one or more columns that uniquely identifies each row; the column(s) are known as
the primary key. The CONSTRAINT clause indicates that the customer_id column is the
primary key. A CONSTRAINT clause restricts the values stored in a column, and, for the
customer_id column, the PRIMARY KEY keywords indicate that the customer_id
column must contain a unique value for each row. You can also attach an optional
name to a constraint, which must immediately follow the CONSTRAINT keyword—for
example, customers_pk. You should always name your primary key constraints, so
that when a constraint error occurs it is easy to spot where it happened.


first_name Contains the first name of the customer. You’ll notice the use of the NOT
NULL constraint for this column—this means that a value must be supplied for first_
name when adding or modifying a row. If a NOT NULL constraint is omitted, a user
doesn’t need to supply a value and the column can remain empty.

last_name Contains the last name of the customer. This column is NOT NULL, and
therefore a value must be supplied when adding or modifying a row.
dob Contains the date of birth for the customer. Notice that no NOT NULL constraint is
specified for this column; therefore, the default NULL is assumed, and a value is optional
when adding or modifying a row.

phone Contains the phone number of the customer. This is an optional value.


You can see the rows in the customers table for yourself by executing the following
SELECT statement using SQL*Plus:
SELECT * FROM customers;

The product_types table holds the names of the product
types sold by the store. This table is created by the store_schema.sql script using the
following CREATE TABLE statement:


CREATE TABLE product_types (
product_type_id INTEGER CONSTRAINT product_types_pk PRIMARY KEY,
name VARCHAR2(10) NOT NULL
);



The product_types table contains the following two columns:

product_type_id uniquely identifies each row in the table; the product_type_id
column is the primary key for this table. Each row in the product_types table must
have a unique integer value for the product_type_id column.

name contains the product type name. It is a NOT NULL column, and therefore a value
must be supplied when adding or modifying a row.

You can see the rows in the product_types table for yourself by executing the following
SELECT statement using SQL*Plus:

SELECT * FROM product_types;


The products Table The products table holds the products sold by the store. The
following pieces of information are held for each product:
Product type
Name
Description
Price

The sql for creating the product table


CREATE TABLE products (
product_id INTEGER CONSTRAINT products_pk PRIMARY KEY,
product_type_id INTEGER
CONSTRAINT products_fk_product_types
REFERENCES product_types(product_type_id),
name VARCHAR2(30) NOT NULL,
description VARCHAR2(50),
price NUMBER(5, 2)
);


The columns in this table are as follows:

product_id uniquely identifies each row in the table. This column is the primary key
of the table.

product_type_id associates each product with a product type. This column is a
reference to the product_type_id column in the product_types table; it is known
as a foreign key because it references a column in another table. The table containing
the foreign key (the products table) is known as the detail or child table, and the table
that is referenced (the product_types table) is known as the master or parent table.

This type of relationship is known as a master-detail or parent-child relationship. When
you add a new product, you associate that product with a type by supplying a matching
product_types.product_type_id value in the products.product_type_id
column .

name contains the product name, which must be specified, as the name column is
NOT NULL.

description contains an optional description of the product.

price contains an optional price for a product. This column is defined as NUMBER(5,
2)—the precision is 5, and therefore a maximum of 5 digits may be supplied for this
number. The scale is 2; therefore 2 of those maximum 5 digits may be to the right of the
decimal point.

***********************************************************************************************************


Adding modifying and removing rows in a table

Adding a Row to a Table
You use the INSERT statement to add new rows to a table. You can specify the following
information in an INSERT statement:

The table into which the row is to be inserted
A list of columns for which you want to specify column values
A list of values to store in the specified columns
When inserting a row, you need to supply a value for the primary key and all other columns
that are defined as NOT NULL. You don’t have to specify values for the other columns if you don’t
want to; those columns will be automatically set to null if you omit values for them.
You can tell which columns are defined as NOT NULL using the SQL*Plus DESCRIBE
command. The following example DESCRIBEs the customers table:

SQL> DESCRIBE customers
Name Null? Type
----------------------------------------- -------- ------------
CUSTOMER_ID NOT NULL NUMBER(38)
FIRST_NAME NOT NULL VARCHAR2(10)
LAST_NAME NOT NULL VARCHAR2(10)
DOB DATE
PHONE VARCHAR2(12)

As you can see, the customer_id, first_name, and last_name columns are NOT NULL,
meaning that you must supply a value for these columns. The dob and phone columns don’t
require a value; you could omit the values if you wanted, and they would be automatically set to null.
Go ahead and run the following INSERT statement, which adds a row to the customers
table; notice that the order of values in the VALUES list matches the order in which the columns
are specified in the column list:

SQL> INSERT INTO customers (
 customer_id, first_name, last_name, dob, phone) VALUES ( 6, 'Fred', 'Brown', '01-JAN-1970', '800-555-1215' );


SELECT *
FROM customers;

will show the added row


Possible error when inserting


When a row is added to the customers table, a unique value for the customer_id column
must be given. The Oracle database will prevent you from adding a row with a primary key value
that already exists in the table; for example, the following INSERT statement causes an error
because a row with a customer_id of 1 already exists:
SQL> INSERT INTO customers (
2 customer_id, first_name, last_name, dob, phone
3 ) VALUES (
4 1, 'Lisa', 'Jones', '02-JAN-1971', '800-555-1225'
5 );
INSERT INTO customers (
*
ERROR at line 1:
ORA-00001: unique constraint (STORE.CUSTOMERS_PK) violated


Modifying an Existing Row in a Table


You use the UPDATE statement to change rows in a table. Normally, when you use the UPDATE
statement, you specify the following information:
The table containing the rows that are to be changed
A WHERE clause that specifies the rows that are to be changed
A list of column names, along with their new values, specified using the SET clause
You can change one or more rows using the same UPDATE statement. If more than one row is
specified, the same change will be made for all the rows. The following example updates customer
#2’s last_name to Orange:

UPDATE customers
SET last_name = 'Orange'
WHERE customer_id = 2;
1 row updated.
SQL*Plus confirms that one row was updated.


SELECT *
FROM customers
WHERE customer_id = 2;


Removing a Row from a Table

You use the DELETE statement to remove rows from a table. You typically use a WHERE clause to
limit the rows you wish to delete; if you don’t, all the rows will be deleted from the table.
The following DELETE statement removes customer #2:
DELETE FROM customers
WHERE customer_id = 2;
1 row deleted.
To undo the changes you’ve made to the rows, you use ROLLBACK:
ROLLBACK;


********************************************************************

show all the tables in your schema

log in as store

conn store@sysdba;

enter password

select TABLE_NAME from user_tables



************************************************************************


To see the post number and the instance name of your oracle database

open this file in notepad

E:\app\sourav\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

*******************************************************************************