Transcript Intro_To_Oracle

Intro To Oracle :part 1
1. Save your Memory Usage & Performance.
2. Oracle Login ways.
3. Adding Database to DB Trees.
4. How to Create your own user(schema).
5. Roles & Privileges of User.
6. Table Space for Specific user.
7. SQL Scripts ( Part 2 of Presentation)
Save your Memory Usage & Performance.
1.
Once you guys have installed the oracle and created the database instance (SID). Go to
your Enterprise Manager and click on your database and you will see following.
2.
Following information give us , port number , host name where our database is resided ,
and by which service name it is running in our pc as well.
3. Now go to following location.
Settings Control PanelAdministrative tools.
4. Double click on “services”.
we have to find our database service (SID).
It will look like following
5. Probably your start type would be “Automatic”. ( It means it will start running when ever
your pc turns on.
6. We have to change it to Manual. Reason is like some time if we don’t to use the oracle , it
will eat our memory and slow down our cpu performance , therefore we will set the start –up
type of our database(SID) services to “manual”.
** Every time you want to use your database , you have to go to your services and start it
manually.
7. Right click on it and go to Properties. And set it to Automatic , click ok.
Oracle Login ways.
1.
Login from Enterprise Manager.(user friendly IDE)
2.
Login from SQL+.
3.
Login into SQLPLUS from CMD mode.
4.
I will only explain CMD mode.
5.
Go to your cmd mode and type following
“C:\>sqlplus system/fahad@ORCL_localhost”
“C:\>sqlplus system/fahad@ORCL_192.168.1.48”
6. In above statement “system” is the ID , “fahad” is the password & “ORCL” is the name
of your database (SID).
** Most of the time users on network , and if your database in which you want to login
reside on other machine , you can provide the ip address of that machine to login.
7. You can only type “sqlplus” at your cmd mode , and it will prompt for user name and
password , it will login into the database of your local machine at that moment.
8. Why I like this because , I can use DOSKEY. So don’t
copy paste the previous stuff.
Adding Database to DB Trees.
1.
If you are on network , and want to access the database which is not in your machine.
2.
First you have to Open your Enterprise manager.
3.
Add that database in your Enterprise manager , so that it will be available to you.
4. Now , following window will pop-up.
5. In order to access the database , we need that Machine ipaddress , port nos usually
1521 , SID( database name ).
5. Click ok.( Once database is added , you can see in your list of database.
6. You also need( login id and password ) to access that DB.
1. SQL
There are 2 kinds of SQL STATEMENTS.
1.
DML.( Data Manipulation Language)
select , insert , update , merge , delete , locking of table
DDL( Data Definition Language)
create , alter , drop , rename , truncate , grant, revoke, auditing as well
2.
There are some others too , Transaction , Session and System Control.
Accounts Authentication
When a user connects to an Oracle database, he must be authenticated. Oracle can be
configured for one of three types of authentication:
§
The default is database authentication. With database authentication, Oracle
checks that the user is a legitimate user for that database and has supplied the correct
password.
§
With external authentication, Oracle only checks that the user is a legitimate user
for that database; the password is validated by the operating system or network.
With global authentication, Oracle only checks that the user is a legitimate user for that database.
The password is validated by the Oracle Security Service, a separately licensed and configured
service.
We will create normal user which is just a normal
database authenticated user.
. First login as DBA , which is your “system” account
SQL> conn as sysdba
Enter user-name: system
Enter password:
Connected.
SQL>
Now , we are login as “system” user which is our DBA ,
and it has priv/role to create another user.( by default)
Creating user
Now , we can create new user “FAHAD” by typing following
CREATE USER FAHAD IDENTIFIED BY khan40;
In above statement “khan40” is the password.
Change Password.
We , can also change the user Attributes , like change the password of user
ALTER USER FAHAD IDENTIFIED BY sanjosestate;
Setting Password Expiration
ALTER USER FAHAD IDENTIFIED BY database;
ALTER USER FAHAD PASSWORD EXPIRE;
Limiting the Number of User Accounts
There is one parameter in Oracle , “license_max_users “
We can set this to our own requirement .
Oracle has three types of privileges:
•Object privileges are permissions on schema objects, such as tables, views,
programmer-defined functions, and libraries.
System privileges give the grantee the ability to perform system-level activities,
such as connecting to the database, altering the user session, creating tables, or
creating users.
•Role privileges are those privileges that a user owns by way of a role.
1. How to Create your own (schema).
So once you have created the user , and you login into oracle as that user .for e.g.
Steps ( CMD MODE)
//go to cmd ,
Connect as sysdba
Create new user ( create user FAHAD identified by hussain)
Conn fahad
Hussain
Create table mytable(name varchar(40) , phnos varchar(50))
Tablespace system;
Now if you type this query ,
select table_name form user_tables;
You will only see “MYTABLE” , because you are login as “FAHAD” so you will see
only FAHAD tables.
Create Schema and tables from Enterprise Manager.
1.
Create user
click on security , Right click on user
Now create table under that user
Hit CTR+N on enterprise Manager
You will see following , select table from this list , and hit CREATE
What schema is?
Schema consist of tables , indexes , views , and others as you can see
here , schema “fahad” has following object in it.
You will see following wizard , good part about oracle is that it will give you query as well
even though you are using Wizard.
Now you can see in the drop down , it will ask you that in which schema would you like to
add this table, you can see I select mine , actually schema name most of the time is the user
name.
Create table space from EP
If I have 2nd presentation , I can tell more about how
to write sql scripts.
Table Space for Specific user.
Assigning a Default Table space
The default table space is where the user's objects (tables, indexes, and clusters) will be
placed if an explicit TABLESPACE clause is not included in that object's CREATE
statement. The default is the SYSTEM table space, which is generally not a good place
to put non-data dictionary objects.
CREATE USER FAHAD IDENTIFIED BY hussain DEFAULT TABLESPACE
user_data;
CREATE USER AMIR IDENTIFIED EXTERNALLY;
ALTER USER AMIR DEFAULT TABLESPACE devl_data;
Table space