Sequence چیست؟

sequence یک شمارنده در اوراکل است که همیشه در حال شمارش است. به مثال زیر توجه کنید:

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 1

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 2

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 3

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 4

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 5

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 6

نکته: SEQUNCE به صورت TRANSACTIONAL نیست.

SQL> ROLLBACK;
Rollback complete.

SQL> SELECT S1.NEXTVAL FROM DUAL;

NEXTVAL
----------
 7

سطل بازیابی در اوراکل (dba_recyclebin)

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

SQL> CONNECT MAHDI;
Connected
Connection created by CONNECT script command disconnected
SQL> DESCRI T9;
DESCRI T9
ERROR: 
------------------------------- 
ERROR: object T9 does not exist

SQL> CREATE TABLE T9(ID NUMBER, NAME VARCHAR2(20), FAMILY VARCHAR2(20));
table T9 created.
SQL> DROP TABLE T9;
table T9 dropped.
SQL> SELECT object_name ,droptime ,original_name ,owner from dba_recyclebin ;
OBJECT_NAME                                        DROPTIME            ORIGINAL_NAME           OWNER
-------------------------------------------------- ------------------- ----------------------- ----------
BIN$uPBAMAcxSEKbICnDjzXV1Q==$0                     2015-05-15:09:46:16 T9                      MAHDI 
BIN$d9S76pNMSmiI/fISqrthNQ==$0                     2015-05-15:09:51:55 T9                      MAHDI

OBJECT_NAME = شماره هر آبجکت چون امکان داره شما چندتا جدول با اسم‌های یکسان drop کرده باشید و این فیلد به ما تفاوت هر آبجکت رو نشون میده.

DROPTIME = زمان حذف آبجکت

ORIGINAL_NAME = اسم اصلی آبجکت

OWNER = مالک آبجکت

لیست تغییرات جدول توسط کاربران

س: لیست تغیراتی که کاربران بر روی جدول T1 داده‌اند را نمایش دهید:

ج: با استفاده از کوئری زیر می‌توانید به این هدف برسید.

SQL> select commit_timestamp , logon_user, XID from flashback_transaction_query
 where table_NAME='T1';

COMMIT_TI LOGON_USER                     XID
--------- ------------------------------ ----------------
12-MAY-15 MAHDI                          02000000B8090000
12-MAY-15 MAHDI                          02000000B8090000
12-MAY-15 MAHDI                          02000000B8090000
12-MAY-15 MAHDI                          08001C00C6090000

گزارش بالا لیست commitهایی که کاربران رو سطح دیتابیس رو جدول t1 انجام داده‌اند.

نکته: این گزارش نمونه‌ای از اطلاعات دیتادیکشنری است. همچنین همانطور که مشاهده می‌کنید این گزارش از flashback_transaction_query گرفته شده است.

XID = شماره پیگیری transaction است.

نمایش لیست فیلدهای جدول flashback_transaction_query

SQL> describ flashback_transaction_query;
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------------

XID                                                 RAW(8)
 START_SCN                                          NUMBER
 START_TIMESTAMP                                    DATE
 COMMIT_SCN                                         NUMBER
 COMMIT_TIMESTAMP                                   DATE
 LOGON_USER                                         VARCHAR2(30)
 UNDO_CHANGE#                                       NUMBER
 OPERATION                                          VARCHAR2(32)
 TABLE_NAME                                         VARCHAR2(256)
 TABLE_OWNER                                        VARCHAR2(32)
 ROW_ID                                             VARCHAR2(19)
 UNDO_SQL                                           VARCHAR2(4000)

نکته: اگر در حالت audit نباشید فیلد undo_sql, row_id اطلاعاتی در خود ندارد.

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

برگشت به یک زمان خاص بدون بک‌آپ - Oracle Flashback

دامیومنت

Setup and Maintenance for Oracle Flashback Database

مقدمه

اوراکل با استفاده از فایلهای redo می‌تواند تا زمانی که فایلهای redo برای داده‌های موردنظر شما دوباره‌نویسی نشده باشند بدون داشتن backup دیتاهای حذف شده شما را برگرداند.

SQL> drop user mahdi cascade;
User dropped.

SQL> create user mahdi identified by qwerty512;
User created.

SQL> grant dba, connect to mahdi;
Grant succeeded.

SQL> connect mahdi
Enter password:
Connected.

SQL> password
Changing password for MAHDI
Old password:
New password:
Retype new password:
Password changed

SQL> create table t1(id number, name varchar2(20));
Table created.

SQL> insert into t1 values(1, 'mohammad');
1 row created.

SQL> insert into t1 values(2, 'mahdi');
1 row created.

SQL> insert into t1 values(3, 'mohsen');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t1;

ID         NAME
---------- --------------------
 1         mohammad
 2         mahdi
 3         mohsen

SQL> delete from t1 where id=2;
1 row deleted.

SQL> commit;
Commit complete.

SQL> select * from t1;
 ID        NAME
---------- --------------------
 1         mohammad
 3         mohsen

SQL> SELECT * FROM t1 AS OF TIMESTAMP TO_TIMESTAMP('2015-05-12 16:55', 'YYYY-MM-
DD HH24:MI:SS');

ID         NAME
---------- --------------------
 1         mohammad
 3         mohsen

SQL> SELECT * FROM t1 AS OF TIMESTAMP TO_TIMESTAMP('2015-05-12 16:50', 'YYYY-MM-
DD HH24:MI:SS');

no rows selected

SQL> SELECT * FROM t1 AS OF TIMESTAMP TO_TIMESTAMP('2015-05-12 16:51', 'YYYY-MM-
DD HH24:MI:SS');

ID         NAME
---------- --------------------
 1         mohammad
 2         mahdi
 3         mohsen

نکته: توجه داشته باشید اگر بخواهید محدودیتی برای flashback نداشته باشید فقط در صورتی که هارد پرسرعت و فضای کافی ذخیره‌سازی در اختیار دارید می‌توانید دیتابیس را در حالت archive قرار دهید. برای قرار دادن دیتابیس در حالت archive می‌توانید به صورت زیر عمل کنید:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.
Total System Global Area 3240239104 bytes
Fixed Size 2407880 bytes
Variable Size 1879048760 bytes
Database Buffers 1342177280 bytes
Redo Buffers 16605184 bytes
Database mounted.

نکته: در موقع flashback می‌توانید از دستوراتی نظیر where نیز استفاده کنید:

SQL> SELECT * FROM mahdi.t1 AS OF TIMESTAMP TO_TIMESTAMP('2015-05-12 16:51', 'YY
YY-MM-DD HH24:MI:SS') WHERE id>0;

ID         NAME
---------- --------------------
 1         mohammad
 2         mahdi
 3         mohsen

ذخیره اطلاعات

برای ذخیره اطلاعات می‌توانید به شکل زیر عمل کنید:

SQL> create table t3(id number, name varchar2(20));
Table created.

SQL> INSERT INTO T3 SELECT * FROM mahdi.t1 AS OF TIMESTAMP TO_TIMESTAMP('2015-05
-12 16:51', 'YYYY-MM-DD HH24:MI:SS');
3 rows created.

SQL> select * from t3;

ID         NAME
---------- --------------------
 1         mohammad
 2         mahdi
 3         mohsen

SQL> commit
 2 ;
Commit complete.

پیداکردن اختلاف بین ۲ جدول

برای اینکار می‌توانید از minus به شکل زیر استفاده کنید:

SQL> select * from t3 minus select * from mahdi.t1;

ID         NAME
---------- --------------------
 2         mahdi

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

س: اگر ساختار جدول تغییر کرد می‌توان با استفاده از flashback اطلاعات را بازیابی کرد؟

ج: خیر، چون فیلدهایی که در اون بازه زمانی در redo موجود بوده‌اند با ساختار مشخصی از جدول ثبت شده‌اند و اگر بخواهیم در یک جدولی که ساختار یکسانی با آنچه ثبت شده است اطلاعات را ذخیره کنیم به مشکل برخواهیم خورد چون اطلاعات دیگر منطبق بر CTL فایل‌ها نیست.

س: وقتی flashback را enable می‌کنیم اجازه میدیم که flashback از redo فایل archive بگیره؟ 

ج: بله

کمی بیشتر درباره 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 از آنها استفاده کرد؟

ج: خیر

جداول بسیار حجیم در 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 پردازش می‌کنه و به همین ترتیب تا پایان پردازش کل جدول ادامه میده.

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

تغییر داده‌ها با استفاده از UPDATE

دستور UPDATE این امکان را به شما می‌دهد تا بتوانید، داده‌های مورد نظرتان را تغییر دهید. برای مثال از جدول PERSON استفاده می‌کنیم.

SELECT *
FROM PERSON;

NAME       FAMILY                AGE  ID_PERSON
---------- ------------ ------------ -----------
MOHAMMAD   GHAFFARI               23          1
MAHDI      GHAFFARI               21          2
MOHSEN     GHAFFARI               19          3
FARZAD     KARKHANI               25          4
EHSAN      JALALI                 23          5

حال با استفاده از دستور UPDATE، افرادی را که نام آن‌ها EHSAN می‌باشد را تغییر، و ALI را جایگزین آن می‌نماییم.

UPDATE PERSON
SET NAME = 'ALI';
WHERE NAME = 'EHSAN';

1 row updated.

جدول PERSON را مشاهده کنید.

دستور DEFAULT

دستور CREATE را در زیر مشاهده کنید. در این دستور مقدار 222 پیش فرض فیلد ID می‌باشد. پیش فرض به معنای آن است که هرگاه مقداری برای فیلد ID تعین نشد مقدار پیش فرض تعریف شده، در فیلد قرار می‌گیرد.

CREATE TABLE D1(ID NUMBER DEFAULT 222);

برای تغییر ساختار جدول به شکل زیر عمل می‌کنیم:

ALTER TABLE D1 ADD NAME VARCHAR2(20);

INSERT INTO D1(NAME) VALUES('MAHDI');

SELECT * FROM D1;
       ID NAME
--------- -------------
       222 MAHDI

INSERT INTO D1(ID, NAME) VALUES(NULL, 'MAHDI');
SELECT * FROM D1;
       ID NAME
--------- -------------
      222 MAHDI
          MAHDI

نکته: توجه کنید حتی اگه ID رو NULL رد کنیم (به هر شکل) مقدار DEFAULT اعمال نمی‌شه و فقط زمانی مقدار DEFAULT اعمال میشه که در دستور INSERT فیلد ما ID حظور نداشته باشد.

زبان SQL - قسمت دوم

ساخت جدول

دستور زیر جدولی به اسم PERSON با فیلدهای ID, NAME, AGE با نوع‌های مشخص ایجاد می‌کند(ID, AGE اطلاعات عددی نگه‌می‌دارن و NAME اطلاعات رشته‌ای، همچنین این جدول در یوزر MGHAFFARI ساخته شده است):

SQL> CREATE TABLE PERSON(ID NUMBER, NAME VARCHAR2(20), AGE NUMBER);

Table created.

SQL> show user;
USER is "MGHAFFARI"

ورود اطلاعات

برای ورود اطلاعات از دستور INSERT به شکل زیر استفاده می‌کنیم:

SQL> INSERT INTO PERSON(ID, NAME, AGE) VALUES(1, 'MAHDI', 21);

1 row created.

SQL> INSERT INTO PERSON(ID, NAME, AGE) VALUES(2, 'EHSAN', 22);

1 row created.

SQL> INSERT INTO PERSON(ID, NAME, AGE) VALUES(3, 'FARZAD', 25);

1 row created.

برای نمایش اطلاعات از دستور SELECT استفاده می‌کنیم:

SQL> SELECT * FROM PERSON;

ID NAME AGE
---------- -------------------- ----------
 1 MAHDI 21
 1 EHSAN 22
 1 MAHDI 21
 2 EHSAN 22
 3 FARZAD 25

نکته: حتما بعد از INSERT اطلاعات عمل COMMIT را انجام دهید وگرنه TRANSACTION بعد از بستن محیط یا هر اتفاقی ROLLBACK میشه.

نکته: برای دستورات CREATE نیازی به COMMIT نیست و فقط برای دستوراتی که مستقیماً با DATA طرف هستند و درج یا آپدیتی انجام می‌دهند باید عمل COMMIT صورت گیرد.

نکته: MGHAFFARI تا قبل از ایجاد جدول و ورود داده فقط یوزر بود ولی بعد از ایجاد جدول و ورود داده SCHEMA شد.

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

SELECT * FROM SCHEMA_NAME.OBJECT_NAME;

به عنوان مثال برای SELECT PERSON از یوزر SYSTEM به صورت زیر عمل می‌کنیم:

SQL> connect system;
Enter password: 
Connected.
SQL> SELECT * FROM MGHAFFARI.PERSON;
ID NAME AGE
---------- -------------------- ----------
 1 MAHDI 21
 1 EHSAN 22
 1 MAHDI 21
 2 EHSAN 22
 3 FARZAD 25