یکی از سوالاتی که برای بعضی از دوستان پیش می آید این است که آیا ترتیب تعریف ستون های یک جدول تاثیری بر عملکرد کوئری ها دارد؟

برای پاسخ به این سوال، به نحوه ذخیره سازی سطرهای جداول در یک block توجه فرمایید که به شکل زیر است:

H: Header

Ln: Length of Column n

Dn: Data of Column n

این نکات را در نظر داشته باشید:

• خواندن block به شکل سریال است، پس برای خوانده شدن اطلاعات ستون دوم، به ناچار ستون اول هم خوانده می شود و .
• برای مقادیر null چیزی ذخیره نمی شود.

پس با توجه به موارد بالا، در طراحی جدول این نکات را در نظر داشته باشید:

1. ستون هایی را که خیلی استفاده می شوند را در ابتدا تعریف کنید. (به علت سریالی خوانده شدن block). تاثیر این نکته وقتی بیشتر مشخص می شود که جدولی داریم با ستون های زیاد و ما فقط چند ستون آن را به تناوب نیاز داریم.
2. تنها ستون هایی را در دستور select بیاورید که واقعاً مورد نیاز هستند.
3. ستون های یک جدول که می توانند مقادیر null داشته باشند را در انتهای ستون های جدول تعریف کنید.

حالا نگاهی هم به هزینه اجرای دستورات می اندازیم. برای این کار جدول test_cost_tbl را با پنج ستون می سازیم:
 

drop table test_cost_tbl purge;

CREATE TABLE test_cost_tbl(
        col_1 NUMBER,
        col_2 NUMBER,
        col_3 NUMBER,
        col_4 NUMBER,
        col_5 NUMBER);

سپس یک سطر در این جدول insert و آمار جدول را نیز بروز رسانی میکنیم:

INSERT INTO test_cost_tbl VALUES (1, 2, 3, 4, 5);

commit;

begin
  dbms_stats.gather_table_stats(user,'test_cost_tbl');
end;

برای خواندن هر کدام از ستون های جدول به صورت مجزا و تهیه Plan اجرای آنها از دستورات زیر استفاده می کنیم:

EXPLAIN PLAN SET STATEMENT_ID 'col_1' FOR SELECT col_1 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_2' FOR SELECT col_2 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_3' FOR SELECT col_3 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_4' FOR SELECT col_4 FROM test_cost_tbl;
EXPLAIN PLAN SET STATEMENT_ID 'col_5' FOR SELECT col_5 FROM test_cost_tbl;

با بررسی مقادیر ثبت شده در جدول plan_table می توانیم مشاهده کنیم که هزینه CPU خواندن از ستون ها با هم متفاوت است (20 عدد به ازا هر ستون به هزینه CPU اضافه می شود) اما هزینه I/O آنها ثابت مانده است. علت ثابت ماندن هزینه I/O احتمالاً این است که کل رکورد در یک بلاک ذخیره شده اند و برای خواندن آن نیاز به I/O اضافی نیست.

SELECT statement_id, cpu_cost as total_cpu_cost,cpu_cost-lag(cpu_cost) OVER (ORDER BY statement_id) AS cpu_cost_diff, io_cost
FROM plan_table
WHERE id = 0
ORDER BY statement_id;

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

پی نوشت:

این مطلب و مثال را چند سال پیش از یک وبلاگ خواندم و مستند کردم. متاسفانه آن وبلاگ را پیدا نکردم تا ارجاع بدهم که در این مورد عذرخواهی میکنم.

موفق باشید

خطای ORA-01950: no privileges on tablespace در زمان اجرای Export Datapump

به هنگام اجرای دستور expdp خطای زیر در log مشاهده شد:

ORA-31642: the following SQL statement failes:

Begin SYS”.”DBMS_RULE_EXP_RULES” SCHEMA_CALLOUT …

ORA-01950: no privileges on tablespace ‘SYSAUX’

این خطا در حالی رخ می داد که عملیات expdp با استفاده از کاربر sys اجرا می شد و این خطا غیر عادی به نظر می رسید. این خطا تنها زمانی رخ می داد که full export تهیه می شد. به نظر می رسید که این خطا مربوط به بخشی از دیتابیس (یک یا چند اسکیما) است، اما پیدا کردن قسمتی که موجب بروز مشکل شده با توجه به میزان log قابل تشخیص نبود، پس لازم بود تا مشخص شود دقیقاً چه دستوری موجب بروز این خطا می شود. برای این کار ابتدا دستور زیر اجرا شد:

$ sqlplus / as sysdba

SQL> alter system set events '1950 trace name ERRORSTACK level 3';

سپس دستور expdp مجدد اجرا شد. پس از آنکه در log خطای مذکور مشاهده شد، job مربوط به expdp را متوقف کردم و با دستور زیر هم trace را غیر فعال کردم:

SQL> alter system set events '1950 trace name ERRORSTACK off';

در مسیر فایل های trace فایل مربوطه را شناسایی شد و سپس در این فایل دنبال خطای ora-01950 گشتم. پس از این خطا، دستور SQL که موجب بروز خطا شده است مشخص است:

insert into dmuser.my_table …

پس متوجه شدم که کاربر dmuser دسترسی لازم را روی sysaux tablespace ندارد که با اعطا دسترسی مشکل برطرف شد.

نحوه برطرف کردن خطای ora-02437 cannot validate primary key violated

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

@?/rdbms/admin/utlexpt1.sql

با اجرای دستور بالا، جدول EXCEPTIONS ایجاد می شود. (در اسکیمای sys)

**************************************************************

SQL> alter table MY_INFORMATIONS add constraint MY_TAB_PK primary key (COL1, COL2, COL3, COL4) exceptions into SYS.EXCEPTIONS;

select * from exceptions
 

حالا مشخصات سطرهایی که مشکل ایجاد کرده اند در جدول EXCEPTIONS وجود دارد.

**************************************************************

SQL> create table copy_cons as select * from MY_INFORMATIONS where rowid in (select ROW_ID from exceptions);

SQL> SELECT * FROM copy_cons
 

در این جدول اطلاعات کامل از سطرهایی که مشکل ایجاد کرده اند وجود دارد.

**************************************************************

SQL> delete MY_INFORMATIONS where rowid in (select ROW_ID from exceptions)

COMMIT;
 

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

**************************************************************

حالا می توانید با استفاده از دستور زیر کلید مورد نظرتان را بسازید:

SQL> alter table MY_INFORMATIONS add constraint MY_TAB_PK primary key (COL1, COL2, COL3, COL4);
 

**************************************************************

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

 
SQL> TRUNCATE TABLE EXCEPTIONS;

SQL> drop table copy_cons;

فایل های patch را دانلود کنید و آنها را به دایرکتوری مورد نظر بر روی سرور منتقل کنید.

به روز رسانی OPatch:

$ $ORACLE_HOME/OPatch/opatch version

$ cd /u01/source/patch/OPatch

$ cp p6880880_122010_Linux-x86-64.zip $ORACLE_HOME

$ cd $ORACLE_HOME

$ mv OPatch/ OPatch_old/

$ unzip p6880880_122010_Linux-x86-64.zip

$ rm -rf p6880880_122010_Linux-x86-64.zip

$ $ORACLE_HOME/OPatch/opatch version

متوقف کردن سرویس دیتابیس و سرویس های وابسته:

$ lsnrctl stop

$ sqlplus / as sysdba

SQL> shutdown immediate;

$ cd /u01/source/patch/PSU

$ unzip p28163133_122010_Linux-x86-64.zip

$ cd 28163133

$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

$ $ORACLE_HOME/OPatch/opatch apply

به سوالاتی که پرسیده می شود پاسخ مثبت دهید و منتظر باشید تا patch مورد نظر اعمال شود و در نهایت باید چیزی شبیه به خروجی زیر دریافت کنید:

Patch 28163133 successfully applied.

Log file location: /u01/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2018-12-02_15-41-48PM_1.log

OPatch succeeded.

برای اطمینان از اعمال patch:

$ $ORACLE_HOME/OPatch/opatch lsinventory

$ sqlplus / as sysdba

SQL> startup upgrade

برای اعمال pacth در دیتابیس:

$ cd $ORACLE_HOME/OPatch

$ ./datapatch -verbose

SQL> @?/rdbms/admin/utlrp.sql

SQL> shutdown immediate

SQL> startup

$ lsnrctl start

SQL> alter system register;

SQL> select * from dba_registry_sqlpatch

order by action_time desc;

چنانچه محیط شما multitenant است، سعی کنید PDB ها را باز کنید:

SQL> show pdbs

   CON_ID   CON_NAME                    OPEN MODE RESTRICTED

----------      ------------------------------   ---------- ----------

        2 PDB$SEED                         READ ONLY NO

        3 HRPDB                              READ WRITE YES

        4 HRPDB2                            READ WRITE YES

و با استفاده از دستور زیر patch را اعمال می کنیم:

$ ./datapatch -verbose -pdbs PDB\$SEED,CDB\$ROOT,HRPDB,HRPDB2

SQL> shutdown immediate

SQL> startup

SQL> show pdbs

   CON_ID   CON_NAME                    OPEN MODE RESTRICTED

----------      ------------------------------   ---------- ----------

        2 PDB$SEED                         READ ONLY NO

        3 HRPDB                              READ WRITE NO

        4 HRPDB2                            READ WRITE NO

یکی از ویژگی های جالب در نسخه 12.2، قابلیت Lockdown profile است. فرض کنید قرار است تا یک PDB را از دیتابیس اصلی clone بگیرید و به طور کامل به تیم توسعه تحویل دهید. پس لازم است تا دسترسی DBA را هم به تیم توسعه بدهید. این دسترسی ممکن است منجر به مشکلاتی در سیستم شود، مثلاً ممکن است با استفاده از utl_file فایل های زیادی در سیستم عامل تولید کنند، از قابلیتی استفاده کنند که لایسنس آن را نداریم و .

صورت مسئله این است که چگونه می توانیم مطمئن شویم که کارهای تیم توسعه برای ما دردسری ایجاد نخواهد کرد؟

پاسخ این سوال، استفاده از قابلیت Lockdown Profile است که در نسخه 12.2 معرفی شد.

حالا به بررسی این قابلیت می پردازیم:

در ابتدا به CDB متصل می شویم و یک profile برای local DBA (کاربر DBA تیم توسعه در PDB خودشان) می سازیم:

SQL> create lockdown profile My_Profile;

Lockdown Profile created.

SQL> select profile_name,status from dba_lockdown_profiles;

PROFILE_NAME                   STATUS

------------------------------      -------

MY_PROFILE                         EMPTY

PRIVATE_DBAAS                  EMPTY

PUBLIC_DBAAS                    EMPTY

SAAS                                      EMPTY

غیرفعال کردن قابلیت های دیتابیس

فرض کنید می خواهیم قابلیت های partitioning و advanced queuing را برای پروفایل ساخته شده غیرفعال کنیم پس از دستور زیر استفاده می کنیم:

SQL> alter lockdown profile MY_PROFILE disable option = ('PARTITIONING','DATABASE QUEUING');

Lockdown Profile altered.

SQL> select RULE_TYPE,RULE,STATUS from dba_lockdown_profiles where profile_name = 'MY_PROFILE';  

RULE_TYPE            RULE             STATUS

-------------------- --------------------                -------

OPTION               DATABASE QUEUING DISABLE

OPTION               PARTITIONING       DISABLE

حالا می خواهیم این پروفایل را بر PDB مورد نظر (در این مثال HRPDB) اعمال کنیم:

SQL> alter session set container=hrpdb;

Session altered.

SQL> show parameter pdb_lockdown

NAME                                 TYPE VALUE

------------------------------------ ----------- ------------------------------

pdb_lockdown                         string

SQL> alter system set pdb_lockdown = 'MY_PROFILE';

System altered.

با اتصال به کاربر admin مربوط به PDB بررسی می کنیم تا مطمئن شویم که مثلاً قابلیت پارتیشن بندی جداول غیرفعال است:

$ sqlplus PDBADMIN@hrpdb

SQL> create table test_tbl(id number) partition by range (id) interval (10) (partition p1 values less than (10), partition p2 values less than (20));

create table test_tbl(id number) partition by range (id) interval (10) (partition p1 values less than (10), partition p2 values less than (20))

*

ERROR at line 1:

ORA-00439: feature not enabled: Partitioning

همچنین با استفاده از عبارت های ALL و EXCEPT میتوانیم تمام قابلیت ها را فعال یا غیر فعال کنیم یا برای فعال سازی یک یا چند قابلیت استثنا قائل شویم. به عنوان مثال با دستور زیر تمام قابلیت ها به غیر از Advanced Queuing را غیر فعال می کنیم:

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter lockdown profile MY_PROFILE enable option all except = ('DATABASE QUEUING');

Lockdown Profile altered.

اگر دوباره برای ساخت جدول پارتشن شده در PDB اقدام کنیم دستور بدون خطا اجرا خواهد شد، زیرا تمام قابلیت ها به غیر از Advanced Queuing فعال شدند:

SQL> create table test_tbl(id number) partition by range (id) interval (10) (partition p1 values less than (10), partition p2 values less than (20));

Table created.

غیر فعال کردن امکان اجرای دستورات قدرتمند

فرض کنید که لازم است برنامه نویسان بتوانند در PDB خودشان امکان kill session داشته باشند. پس لازم است تا مجوز alter system را به آنها بدهیم ولی می دانیم که این دستور برای خراب کردن دیتابیس کافی است! پس می خواهیم تا تنها اجازه kill session بدهیم ولی دیگر دستورات alter system را غیر مجاز کنیم، پس با اتصال به CDB دستورات زیر را اجرا می کنیم:

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter lockdown profile MY_PROFILE disable statement = ('ALTER SYSTEM') clause all except = ('KILL SESSION');

Lockdown Profile altered.

با کاربر pdbadmin به hrpdb متصل می شویم و تست می کنیم:

$ sqlplus PDBADMIN@hrpdb

SQL> select sid,serial# from v$session where username = 'HR';

      SID SERIAL#

---------- ----------

      278  31669

SQL> alter system kill session '278,31669' immediate;

System altered.

SQL> alter system set optimizer_dynamic_sampling=8;

alter system set optimizer_dynamic_sampling=8

*

ERROR at line 1:

ORA-01031: insufficient privileges

همچنین ممکن است بخواهیم تا pdbadmin تنها بتواند پارامترهای cursure_sharing  و optimizer_dynamic_sampling را تغییر بدهد. برای این کار می توانیم از دستور زیر استفاده کنیم:

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter lockdown profile MY_PROFILE disable statement = ('ALTER SYSTEM') clause = ('SET');

Lockdown Profile altered.

SQL> alter lockdown profile MY_PROFILE enable statement = ('ALTER SYSTEM') clause = ('SET') option = ('cursor_sharing','optimizer_dynamic_sampling');

Lockdown Profile altered.

تست می کنیم:

$ sqlplus PDBADMIN@hrpdb

SQL> alter system set open_cursors=500;

alter system set open_cursors=500

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> alter system set session_cached_cursors=100;

alter system set session_cached_cursors=100

                                         *

ERROR at line 1:

ORA-02096: specified initialization parameter is not modifiable with this

option

SQL> alter system set optimizer_dynamic_sampling=8;

System altered.

SQL> alter system set cursor_sharing = 'EXACT';

System altered.

غیرفعال کردن امکان اجرای پکیج های دیتابیس

فرض کنید می خوایم امکان اجرای پکیج قدرتمند utl_file را در PDB غیر ممکن کنیم، پس از دستور زیر استفاده می کنیم:

SQL> alter lockdown profile MY_PROFILE disable feature = ('UTL_FILE');

استفاده از Block Change Tracking

همانطور که می دانید به هنگام تهیه Incremental backup، کلیه block های یک دیتافایل خوانده می شود و چنانچه تغییر کرده باشد از آن backup گرفته می شود. برای بهینه کردن این فرایند، می توان فایلی را در نظر گرفت که آدرس block هایی که تغییر کرده اند را در خودش نگه می دارد و به هنگام backup گرفتن فقط به سراغ این block ها می رود، بنابراین سرعت backup گرفتن افزایش قابل ملاحظه ای خواهد داشت. البته در مواقعی که full backup می گیریم، این کار کاربردی ندارد و فقط برای مواقعی است که incremental backup می گیریم.

برای بررسی اینکه این مدل backup به درد ما می خورد یا نه می توان از دستور زیر استفاده کرد:

select d.RECID,d.SET_COUNT,d.FILE#,d.CREATION_TIME,d.INCREMENTAL_LEVEL,d.BLOCKS as "Backed up Blocks",d.BLOCKS_READ "Blocks That Read for Backup" from v$backup_datafile d

where trunc(d.COMPLETION_TIME) = trunc (sysdate );

چنانچه در خروجی دستور بالا، مقدار blocks_read (نشان دهنده تعداد بلاک هایی که خوانده شده اند) خیلی بیشتر از blocks (تعداد بلاک هایی که از آنها Backup گرفته شده است) باشد، یعنی راه اندازی block change tracking به عملکرد سیستم ها کمک بسیاری خواهد کرد.

برای راه اندازی می توانید از دستورات زیر استفاده نمایید:

alter database enable block change tracking using file '/u01/oracle/block_change_tracking_dir/block_change_tracking.bct';

چنانچه از OMF استفاده می کنید نیازی به ذکر کردن مسیر فایل نیست و خود به خود در مسیر مشخص شده در پارامتر db_create_file_dest و در پوشه changetracking ساخته می شود:

alter database enable block change tracking;

برای غیر فعال سازی block change tracking از دستور زیر استفاده می شود:

alter database disable block chnage tracking;

برای مشاهده اطلاعات مربوط به block change tracking می توانید از ویو زیر استفاده نمایید:

select * from V$BLOCK_CHANGE_TRACKING;

منظور از duplex backup، تهیه نسخه پشتیبان در 2 جای مختلف است (2 نسخه یکسان)

پس برای نگهداری datafile ها و archivelog ها از تنظیمات زیر استفاده می کنیم:

RMAN> configure datafile backup copies for device type disk to 2;

RMAN> configure archivelog backup copies for device type disk to 2;

نکته:

• توجه کنید که مسیر داخل FRA نمی توانید بدهید.

• با انجام duplex سرعت پشتیبان گیری کاهش پیدا می کند.

• انجام duplex برای backup های از نوع image copy فقط بر روی Tape امکان پذیر است. (روی دیسک نمی توان image copy backup ها را duplex کرد)

روش اول :

در این روش به تعداد مورد نظر channel تعریف می کنیم و هر مسیر مورد نظر را برای آن مشخص می کنیم:

RMAN> run{

allocate channel ch1 device type disk format ‘/u01/oracle/first_bkp_dir/%U.bkp’;

allocate channel ch2 device type disk format ‘/u02/oracle/second_bkp_dir/%U.bkp’;

backup database plus archivelog;

}

روش دوم:

RMAN> backup database format ‘/u01/oracle/first_bkp_dir/%U.bkp’, ‘/u02/oracle/second_bkp_dir/%U.bkp’;

روش سوم:

RMAN> configure channel device type disk format '+data/first_bkp_dir/%U','+data/second_bkp_dir/%U';

RMAN> backup database;

روش چهارم:

RMAN> run{

set backup copies=2;

backup database plus archivelog format ‘/u01/oracle/first_bkp_dir/%U.bkp’, ‘/u02/oracle/second_bkp_dir/%U.bkp’;

}

چنانچه بخواهیم از datafile 1 در دو مسیر و از datafile 3 فقط در یک مسیر backup بگیریم از یکی از روش های زیر می توانیم استفاده کنیم:

روش اول:

RMAN> run{

allocate channel ch1 device type disk maxpiecesize 100m;

set backup copies=2;

backup as backupset datafile 1 format ‘+data/first_dir/%U’,’+data/second_dir/%U’;

set backup copies=1;

backup as backupset datafile 3 format ‘+data/first_dir/%U’;

}

روش دوم:

RMAN> run{

allocate channel ch1 device type disk maxpiecesize 100m;

backup as backupset copies 2 datafile 1 format ‘+data/first_dir/%U’,’+data/second_dir/%U’;

backup as backupset datafile 3 format ‘+data/first_dir/%U’;

}

نصب پایتون 3 بر روی Oracle Linux

1. به سایت ius.io بروید و rpm های زیر را دانلود کنید:

epel-release-latest-7.noarch.rpm

ius-release-1.0-15.ius.el7.noarch.rpm

2. تنظیمات yum را بر روی سرور خود انجام دهید.

3. فایل های rpm دریافت شده را نصب کنید:

rpm -ivh epel-release-latest-7.noarch.rpm

rpm -ivh ius-release-1.0-15.ius.el7.noarch.rpm

4. در فایل /etc/yum.conf مقدار keepcache را به 1 تغییر دهید تا پکیج هایی که دانلود خواهند شد را نگهداری کند.

5. با دستور زیر به دنبال پکیج نصب پایتون می گردیم: (توجه داشته باشید که باید به اینترنت متصل باشیم)

$ yum search python

6. با دستور زیر عمل نصب را انجام می دهیم:

$ yum install python36u

$ yum install python36u-pip

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

/var/cache/yum/x86_64/7Server

8. با استفاده از دستور زیر فایل های rpm این مسیر را پیدا کنید و در یک دایرکتوری ذخیره کنید:

find -name '*.rpm'

9. در سرور مقصد، فایل های rpm پیدا شده در مرحله قبل را نصب کنید:

rpm -ivh *

به اطلاع میرسانم

موسسه آموزشی آکادمی عصر اوراکل با مدیریت آموزشی اینجانب حمیدرضا قاسمی از ابتدای سال 1398 آغاز بکار کرده است. علاقه مندان میتوانند جهت اطلاع از دوره های آموزشی آکادمی عصر اوراکل به وبسایت

AsreOracle.com مراجعه و یا با شماره تلفن 86084023 تماس حاصل نمایند.

دوره های قابل ارائه در

آکادمی عصر اوراکل به شرح زیر میباشد:

توسعه برنامه های کاربردی

پایگاه داده Oracle 18c

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

علم داده (داده کاوی و بیگ دیتا)

سیستم عامل لینوکس

این قابلیت در نسخه 12.1.0.2 (Enterprise) اضافه شد. این قابلیت در واقع یک property برای جداول است و مشخص می کند که سطرها با چه ترتیبی و چگونه باید کنار هم (به صورت فیزیکی) بر اساس مقادیر یک یا چند ستون دسته بندی و ذحیره شوند.

(توجه کنید که این قابلیت با Table Cluster تفاوت دارد)

با این کار اطلاعاتی که به طور متناوب آنها را با هم می خواهیم در بلاک های پشت سر هم ذخیره می شوند و بنابراین باعث افزایش چشمگیر سرعت اجرای دستورات خواهیم بود.

ادامه مطلب

سلام

میخواهیم یک جدول را از یک اسکیما به اسکیمای دیگر کپی کنیم. مثلاً فرض کنید که جدول source_tbl  را می خواهیم به جدول با نام target_tbl در اسکیمای دیگر منتقل کنیم. می دانیم که این کار به روش های متفاوتی قابل انجام است:

• استفاده از دستور create table as select
• استفاده از ابزار data pump
• استفاده از قابلیت exchange

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

ادامه مطلب