Навіть якщо ви системний адміністратор або розробник, коли у вашому середовищі запущено Oracle, ви можете зіткнутися з ситуацією, коли вам доведеться виконувати деякі основні завдання DBA.
Одним із таких завдань є експорт даних із бази даних Oracle. Використовуючи утиліту exp від Oracle, дуже легко вивести дані з бази даних.
У цьому підручнику ми пояснимо все, що вам потрібно знати, щоб ефективно використовувати команду exp для експорту даних із вашої бази даних Oracle, зокрема таке:
Підключіться до БД в утиліті exp
Експорт повної бази даних
Експортуйте одну або кілька конкретних схем/користувачів
Експортуйте одну або кілька конкретних таблиць
Експортуйте всі об’єкти в табличному просторі
Реєстрація повідомлень експорту
Керування назвою файлу вихідного дампа
Експортувати лише вибрані рядки з таблиці
Контроль обмежень, грантів, тригерів та індексів
Як працює стиснення під час експорту
Використання символів підстановки для експорту кількох таблиць
тощо
Є три методи підключення за допомогою утиліти exp для експорту даних з Oracle. Перші три приклади пояснюють ці три різні методи. Використовуйте будь-який із них залежно від конкретної ситуації.
1. Інтерактивна команда експорту зі значеннями за замовчуванням
За замовчуванням, коли ви вводите команду «exp» і натискаєте клавішу Enter, ви входите в інтерактивний режим, у якому вам буде запропоновано ввести різні значення перед експортом даних таблиці Oracle.
Спочатку він запитає ім’я користувача та пароль схеми Oracle. Це ім’я користувача може бути назвою схеми, яку ви плануєте експортувати, або ім’ям користувача, який має привілей DBA експортувати об’єкти інших користувачів.
$ exp
Username: hradmin
Password:
Далі він запитає вас про наступне:
Export file: expdat.dmp >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
User to be exported: (RETURN to quit) > hradmin
User to be exported: (RETURN to quit) >
У наведеному вище запиті:
За замовчуванням він використовуватиме expdata.dmp як вихідний файл, де зберігатимуться всі експортовані об’єкти.
За замовчуванням він експортує всю схему, як показано у «(2)U» стандартному виборі вище. Щоб експортувати всю базу даних, введіть 1. Щоб експортувати окремі таблиці, введіть 3.
Оскільки ми вибрали параметр за замовчуванням «(2)U» для користувачів, ви також повинні вказати ім’я користувача Oracle, яке потрібно експортувати. У цьому прикладі ми експортуємо всі об’єкти, які належать імені користувача (схемі) hradmin.
Нарешті, він покаже наступний вихід, який вказує на те, що він виконує експорт.
. about to export HRADMIN's tables via Conventional Path ...
. . exporting table BENEFITS 420454 rows exported
. . exporting table EXPENSE_DETAILS 347982 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
..
Export terminated successfully without warnings.
Як ви бачите нижче, створено такий файл експорту.
$ ls -l expdat.dmp
-rw-r--r-- 1 oracle oinstall 40820736 Jan 31 14:21 expdat.dmp
2. Виконайте експорт із командного рядка (неінтерактивний)
Замість інтерактивного введення значень ви можете вказати їх усі в командному рядку. Це дуже корисно, коли ви хочете створити сценарій команди експорту та зробити резервні копії зі сценарію оболонки або фонового завдання cron
exp userid=hradmin/mypassword tables=benefits indexes=n
Вам не потрібно вказувати параметр, якщо ви не змінюєте його значення за замовчуванням. Наприклад, вам не потрібно вказувати «grants=y», тому що за замовчуванням буде експортовано гранти.
У наведеному вище прикладі ми вказали «indexes=n», оскільки за замовчуванням індекси буде експортовано. Але в цьому прикладі ми не хочемо, щоб індекси експортувалися.
Також зауважте, що дані, які ви експортуєте за допомогою команди exp, можна імпортувати назад до бази даних Oracle за допомогою команди imp. Але якщо ви хочете завантажити дані в систему Oracle з плоского текстового файлу, вам потрібно буде використати команду oracle sqlldr для завантаження даних.
3. Експортуйте за допомогою файлу параметрів для вхідних значень
Якщо ви неодноразово вказуєте ті самі параметри для своєї команди exp, ви можете використовувати файл параметрів замість повторного введення їх із командного рядка.
Наприклад, створіть файл параметрів під назвою myexp.conf, як показано нижче.
$ vi myexp.conf
tables=benefits
grants=n
triggers=n
Далі вкажіть цей файл параметрів у параметрі PARFILE команди exp, як показано нижче. Це експортуватиме таблиці з бази даних Oracle на основі параметра, зазначеного у файлі myexp.conf.
exp hradmin/mypassword PARFILE=myexp.conf
4. Змініть назву файлу дампа
Як ми зазначали раніше, за замовчуванням експортований файл завжди має назву expdat.dmp. Ви можете змінити це за допомогою параметра «файл», як показано нижче.
У цьому прикладі таблицю benefits буде експортовано до файлу benefits.dmp.
exp userid=hradmin/mypassword tables=benefits file=benefits.dmp
$ ls -l *dmp
-rw-r--r-- 1 oracle oinstall 20414464 Jan 31 14:50 benefits.dmp
5. Запишіть повідомлення у файл журналу
Коли ви експортуєте повну базу даних або виконуєте команду exp зі сценарію, ви хочете зберегти вихідні дані команди exp у файл журналу, щоб пізніше перевірити, чи команда exp була успішно виконана.
Використовуйте параметр «log=», як показано нижче, щоб указати файл журналу. У цьому прикладі журнал експорту (як повідомлення про успіх, так і про помилку) зберігатиметься у файлі myexp.log.
exp userid=hradmin/mypassword tables=benefits log=myexp.log file=benefits.dmp
6. Експорт повної бази даних
Якщо ви хочете експортувати всі схеми, усіх користувачів і всі об’єкти у своїй базі даних, скористайтеся режимом повного експорту бази даних, вказавши «full=y», як показано нижче.
Користувач, який експортує повну базу даних, повинен мати роль EXP_FULL_DATABASE для виконання цієї операції.
Це експортує все з вашої бази даних Oracle.
$ exp userid=hradmin/mypassword full=y
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
...
Щоб виконати повний експорт бази даних, ви повинні мати привілей DBA або принаймні роль EXP_FULL_DATABASE. Якщо ні, ви отримаєте таке повідомлення про помилку EXP-00023.
$ exp sales/mypassword full=y
Export: Release 11.2.0.1.0 - Production on Sun Jan 31 11:12:51 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00023: must be a DBA to do Full Database or Tablespace export
Крім того, якщо ви помилково розпочали повний експорт величезної бази даних, ви можете скасувати його, натиснувши Ctrl-C. У цьому випадку відобразиться таке повідомлення про помилку та завершиться експорт.
$ exp userid=hradmin/mypassword full=y
About to export the entire database ...
..
EXP-00008: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully
Примітка. Якщо ваша база даних дуже велика, рекомендованим способом резервного копіювання повної бази даних є використання утиліти резервного копіювання Oracle RMAN
7. Експортуйте всі об’єкти з певної схеми або користувача
У режимі користувача експорту ви можете експортувати об’єкти, які належать певному користувачеві (або схемі), вказавши параметр «owner=», як показано нижче.
У цьому прикладі буде експортовано всі об’єкти, які належать користувачу hradmin. Якщо у вас є привілей DBA, ви також можете експортувати об’єкти, які належать іншому користувачу.
$ exp userid=hradmin/mypassword owner=hradmin
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HRADMIN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HRADMIN
About to export HRADMIN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HRADMIN's tables via Conventional Path ...
. . exporting table BENEFITS 420454 rows exported
. . exporting table EXPENSE_DETAILS 347982 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
Якщо ви спробуєте експортувати інших користувачів, але не маєте прав DBA, ви отримаєте повідомлення про помилку EXP-00032, як показано нижче.
У цьому прикладі john не має права DBA експортувати об’єкти hradmin.
$ exp john/mypassword owner=hradmin
EXP-00032: Non-DBAs may not export other users
EXP-00000: Export terminated unsuccessfully
8. Експорт кількох користувачів разом
Якщо ви хочете експортувати кілька схем, укажіть усі ці імена користувачів у параметрі «owner», розділивши їх комами, як показано нижче.
У цьому прикладі буде експортовано всі об’єкти, які належать до схеми HRADMIN і SALES.
$ exp userid=hradmin/mypassword owner=hradmin,sales
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HRADMIN
. exporting foreign function library names for user SALES
. exporting PUBLIC type synonyms
..
. exporting private type synonyms
. exporting object type definitions for user HRADMIN
. exporting object type definitions for user SALES
About to export HRADMIN's objects ...
. exporting database links
. exporting sequence numbers
..
About to export SALES's objects ...
. exporting database links
. exporting sequence numbers
..
Export terminated successfully without warnings.
9. Експортуйте одну або кілька конкретних таблиць
Щоб експортувати певну таблицю, скористайтеся параметром «tables=», як показано нижче. У цьому прикладі буде експортовано лише таблицю benefits.
exp userid=hradmin/mypassword tables=benefits
Щоб експортувати кілька таблиць одночасно, укажіть список імен таблиць у параметрі «таблиці», розділивши їх комами, як показано нижче.
exp userid=hradmin/mypassword tables=benefits,expense_details
Деякі версії команди exp можуть вимагати від вас перерахувати кілька таблиць усередині ( ), як показано нижче.
exp userid=hradmin/mypassword tables="(benefits,expense_details)"
Щоб експортувати таблиці, які належать до іншої схеми (якщо у вас є доступ до неї), ви можете вказати назву схеми перед назвою таблиці в такому форматі: tables=schema_name.table_name.
У цьому прикладі користувач hradmin експортує таблицю monthly_estimate, яка належить користувачеві sales.
$ exp userid=hradmin/mypassword tables=sales.monthly_estimate
Export: Release 11.2.0.1.0 - Production on Sun Jan 31 14:06:42 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
..
About to export specified tables via Conventional Path ...
Current user changed to SALES
. . exporting table MONTHLY_ESTIMATE 4931 rows exported
Export terminated successfully with warnings.
Якщо ви реалізували розділення, ви також можете вказати певний розділ або підрозділ, вказавши в такому форматі: tables=schema_name.table_name:partition_name
Наприклад, нижче буде експортовано лише розділ «california» таблиці «monthly_estimate», який належить користувачу sales.
exp userid=hradmin/mypassword tables=sales.monthly_estimate:california
10. Дамп усіх об’єктів у певному табличному просторі
Замість того, щоб вказувати користувача чи таблицю, ви також можете вказати ім’я табличного простору за допомогою параметра “tablespaces”, як показано нижче.
У цьому прикладі буде експортовано всі об’єкти, які знаходяться в табличному просторі MIDWEST_SALES.
$ exp hradmin/mypassword TABLESPACES=MIDWEST_SALES
About to export selected tablespaces ...
For tablespace MIDWEST_SALES ...
. exporting cluster definitions
. exporting table definitions
. . exporting table ACTIVATE_CLIENTS 860 rows exported
. . exporting table ACTIVE_PIPELINE 799 rows exported
..
11. Експортуйте лише вибрані рядки з таблиці
Замість того, щоб експортувати всі рядки з таблиці, ви також можете написати умову where для таблиці, і експорт видасть лише ті рядки, які відповідають заданому пункту where для цієї конкретної таблиці.
Наприклад, у наступному прикладі таблиця переваг має стовпець під назвою v_status. Ця команда exp експортує лише ті рядки, які містять значення «INVALID» у стовпці v_status.
$ exp hradmin/mypassword TABLES=benefits query=\"where v_status=\'INVALID\'\"
..
About to export specified tables via Conventional Path ...
. . exporting table BENEFITS 20783 rows exported
Як видно з наведених вище виводів, незважаючи на те, що таблиця BENEFITS містить 420454 рядки, було експортовано лише 20783, оскільки лише ті з них відповідали заданій умові where.
12. Виключіть тригери та індекси під час експорту
За замовчуванням тригери та індекси будуть експортовані.
Якщо ви не хочете, щоб тригери експортувалися, використовуйте «triggers=n», як показано нижче
exp john/mypassword owner=hradmin triggers=n
Якщо ви не хочете, щоб індекси експортувалися, використовуйте “indexes=n”, як показано нижче.
exp john/mypassword owner=hradmin indexes=n
На замітку: якщо у вас є якась синтаксична помилка в параметрі, ви отримаєте повідомлення про помилку EXP-00019 і LRM-00108, як показано нижче.
$ exp sales/mypassword full-y
LRM-00108: invalid positional parameter value 'full-y'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
13. Виключіть обмеження та гранти під час експорту
За замовчуванням усі обмеження таблиці та дозволи буде експортовано.
Якщо ви не хочете, щоб обмеження таблиці експортувалися, використовуйте «constraints=n», як показано нижче.
exp john/mypassword owner=hradmin constraints=n
Якщо ви не хочете, щоб дозволи об’єкта експортувалися, використовуйте «grants=n», як показано нижче.
exp john/mypassword owner=hradmin grants=n
Щодо грантів, про які слід пам’ятати: коли ви вказуєте «owner=hradmin» і без «grants=n», за замовчуванням буде експортовано лише дозволи, надані власником таблиці, а не дозволи, надані власником таблиці. хтось інший.
Але, коли ви виконуєте повний експорт бази даних, використовуючи «full=y», експортуються всі гранти певної таблиці, незалежно від того, хто створив грант.
14. Експорт кількох таблиць за допомогою символів підстановки
Подібно до того, що ви робите в команді SELECT, ви можете використовувати % як символи підстановки в деяких параметрах команди exp.
У наступному прикладі буде експортовано всі таблиці, які починаються з HR.
exp userid=hradmin/mypassword tables=HR%
У наступному прикладі буде експортовано всі таблиці, які закінчуються на STATUS.
exp userid=hradmin/mypassword tables=%STATUS
У наступному прикладі буде експортовано всі таблиці, які містять DEV будь-де в назві таблиці.
exp userid=hradmin/mypassword tables=%DEV%
З іншого боку, під час експорту ви можете побачити таке повідомлення про помилку EXP-00091. Якщо статистичні дані вашої таблиці старі та застарілі, ви отримаєте це повідомлення про помилку. Ви можете спокійно ігнорувати це повідомлення.
exp userid=hradmin/mypassword tables=HR%
..
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
15. Виключити дані таблиці (рядки) і стиснення
За замовчуванням команда exp експортує всі рядки таблиць. Якщо ви не бажаєте експортувати рядки, а хочете лише визначення таблиці, тоді вкажіть rows=n, як показано нижче.
$ exp hradmin/mypassword TABLES=benefits rows=n
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table BENEFITS
Export terminated successfully without warnings.
Незважаючи на те, що таблиця BENEFITS містить 420454 рядки, як ви бачите у вихідних даних вище, у ній не зазначено «420454 рядки експортовано». Натомість чітко сказано, що «дані таблиці (рядки) не експортуватимуться».
Що стосується стиснення, за замовчуванням команда exp виконає стиснення. Якщо ви не хочете стискати, використовуйте «compress=n», як показано нижче.
exp john/mypassword owner=hradmin compress=n
Дуже важливо розуміти цей прапор. Це не стискає експортований файл *.dmp.
За замовчуванням, коли для компресії встановлено значення y, команда exp позначатиме дані таблиці, які потрібно консолідувати; тобто коли ви виконуєте імпорт із файлу дампа, дані таблиці будуть консолідовані в один початковий екстент. Якщо ваші розміри розширення більші, але дані таблиці менші за розмір розширення, команда imp усе одно виділить початковий екстент.
Але, якщо для компресії встановлено значення n, команда exp використовуватиме параметр зберігання, пов’язаний із таблицею; тобто буде використано початковий екстент, наступний екстент та інші параметри зберігання.
Під час імпорту даних в утиліті imp немає параметра розпакування. Отже, будьте обережні та вирішіть, перш ніж виконувати сам експорт, чи хочете ви стиснути=y чи стиснути=n залежно від вашої конкретної ситуації.
Коментарі