Transcript Intro To Oracle

Intro To Oracle :part 1
made by: Fahad Hussain
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
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;
SQLLDR FUNCTIONALITY
Maximum Sql lddr Performance
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Maximizing SQL*Loader Performance
SQL*Loader is flexible and offers many options that should be considered to maximize the speed of data
loads. These include:
· Use Direct Path Loads - The conventional path loader essentially loads the data by using standard insert
statements. The direct path loader (direct=true) loads directly into the Oracle data files and creates blocks in
Oracle database block format. The fact that SQL is not being issued makes the entire process much less
taxing on the database. There are certain cases, however, in which direct path loads cannot be used (clustered
tables). To prepare the database for direct path loads, the script
$ORACLE_HOME/rdbms/admin/catldr.sql.sql must be executed.
· Disable Indexes and Constraints. For conventional data loads only, the disabling of indexes and
constraints can greatly enhance the performance of SQL*Loader.
· Use a Larger Bind Array. For conventional data loads only, larger bind arrays limit the number of calls
to the database and increase performance. The size of the bind array is specified using the bindsize
parameter. The bind array's size is equivalent to the number of rows it contains (rows=) times the maximum
length of each row.
· Use ROWS=n to Commit Less Frequently. For conventional data loads only, the rows parameter
specifies the number of rows per commit. Issuing fewer commits will enhance performance.
· Use Parallel Loads. Available with direct path data loads only, this option allows multiple SQL*Loader
jobs to execute concurrently.
$ sqlldr control=first.ctl parallel=true direct=true
$ sqlldr control=second.ctl parallel=true direct=true
· Use Fixed Width Data. Fixed width data format saves Oracle some processing when parsing the
data. The savings can be tremendous, depending on the type of data and number of rows.
· Disable Archiving During Load. While this may not be feasible in certain environments, disabling
database archiving can increase performance considerably.
· Use unrecoverable. The unrecoverable option (unrecoverable load data) disables the writing of the data
to the redo logs. This option is available for direct path loads only.
Using the table table_with_one_million_rows, the following benchmark tests were performed with the
various SQL*Loader options. The table was truncated after each test
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
bad – A file that is created when at least one record from the input file is rejected. The rejected data records are placed in this file. A record
could be rejected for many reasons, including a non-unique key or a required column being null.
· bindsize – [256000] The size of the bind array in bytes.
· columnarrayrows – [5000] Specifies the number of rows to allocate for direct path column arrays.
· control – The name of the control file. This file specifies the format of the data to be loaded.
· data – The name of the file that contains the data to load.
· direct – [FALSE] Specifies whether or not to use a direct path load or conventional load.
· discard – The name of the file that contains the discarded rows. Discarded rows are those that fail the WHEN clause condition when
selectively loading records.
· discardmax – [ALL] The maximum number of discards to allow.
· errors – [50] The number of errors to allow on the load.
· external_table – [NOT_USED] Determines whether or not any data will be loaded using external tables. The other valid options include
GENERATE_ONLY and EXECUTE.
· file – Used only with parallel loads, this parameter specifies the file to allocate extents from.
· load – [ALL] The number of logical records to load.
· log – The name of the file used by SQL*Loader to log results.
· multithreading – The default is TRUE on multiple CPU systems and FALSE on single CPU systems.
· parfile – [Y] The name of the file that contains the parameter options for SQL*Loader.
· parallel – [FALSE] Specifies a filename that contains index creation statements.
· readsize – The size of the buffer used by SQL*Loader when reading data from the input file. This value should match that of bindsize.
· resumable – [N] Enables and disables resumable space allocation. When “Y”, the parameters resumable_name and resumable_timeout
are utilized.
· resumable_name – User defined string that helps identify a resumable statement that has been suspended. This parameter is ignored
unless resumable = Y.
· resumable_timeout – [7200 seconds] The time period in which an error must be fixed. This parameter is ignored unless resumable = Y.
· rows – [64] The number of rows to load before a commit is issued (conventional path only). For direct path loads, rows are the number of
rows to read from the data file before saving the data in the datafiles.
· silent – Suppress errors during data load. A value of ALL will suppress all load messages. Other options include DISCARDS, ERRORS,
FEEDBACK, HEADER, and PARTITIONS.
· skip – [0] Allows the skipping of the specified number of logical records.
· skip_unusable_indexes – [FALSE] Determines whether SQL*Loader skips the building of indexes that are in an unusable state.
· skip_index_maintenance – [FALSE] Stops index maintenance for direct path loads only.
· streamsize – [256000] Specifies the size of direct path streams in bytes.
· userid – The Oracle username and password.
To check which options are available in any release of SQL*Loader use this command:
sqlldr help=y
External tables in 9i
• External Tables in 9i
• In version 9, Oracle introduced the concept of external
tables. These objects provide a way to access data stored
outside of the database with a SQL statement. These also
provide an alternative to SQL*Loader, given their
simplicity and comparable performance. Data loads can be
performed by simple statements like:
• SQL> create table load_table as select * from
external_table;
• When executed, the data would be loaded from an
operating system data file into LOAD_TABLE.
FAQ on SQLLDR
• Check this link , u can upload voices and
images as well from sqlldr.
• http://www.orafaq.com/faqloadr.htm
How to Use Apache utility to
upload in oracle
•
First a Database Access Descriptor (DAD) must be created:
•
Access the database HTTP server main page via a browser (http://yourServer:7777/).
The correct port number is listed in the
$ORACLE_HOME/Apache/Apache/setupinfo.txt file
Click on the "Mod_plsql Configuration Menu" link.
Click on the "Gateway Database Access Descriptor Settings" link.
Click on the "Add Default (blank configuration)" link.
Enter UTILS as the Database Access Descriptor Name. This will be used in the
requesting URL.
Enter the username (UTILS), password (UTILS) and connect string (W2K1) for the
desired database connection.
Select the "Basic" authentication mode.
Enter "documents" for the Document Table.
Enter "docs" for the Document Access Path.
Enter "document_api.download" for the Document Access Procedure.
Click the OK button at the top right of the screen
•
•
•
•
•
•
•
•
•
•
• For the upload to be successful the documents
table must be created with the following structure:
• CREATE TABLE documents ( name
VARCHAR2(256) UNIQUE NOT NULL,
mime_type VARCHAR2(128), doc_size
NUMBER, dad_charset VARCHAR2(128),
last_updated DATE, content_type
VARCHAR2(128), blob_content BLOB )
• /Next we create a package specification which will contain all the necessary code:
• CREATE OR REPLACE PACKAGE
document_api AS PROCEDURE upload
(file IN VARCHAR2); PROCEDURE
download; PROCEDURE download (file
IN VARCHAR2); END; / SHOW ERRORS
•
•
•
•
•
•
•
•
•
•
•
CREATE OR REPLACE PACKAGE BODY document_api AS -- --------------------------------------------------------------------------- PROCEDURE upload (file
IN VARCHAR2) AS -- --------------------------------------------------------------------------- l_real_name VARCHAR2(1000);
BEGIN HTP.htmlopen;
HTP.headopen; HTP.title('File Uploaded');
HTP.headclose; HTP.bodyopen; HTP.header(1, 'Upload Status'); l_real_name
:= SUBSTR(file, INSTR(file, '/') + 1);
BEGIN -- Delete any existing document to allow update. DELETE FROM
documents WHERE name = l_real_name; -Update the prefixed name with the real file name. UPDATE documents SET
name = l_real_name WHERE name = file;
HTP.print('Uploaded ' || l_real_name || ' successfully.');
EXCEPTION WHEN OTHERS THEN HTP.print('Upload of ' || l_real_name
|| ' failed.'); HTP.print(SQLERRM); END; HTP.br; -- Create some links to
demonstrate URL downloads. HTP.br; HTP.print('URL Downloads:');
HTP.br; FOR cur_rec IN (SELECT name FROM documents) LOOP
HTP.anchor('docs/' || cur_rec.name, 'docs/' || cur_rec.name);
HTP.br; END LOOP; -Create some links to demonstrate direct downloads. HTP.br; HTP.print('Direct
Downloads:'); HTP.br; FOR cur_rec IN (SELECT name FROM documents)
LOOP HTP.anchor('document_api.download?file=' || cur_rec.name,
'document_api.download?file=' || cur_rec.name); HTP.br; END LOOP;
HTP.bodyclose; HTP.htmlclose; END;