یادداشت های کلاس اوراکل خاتم

در این نوشته یادداشت های حاصل از کلاس oracle برگزار شده توسط دانشگاه خاتم، آورده شده است.

موارد عملی:

-1-

متغیر محیطی: program files- temp

oracle home : مسیری که اوراکل در آن نصب میشود

oracle base : مسیر بالا تا قبل از db

پوشه نتورک و ادمین زیاد باهاش کار داریم.
زمانی

listener با چورت پیشفرض1521 که به کمک آن به db وصل میشویم.

لیستنر باید بالا باشد. میتوان پورت آن را در صورت باز بودن تغییر داد.

برای وصل شدن مردم به دیتا بیس- باید IP بدم– اسم دیتا بیس ID و پورت(عموما 1521)— میتوانیم یوزر و پسورد هم بدهیم و … به صورت پیشفرض

0- نصب Oracle Database 18c Express Edition
در زیر به ادرس نصب دقت کنید

1- نصب ابزار oracle SQL developer – لینک

2- نصب اوراکلXE184 – این مورد را یا روی vmvare نصب میکنیم یا روی ویندوز- در صورتی که روی ویندوز نصب کنیم در مواقعی که به آن نیاز نداریم میتوانیم از بخش سرویس های ویندوز آن را غیر فعال کنیم.

بعد از نصب:
دو یوزر داریم
sys که بدون یوزر و پسورد میتواند دسترسی داشته باشد.
system که باید یوزر و پسورد داشته باشد.

Oracle SID یا system identifier نام دیتا بیس است.

باید 3 مورد را در اختیار client ها قرار دهیم: Ip و port وSID یا service name

با این سه مورد هر فرد میتواند با ابزار خاص و با یوزر و پسورد به دیتابیس وصل شود.

به صورت پیشفرض با creat user هیچ دسترسی ندارد مگر آنچه به او grant کنیم.

مثلا میگوییم یوزر علی به اسکیمای HR دسترسی داشته باشد در همه جداول. مثلا میگوییم فقط خواندنی باشد(only select )

با فرض نصب مورد 2 :
دستورات زیر را در cmd وارد میکنیم:

sqlplus

نتیجه:

و دستور زیر:

sql sys/ as sysdba

بعد از دستور فوق SQL نمایش داده میشود و این یعنی میتوان دستورات sql را از این طریق نیز وارد نمود.

روش دوم این است که تنها عبارت sqlplus را انتخاب کنیم که در این صورت از ما یوزر و پسورد خواهد خواست

نتیجه:

که از ما یوزر و پسورد میخواهد.

فرض میکنیم که میخواهیم از روش زیر وارد شویم:

sqlplus /as sysdba

چند دستور ادمینی را وارد میکنیم:

1- دستور زیر نشان میدهد که فعلا یک دیتابیس به نام xe داریم (که container ما می باشد):

2- دستور زیر نیزنام دیتابیس ها را به ما نشان میدهد:(این مورد container ما هست)

3- دستور زیر نیز pdb ها را به ما نشان میدهد:

موردEXPDB1 را باید در فایل tnsnames.ora ازآدرس زیر بروزرسانی نماییم:

C:\app\Shahram\product\18.0.0\dbhomeXE\network\admin

یعنی باید مقادیر زیر را در فایل فوق نوشته باشیم (که از مورد اول قابل کپی کردن است):

همچنین قسمت هاست را روی 192.168.200.100 تنظیم میکنیم که مردم از بیرون بتوانند به ما کانکت شوند 🙂

برای تست کردن وصل بودن دیتابیس:

ping 192.168.200.100 

یعنی آی پی سرور را پینگ میکنیم.
تمرین (این مورد را عملا اجرا کنید و خروجی خود را مشاهده کنید)
جواب: ابتدا در cmd عبارت ipconfig را وارد میکنیم تا IP سیستم به ما داده شود. سپس میتوانیم از آن ping بگیریم.

همچنین از طریق تلنت (در صورتی که telnet نصب باشد): ابزاری برای اینکه ببینیم پورت باز است یا نه

telnet 192.168.200.100 1521

در دستور بالا شماره پورت با فاصله نوشته می شود. در صورتی که صحیح باشد وارد یک صفحه سیاه میشود و میتوان با کنترل+ کروشه از آن خارج شد.

شاید مشکل از سمت ما باشد. در این صورت کد زیر را در CMD وارد میکنیم:

tnsping XEPDB1

اگر Ok 0 sec را مشاهده کنیم، یعنی همه چیز درست است.

تمرین: مورد فوق را اجرا کنید و ok 0 sec را مشاهده کنید.
جواب:

مشکل دیگر ممکن است از oracleserver ما باشد که دیر پاسخ میدهد. در هر صورت دستورات فوق برای تست کانکشن مورد استفاده قرار میگیرند.

***

1- روش ورود از طریق CMD

با وارد کردن sqlplus از ما نام username خواسته میشود و نیز پسورد که ما به ترتیبsystemو admin123 را میزنیم و وارد میشویم.

همچنین روش زیر و بدون پسورد:

2- روش ورود از طریق plsql:

یوزر :system
پسورد:admin123
Databese : مطابق شکل. که ابتدای آن آدرس هاست برگرفته از فایل متنی tnsnames.ora است و 1521 آدرس پورت ماست و Ali مربوط بهservice name از فایل متنی tnsnames.ora است.

3-ورود از طریق oracle SQL developer

ابتدا از آیکون سبز مشخص شده زیر گزینه new database connection را انتخاب میکنیم.

سپس در پنجره زیر اطلاعات را به دقت وارد میکنیم:
name : یک نام دلخواه
username : به نام sys as sysdba
رمز: admin123
هاست: localhost
SID همان XE
نام سرویس:KARA

لازم به ذکر است که در فایل متنی tnsnames.ora باید مورد زیر را بیافزاییم:

نمایش نام دیتابیسهای موجود:

PL sql به اوراکل سرور یا اوراکل کلاینت نیاز دارد.
در واقع به فایل هایtnsnames.ora نیاز دارد.

زمانی که نرم افزار pl-sql developer را نصب کرده باشیم از طریق پنجره زیر باید مشخص کنیم که باید به کدام دیتا بیس متصل شویم.

از طریق آدرس زیر میتوانیم به فایل متنی tnsnames.ora دسترسی داشته باشیم.

C:\app\Shahram\product\18.0.0\dbhomeXE\network\admin

اگر فایل متنی مذکور را باز کنیم ، به صورت زیر آن را خواهیم داشت:

از XE میتوانیم کپی بگیریم و تنها آدرس و پورت را تغییر دهیم.

هر چه در فایل متنی tnsnames.ora ساخته باشیم، در این زبانه در دسترس خواهد بود.

برای مثال میتوانیم انتخاب کنیم که به دیتابیس به نام XEpp وصل شویم. در دوقسمت بالا باید یوزر و پسورد را انتخاب کنیم.
قسمت پایین را نیز روی sysdba انتخاب میکنیم. نکته: (میزان دسترسی sys کمتر از system است)

نکته : برخی موارد ممکن است سرویس اوراکل اجرا نشود. در چنین مواقعی میتوان به بخش services رفته و از فعال بودن چند مورد مشخص شده زیر اطمینان حاصل کرد: (بویژه oracle service XE و listener آن)

بعد از اتمام کار نیز برای جلوگیری از مصرفram میتوان از همین بخش موارد مشخص شده را stop کرد.

اگر به درستی به دیتابیس متصل شد باشیم در بالای پنجره عبارت زیر را مشاهد خواهیم کرد:

در سمت چپ شکل بالا، آبجکت ها را مشاهده میکنیم. در این قسمت یوزرها ، اسکیماها و … مشخص هستند. همچنین history مرتبط با کانکشن های ما مشخص است. ما با این قسمت کاری نداریم. زیرا به طور کلی نباید از یوزر سیستمی برای درج یا تغییر اطلاعات استفاده کرد. زیرا باید درخواست کنیم که اسکیمای خاصی برای ما اختصاص دهد.
از کلید طلایی رنگ موجود در تب بالا میتوان نوع کانکشن را تغییر داد.

حال میخواهیم یک یوزر یا اسکیما بسازیم. (اسکیما آن است که یک آبجکت در اختیار دارد. یوزر معمولا فقط به دیتابیس کانکت میشود واز یک اسکیما بهره میبرد.)

1- روش tns names یا استفاده از الیاس ها: استفاده از فایل متنی tnsnames.ora است.

2-روش EZ_connect یا easy connect
در بخش database از پنجره زیر، اگر IP سرور را داشته باشم (192.168.20.100 برای مثال)
سپس دو نقطه میگذاریم و شماره پورت را مینویسیم و
سپس اسلش(/) اسم سرویس و یا اسم pdb را مینویسیم. (مثلاxepdb1)

تمرین: (روش 2 را یکبار توسط خودتان اجرا کنید)

همچنین از طریق CMD و با دستور زیر میتوان به SQL متصل شدک

با دستور زیر میتوان نام و مشخصات pdb ها را مشاهده نمود:

الان در pdbcontainer هستیم. اگر بخواهیم به XEPDB1 برویم باید دستور زیر را وارد کنیم:

حال اگر مجددا PDB SHO بگیریم نتیجه زیر را خواهیم داشت:

حال با دستورEXIT خارج میشویم و میخواهیم با یوزر SYSTEM وارد شویم.

در همین خط باید بیان کنیم که میخواهیم به چه کسی وصل شویم.
بعد از علامت @ نام الیاس را فایل متنی tnsnames انتخاب میکنیم.

تمرین:
کد بالا را اجرا نمایید و به یک یوزر دلخواه متصل شوید.

ساعت 1 دقیقه 7(جلسه 4 بخش 1)

دو نوع یوزر داریم- GLOBAL- LOCAL

میخواهیم یوزر جدید بسازیم:

یوزری به نام datatech با رمز admin123

create user datatech identified by admin123

ممکن است خطای زیر را دریافت کنید:

error: ORA-65096: invalid common user or role name in Oracle database

راه حل(لینک)

بعد از ایجاد user نام آن در فهرست user ها باید مشخص باشد.
راه حل:
کانکشن را عوض کرده و به عنوان system به XEPDB1 وصل شده و سپس دستورزیر را وارد کردیم و دیگر خطایی نداشتیم:

ساخت یوزر بدون ارور:

CREATE USER SHAHRAM_user IDENTIFIED BY tiger; 

اکنون این یوزر در لیست یوزرها وجود دارد. و یوزری دیگری با همین نام نیمتواند تعریف شود.(ارور اشغال بودن خواهیم داشت.)

تمرین-
میخواهیم مجوز دسترسی به اسکیمایhr را به یوزرSHAHRAM بدهیم

میخواهیم پسورد یک یوزر را عوض کنیم:

ALTER USER SHAHRAM_USER IDENTIFIED  by admin321;

اگر بخواهیم یک یوزر را از بلاک بودن خارج کنیم:

ALTER USER HR IDENTIFIED BY HR Account unlock

روش دیگر ساخت یوزر:

این روش برای بعد از زمانی که کد نویسی را یادگرفتیم پیشنهاد میشود.

اطلاعات دلخواه را وارد میکنیم و سپس دکمه پایین سمت راست را میزنیم تا کد برای ما generate شود:

کد :

-- Create the user 
create user reza
  identified by admin123
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT;

تمرین- ساخت جدول

از روی جدول گزینه new را میزنیم و پنجره زیر باز میشود: owner را رویHR و نام را رویtest1 ست میکنیم

در قسمت ستون char 20 را انتخاب میکنیم

تفاوت varchar و char اینکه مورد اول متغیر است. برای بهبود وضعیت حافظه از varchar استفاده کنیم. در واقع با وارد کردنchar20 به ما 20 خانه اختصاص میدهد که هر کدام برای مثال 5 ظرفیت دارند. که قابل تغییر هم نیست. اما با varchar20 تعداد 20 لوکیشن متغیر به ما اختصاص داده میشود که ممکن است 2 یا 3 یا 5 یا … ظرفیت داشته باشند.

تا پایان جلسه 4 بخش اول

جلسه 4 بخش دوم

در مثال زیر کد ملی را برابر با char 10 در نظر میگیریم . زیرا میدانیم که حتما 10 رقم دارد. اما نام و نام خاوادگی را از نوع varchar میگیریم زیرا ممکن است 5 حرف یا 15 حرف باشد. (صرفه جویی در اشغال فضای دیتابیس)

مقدار پیشفرض default. مثلا اگر کسی کد ملی نداشت مقدار 0000000000 برای او ثبت میشود.

گزینه view sql را میزینم و کد برای ما نمایش داده میشود.

- Create table
create table TABLE4011028.sh4011020
(
  id            number,
  st_number     number,
  name          varchar2(20),
  lastname      varchar2(20),
  register_date date,
  code_melli    char(10) default 0000000000
)
;

اختصاص دادن دیتافایل با مقدار سایز مثلا 100 مگابایت:

create tablespace datatech_tbs datafile 
'C:\app\Shahram\product\18.0.0\oradata\XE\XEPDB1\db01.DBF'
size 100m

با افزودن کد زیر میتوان قابلیتی ایجاد کرد به صورت اتومات به ظرفیت آن اضافه گردد(در صورت پر شدن)

حال میتوان در هنگام ساخت یوزر مشخص کرد که tablespace پیشفرض آن کدام باشد:

CREATE USER sh4011028 identified by admin123 default tablespace datatech_tbs;

حال میخواهیم از همه دیتابیس ها بیرون برویم و با یوزر shahram وارد شویم. از session –> disconnect all

با دستور زیر یک یوزر به نام hrr و همین پسورد میسازیم. اما نمیتواند وارد شود. زیرا previlege به آن داده نشده است. انگا هیچی:

create user hrr identified by hrr; 

بنابراین باید به آن grant یا rol بدهیم. دستور زیر دو رول به hrr میدهد. یکی وصل شدن و یکی مجوز ایجاد ابجکت.(البته زمانی میتوانیم این کارها را بکنیم که با یوزر system وارد شده باشیم.)

create user hrr identified by hrr; 
grant connect,resource to hrr

همچنین با راست کلیک بر روی hrr از بخش یوزرها میتوان پسورد آن را ریست کرد.
همچنین از tab باز شده میتوان previledge های متعدد را به آن اعطا نمود.

تا دقیقه 17

اگر با system لاگین باشیم، با راست کلیک روی table نوعی میتوان گزینه view را انتخاب کرد و اطلاعاتی در مورد آن بدست آورد.

همچنین با راست کلیک گزینه های دیگری نیز نمایش داده میشود که هر کدام کاربرد خاصی دارند. برای مثال گزینه DBMS_metadata و سپس گزینه ddl به ما درمودر کد sql منجر به ساخته شدن این talble اطلاعاتی میدهد.

خط اول کد ظاهر شده را باهم مرور میکنیم.

CREATE TABLE “SYSTEM”.”LOGMNRC_SHARD_TS” SHARING=METADATA

که مشخص است که جدولی با creat ساخته شده. عبارت system.log… نشان میدهد که اول اسکیما آمده و سپس ابجکت.

همچنین اگر گزینه query data را انتخاب کنم کدی که بوسیله آن میتوان از آن جدول اطلاعات بدست آورد به ما نمایش داده میشود.

در جدول ظاهر شده میتوان تغییرات را مستقیما از روی جدول انجام داد–> تیک سبز–> commit

اگر گزینه rollback را انتخاب کنیم یعنی پشیمان شده ایم و میخواهیم تراکنش را به حالت قبل برگردانیم.

تا دقیقه 24

ctas به معنای creat table as می باشد. در کد زیر یک کپی از یک جدول موجود ساخته ایم و نام آن را double_1 گذاشته ایم و سپس در خط آخر آن را فراخوانی کرده ایم ببینیم تشکیل شده یا نه.

--ctas
create table double_1 as
select * from AQ$_INTERNET_AGENTS;

select * from double_1;

اگر بخواهیم تنها ساختار جدول را کپی کنیم (کاری با دیتا نداریم) از دستور زیر استفاده میکنیم. در واقع یک شرط غلط میدهیم که هیچ دیتایی کپی نشود و تنها ساختار جدول کپی شود:

create table double_2 as
select * from AQ$_INTERNET_AGENTS where 1=2;

از طریق cmd درون برنامه (new–> command window)میتوانیم اطلاعاتی در مورد جداول ساخته شده بدست آوریم: desc به معنای description می باشد.

تا دقیقه 33

برای ست کردن تنظیمات صفحه نمایش CMD :

set pages 2000 lines 3000

یعنی هر صفحه 2000 خط و هر خط 3000 نقطه ظرفیت داشته باشد.

***

دستور Alter برای تغییر جدول:

با دستور زیر یک مورد حذف و یک مورد اضافه نمودیم. هم گرافیکی و هم با کد این کار قابل اجرا است.

alter table DOUBLE_1 drop column spare1;
alter table DOUBLE_1 add ali number;

اوراکل outocommit نیست.

commit را علاوه بر دکمه گرافیکی با کد حروفی commit نیز قابل اجراست.

نحوه وارد کردن دیتا به جدول EMP

SQL> insert into EMP (c1,c2,...) values (val1,val2,...)

میتوان 100 مورد نوشت و 100 مورد را وارد جدول کرد. همچنین میتوان از روش زیر استفاده کرد:

SQL> insert into EMP
   select * from employee where E_id>80;
commit;

کامیت در خط آخر باید وجود داشته باشد تا اجرا به سرانجام برسد.

همچنین از روش زیر برای تعیین ستون مورد نظر استفاده میشود:

insert into EMP 
select from employee e where t.e_id >80
commit;

با دستور زیر نیز میتوان truncate انجام داد:

truncate table EMP

تفاوت delete و truncate:

The DELETE command is used to delete particular records from a table.

 The TRUNCATE command is used to delete the complete data from the table. It is a DML command. The DELETE command acquires the lock on every deleting record; thus, it requires more locks and resources

تا دقیقه71

f6 کلیدی است که outocomplete میکند.

Update

تمرین: بروز کردن مقدار حقوق کارمند با آیدی شماره 177

update employee w set w.salary=w.salary +  where w.employee_id = 177;

تمرین : افزایش 10 درصدی حقوق کارمندانی که حقوق آنها کمتر از 5000 دلار است:

update emp w set w.salary=w.salary*1.1 where w.salary <=5000;

insert

شکل یا قالب کلی به صورت زیر است

insert into emp(employee_id,firs_name,hire_date) values (v1,v2,v3, ...)

dual

برای انجام محاسبات نوعی زیر از dual استفاده می شود:

select sysdate from dual;
select 1+99 from dual;
select 6900/3 from dual;

تا ساعت 1 و دقیقه 40

PL/SQL

مثال 1

--set serveroutput on;
BEGIN
  dbms_output.put_line('Hello world..');
END;
/

که خروجی در output نمایش داده میشود.

تا پایان جلسه چهارم

جلسه 5- قسمت اول-

قالب اصلی PL/SQL

به شرح زیر است

پایان بخش اول از جلسه 5

جلسه 5 بخش دوم

برای نوشتن PLSQL در محیط گرافیکی به دو مورد مشخص شده در شکل نیازی نداریم اما اگر در محیط cmd یا command بنویسیم باید دو مورد را بنویسیم.

اگر خروجی تنها یک عدد باشد میتوان آن را درون یک ظرف ریخت. در غیر اینصورت نمیتوان:

در شکل زیر خط سوم ارور خواهد داد زیرا جواب ریکوئست بیش از یک مورد است و نمیتوان آن را درون یک متغیر ذخیره نمود.

تا پایان جلسه ششم