ایجاد پروفایل

مستند اوراکل

CREATE PROFILE

مقدمه

نکته: role دلالت بر قدرت یک کاربر دارد در حالی که profile دلالت بر ضعف یک کاربر دارد. شما با دادن role های مختلف به کاربر آن کاربر را قوی می‌کنید و با ایجاد profile برای کاربر آن کاربر را محدود خواهید کرد.

profile بر ۲ دسته تقسیم می‌شود. یعنی ۲ نوع تنظیم(محدودیت) را می‌تواند در داخل خودش داشته باشد.

1. تنظیمات سخت‌افزاری و physical
2. تنظیمات امنیتی

نکته: توی اوراکل ما با profile می‌توانیم محدود کنیم یک کاربر را که چند بلاک از cpu‌ را توی سرور ما اشغال کند.

نکته: با ایجاد Profile به این نکته پی خواهید برد که چقدر دیتابیس در اپلیکیشن تاثیر گذار است.

ایجاد Synonym

Synonym نامی مستغار برای موجودیتی در بانک‌اطلاعاتی است. هر موجودیت در بانک‌اطلاعاتی اوراکل می‌تواند یک مترادف داشته باشد.

public synonym

Create public synonym sssss for mahdi.t1;

در این شرایط کل کاربران بانک‌اطلاعاتی می‌توانند به sssss دسترسی داشته باشند.

private synonym

Create synonym sssss for mahdi.t1;

 فقط خود کاربر ایجاد کننده این نام متعارف می‌تواند آن را مشاهده کند.

drop synonym

Drop public synonym;

در drop حتماً باید نوع synonym مشخص بشود.

نکته: اگر نوع synonym مشخص نکردد به طور پیش‌فرض private است.

Drop synonym;

شکل کلی

CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM
 [ schema. ]synonym 
 FOR [ schema. ]object [ @ dblink ] ;

جاوا کارها

مترادف‌ها در جاوا بسیار کاربرد داردند، در JPA یا Hibernate چون ما به یک کاربر کانکت می‌شویم وقتی بخواهیم map رو ایجاد کنیم می‌توانیم map رو روی مترادف انجام دهیم.

داکیومنت اوراکل

CREATE SYNONYM

کمی بیشتر درباره View

مزیت‌های View

1 . می‌توان پرس و جوی پیچیده پیاده‌سازی کرد. 2 . ارتباط مستقیم کاربران را با جداول حذف کرد.

ورود داده در View

SQL> CREATE TABLE TBL1(ID NUMBER, NAME VARCHAR2(20));
Table created.

SQL> CREATE VIEW V1 AS SELECT * FROM TBL1;
View created.

SQL> INSERT INTO V1(ID, NAME) VALUES(1, 'MAHDI');
1 row created.

SQL> COMMIT;
Commit complete.

س: اگر view ما ترکیبی باشه به چه صورت باید insert را انجام دادا؟

ج: با pl/sql می‌توان insert را انجام داد، چون insert ما باید قاعده view رو رعایت کنه و روی ضرب دکارتی تاثیر نذاره

س: اگر در دستورمان Aggregate functions داشته باشیم(توابعی مانند Sum و Countو...) می‌توان با view از آنها استفاده کرد؟

ج: خیر

ساختار کامل ایجاد کاربر همراه با تعریف Default Tablespace

به مستند زیر توجه کنید:

CREATE USER

CREATE USER sidney 
    IDENTIFIED BY out_standing1 
    DEFAULT TABLESPACE example 
    QUOTA 10M ON example 
    TEMPORARY TABLESPACE temp
    QUOTA 5M ON system 
    PROFILE app_user 
    PASSWORD EXPIRE;

این ساختار کلی و کامل تعریف یوزر در اوراکل است. همونطور که می‌بیندی هر یوزر موقع ایجاد یک DEFAULT TABLESPACE دارد که اگر مقداردهی نشود با DEFAULT TABLESPACE پیش‌فرض خود اوراکل مقداردهی می‌شود.

پس اگر table در این یوزر تعریف بشود و این یوزر schema یک جدول باشد (مالک یک جدول باشد) و آن جدول به یک tablespace اتصال داده نشده باشد اوراکل از DEFAULT TABLESPACE استفاده می‌کند.

تمامی یوزرها در اوراکل به صورت پیش‌فرض DEFAULT TABLESPACE اشون tablespace یوزر 01 است که محدودیتی ندارد.  

نکته: اگر خودتان به صورت دستی جدولی را به tablespace ای اتصال دهید دیگر جدول وارد default tablespace نمی‌شود و وارد آن tablespace موردنظرتان می‌شود.

نکته: اگر بخواهیم temporary tablespaceها را زمانی که پر شده خالی کنیم در وقتی که به هر دلیلی توسط دیتابیس به طور خودکار clear نمی‌شوند

باید به صورت دستی temporary tablespace را drop کنیم. 

temporary tablespace زمانی توسط اوراکل استفاده میشه که tablespace اصلی ما به مشکل بخوره (آفلاین بشه،‌مشکلی براش پیش بیاد)

temporary tablespace یه واحدیه بعد از Redo و قبل از DataFile که زمانی که مشکلی برای DataFile شما پیش بیاید temporary tablespace سریع جایگزین خواهد شد.

چند مثال از نحوه ایجاد کاربر:

Create user mahdi identified by 123 password expire;

در این صورت کاربر بعد از اولین ارتباط با بانک باید دستور password را جهت تغیر password وارد نماید.

Create user mahdi identified by 123 password expire account lock;

در این صورت بعد از تغییر پسورد توسط کاربر اکانت کاربر قفل می‌شود.

همچنین می‌توانید به مستند زیر مراجعه کنید:

Oracle Users

Index ها

index چیست؟

با یک مثال index را توضیح می‌دهم، فرض کنید یک کتاب ۴ هزار صفحه‌ای را باز کرده‌اید، وقتی می‌خواهید به مطلبی رجوع کنید قطعاً شما تمام کتاب را نمی‌خوانید تا به مطلب مورد نظر برسید. در این حالت شما به فهرست رجوع می‌کنید و به صفحه مورد نظر می‌روید. دقیقاً تعریف index همان فهرست در کتاب است.

در حقیقت یعنی از روی اون شاخصی به صفحه موردنظر می‌رسید. 

اوراکل روش‌های مختلفی را برای indexing به کار می‌برد که در ادامه با آنها آشنا خواهیم شد.

در چه شرایطی index گذاری می‌کنیم؟

کوئری ما کمتر از ۵ درصد کل رکورد را بر گرداند.

زمانی index گذاری می‌کنیم که عموم کوئری‌هایی که از اون جدول می‌گیرید کمتر از ۵ درصد از کل رکوردها رو برگرداند. توی این شرایط ما index گذاری انجام می‌دهیم. 

یادتان باشد در شرایطی اگر اصلاً index گذاری نشود بهتر است.

مثال: جدولی حاوی ۱۰۰ رکورد است کوئری وارد شده برای گزارش‌گیری توسط ما ۵۰ رکورد آن را بیشتر برنمی‌گرداند، آیا index گذاری باید انجام شود؟

ج: خیر - چون با ایندکس‌گذاری روی همچین جدولی برای همچین کوئری‌هایی سرعت دیتابیس ما به شدت افت پیدا خواهد کرد.

جداول بسیار حجیم در Very Large Databaseها (انواع پارتیشن‌بندی)

به index گذاری که روی سطح لایه فیزیکی دیتابیس باشه اصطلاحاً پارتیشن‌بندی گفته می‌شود.

partition by range

به مثال زیر دقت کنید، در این مثال جدولی با پارتیشن بر مبنای فیلدهای id, age ساخته‌ایم:

SQL> create table sal (id number,age number,name varchar2(20))
partition by range(id,age)
(partition s1 values less than (10,20) tablespace ts1,
partition s2 values less than(20,30) tablespace ts2,
partition s3 VALUES LESS THAN (MAXVALUE) tablespace ts3);

در ادامه گفته‌ایم که مقدارهای ورودی در پارتیشن s1 باید کمتر از 10,20 باشند و این پارتیشن در tablespace ts1 قرار دارد(یعنی idهای کمتر از ۱۰ و ageهای کمتر از ۲۰)

در ادامه پارتیشن s2 را داریم که مقدارهای کمتر از 20,30 را در خود می‌گیرد و در tablespace ts2 قرار دارد.

و در ادامه پارتیشن s3 را داریم که اگر رکوردی وارد شد که تو بازه‌ی پارتیشن‌بندی ما نبود اون رکورد که حالا معلوم نیست بزرگه یا کوچیکه وارد tablespace ts3 شود.

نکته: اسم پارتیشن‌ها زیاد اهمیتی برای ما ندارند.

نکته: اگر عملیات update انجام شود و مقدار عوض شود جابه‌جایی در پارتیشن نیز رخ خواهد داد.

نکته: تو partition by range بهتره که تمام فیلدها مشخص شوند، یعنی چیز نامعلومی که کاربر بخواد وارد کنه و به مشکل بخوره نباشه

Partitioning

Partitioning تو ۹۰ درصد مواقع به درد ما نمی‌خوره ولی زمانی که روی (Very Large Database (VLDB کار می‌کنیم که یک جدول ممکنه ۱ گیگ باشه اونوقت باید از تکه تکه کردن یا همون Partitioning استفاده کنیم.

چرا از Partitioning استفاده می‌کنیم؟

یه جدول ۱۰۰ گیگی رو در نظر بگیرید برای کش جدولی که به صورت فیزیکی ۱۰۰ گیگه تقریباً حداقل ۱۶ گیگ رم نیاز داریم، اما وقتی ما ۲ گیگ بیشتر رم نداریم باید چی کار کنیم؟ تو این شرایط اوراکل نمی‌تونه کش رو یکجا انجام بده پس به صورت پیش‌فرض اوراکل کش را تکه تکه انجام میده یعنی قسمتی را وارد SGA می‌کند و پردازش می‌کند و بعد از پردازش قسمتی دیگر را وارد SGA می‌کند و پردازش می‌کند. حتی اگر where هم گذاشته باشید این اتفاق بازهم می‌افته کل جدول رو تیکه تیکه میاره تو فضای SGA و توسط PGA پردازش می‌کنه و به همین ترتیب تا پایان پردازش کل جدول ادامه میده.

توی ۹۰ درصد مواقع شاید من لازم داشته باشم به این روش عمل کنم:

DataDictionaryهای مربوط به TableSpaceها

دیتادیکشنری جداولی هستند که به صورت پیش‌فرض در اوراکل وجود دارند و ساختار دیتابیس رو نگه‌داری می‌کنند. اونایی که اولشون DBA است جدول هستند و اونایی که اولشون V است VIEW هستند.

یه وضعیتی روی TableSpaceها وجود داره که شما می‌تونید به وسیله دیتا دیکشنری اسم همه TableSpaceهاتون رو پیدا کنید:

نکته: با دستور DESCRIB می‌توانید ساختار یک جدول را ببینید. وقتی می‌خوایم ساختار جداول دیتادیکشنری رو ببینیم از این دستور پر کاربرد استفاده می‌کنیم.

SQL> DESCRIB DBA_TABLESPACES;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------

 TABLESPACE_NAME                            NOT NULL  VARCHAR2(30)
 BLOCK_SIZE                                 NOT NULL  NUMBER
 INITIAL_EXTENT                                       NUMBER
 NEXT_EXTENT                                          NUMBER
 MIN_EXTENTS                                NOT NULL  NUMBER
 MAX_EXTENTS                                          NUMBER
 MAX_SIZE                                             NUMBER
 PCT_INCREASE                                         NUMBER
 MIN_EXTLEN                                           NUMBER
 STATUS                                               VARCHAR2(9)
 CONTENTS                                             VARCHAR2(9)
 LOGGING                                              VARCHAR2(9)
 FORCE_LOGGING                                        VARCHAR2(3)
 EXTENT_MANAGEMENT                                    VARCHAR2(10)
 ALLOCATION_TYPE                                      VARCHAR2(9)
 PLUGGED_IN                                           VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                             VARCHAR2(6)
 DEF_TAB_COMPRESSION                                  VARCHAR2(8)
 RETENTION                                            VARCHAR2(11)
 BIGFILE                                              VARCHAR2(3)
 PREDICATE_EVALUATION                                 VARCHAR2(7)
 ENCRYPTED                                            VARCHAR2(3)
 COMPRESS_FOR                                         VARCHAR2(30)

TableSpaceهای حجیم

هر دیتافایل در اوراکل تا حجم 8 اگزابایت معادل 8 میلیون ترابایت می‌تواند مقدار‌دهی شود. و توجه داشته باشید که یک دیتابیس از یک مخزن می‌تواند به طور نامحدود دیتافایل داشته باشد.

برای ایجاد TableSpaceهای حجیم بهتره در اوراکل از دستور زیر استفاده کنید، چون مکانیزم index گذاری DataFileهای حجیم فرق میکنه و دیگه snapshot بیس نیست:

Create bigfile tablespace ts datafile ‘d:\root\a.dbf’ size 100g;

نکته: بعد از زدن دستور ایجاد دیتافایل با سایز زیاد مدت زمانی طول می‌کشد تا initialize انجام شود. اندازه ۱۰۰ گیگ Gap رو ایجاد می‌کنه ته فایل رو می‌بنده.

نکته: اگر تمام فضای پارتیشن شما با دیتافایل پر شود نه بخشی از آن تمام آن بخش به دیتافایل اختصاص می‌یابد و دیگر قابل recovery نیز نیست.

نکته: معمولاً از حجم ۱ تا ۱۰ گیگ به بالا وقتی شما دارید دیتافایل ایجاد می‌کنید تا سقف ۸ اگزابایت tablespace ای که ایجاد می‌کنید بهتره bigfile‌ باشه

m = megabyte
g = gigabyte
t = terabyte
p = petabyte
e = exabyte

س: ۵ اگزابایت چند بایت است؟

ج: 5.0 × 10¹⁵ kilobytes به سرچ زیر در گوگل دقت کنید:

https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=5+exabyte+convert+to+kilobyte

فقط خواندنی و فقط نوشتنی کردن یک TableSpace

برای اینکه یک TableSpace را به حالت read only ببریم از دستور زیر استفاده می‌کنیم، در این حالت از جداولی که روی TableSpace ما هستند فقط میشه select گرفت و نمیشه ورود داده کرد:

نکته: وقتی می‌خواهیم در یک بازه زمانی اصلاً ورود و یا تغییر داده روی فیلدها انجام نشود، از این حالت استفاده می‌کنیم.

alter tablespace ts read only;

برای اینکه TableSpace را به حالت اول برگردونیم از دستور زیر استفاده می‌کنیم، در این حالت هم اجازه خواندن را می‌دهیم و هم اجازه نوشتن را:

alter tablespace ts read write;