В управлении таблицами Oracle может помочь набор представлений словаря данных. Наиболее важное из них — представление DBA_TABLES - дает информацию о владельце, количестве строк, имени табличного пространства, информации о пространстве и множестве прочих деталей обо всех таблицах базы данных. В листинге ниже приведен пример запроса.
SQL> SELECT tablespace_name, table_name, num_rows FROM dba_tables WHERE owner='HR'; TABLESPACE_NAME TABLE_NAME NUM_ROWS --------------- -------------- --------- EXAMPLE DEPARTMENTS 27 EXAMPLE EMPLOYEES 107 EXAMPLE JOBS 19 EXAMPLE JOB_HISTORY 10 EXAMPLE LOCATIONS 23 EXAMPLE REGIONS 4 6 rows selected. SQL>
Для нахождения детальной информации о секционированных таблицах служит представление DBA_TAB_PARTITIONS. В листинге ниже показан пример представления, содержащего информацию о секционированной таблице из предыдущего примера.
TABLE_NAME PARTITION_NAME SUBPARTITION_COUNT ----------- ----------------- ------------------ SALES_DATA SALES_Q1 0 SALES_DATA SALES_Q2 0 SALES_DATA SALES_Q3 0 SALES_DATA SALES_Q4 0 SALES_HASH SYS_P3161 0 SALES_HASH SYS_P3162 0 SALES_HASH SYS_P3163 0 SALES_HASH SYS_P3164 0 SALES_LIST NORTHEAST_SALES 0 SALES_LIST SOUTHWEST_SALES 0 SALES_LIST PACIFICWEST_SALES 0 SALES_LIST SOUTHEAST_SALES 0 SCOUT_GEAR P1 8 SCOUT_GEAR P2 8 SCOUT_GEAR P3 8 SCOUT_GEAR P4 8 QUARTERLY_REGIONAL_SALES Q1_2009 4 QUARTERLY_REGIONAL_SALES Q2_2009 4 QUARTERLY_REGIONAL_SALES Q3_2009 4 QUARTERLY_REGIONAL_SALES Q4_2009 4 20 rows selected. SQL>
Еще одно полезное представление словаря данных, дающее массу информации о столбцах таблицы — DBA_TAB_COLUMNS. В листинге ниже приведен простой запрос, использующий это представление.
SQL> SELECT column_name, data_type, nullable FROM dba_tab_columns WHERE owner='HR' AND table_name = 'EMPLOYEES'; COLUMN_NAME DATA_TYPE NULLABLE -------------- ---------- --------- EMPLOYEE_ID NUMBER N FIRST_NAME VARCHAR2 Y LAST_NAME VARCHAR2 N EMAIL VARCHAR2 N PHONE_NUMBER VARCHAR2 Y HIRE_DATE DATE N JOB_ID VARCHAR2 N SALARY NUMBER Y 8 rows selected. SQL>
Разумеется, получить подобного рода информацию легко и с помощью команды DESCRIBE, пример применения которой представлен в листинге ниже.
SQL> DESCRIBE new_employees Name Null? Type --------------- ----------- ------------ EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME NOT NULL VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) SQL>
Извлечение объекта DDL с помощью пакета DBMS_METADATA
Часто требуется пересоздать таблицу или создать похожую таблицу в другой базе данных, и было бы неплохо иметь DDL-операторы для удобного обращения к исходной таблице. Если вы используете инструмент от независимого поставщика, такой как SQL Navigator от Quest Software,то все, что нужно сделать — это щелкнуть на нескольких кнопках, и необходимые операторы DDL отображаются на экране.
Но какие команды можно применить для получения оператора CREATE TABLE, который создал таблицу? Эту информацию можно получить из представлений DBA_TABLES и DBA_TAB_COLUMNS,но для этого придется писать довольно длинные операторы SQL. Чтобы быстро получить необходимые DDL-операторы для таблиц и индексов, в качестве альтернативы можно воспользоваться поставляемым Oracle пакетом DBMS_METADATA.
Давайте для примера получим DDL-оператор для таблицы employee с помощью этого пакета. Ниже показан вывод, полученный после обращения к пакету.
SQL> CONNECT hr/hr
Connected.SQL> SET LONG 100000
SQL> SELECT dbms_metadata.get_ddl('TABLE','EMPLOYEE') from dual;
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE')
---------------------------------------------------------------------------
CREATE TABLE "HR"."EMPLOYEES"
("EMPLOYEE_ID" NUMBER(6,0),
"FIRST_NAME" VARCHAR2(20),
"LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
"HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
"SALARY" NUMBER(8,2),
"COMMISSION_PCT" NUMBER(2,2),
"MANAGER_ID" NUMBER(6,0),
"DEPARTMENT_ID" NUMBER(4,0),
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE NOVALIDATE,
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"EXAMPLE" ENABLE,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
"EXAMPLE" ENABLE,
CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE NOVALIDATE,
DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES')
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "EXAMPLE"
SQL>
На заметку! Вывод процедуры get_ddl пакета DBMS_METADATA собирает текст DDL в длинном (long) формате. Если в сеансе SQL*Plus не установлена переменная LONG, то вы можете и не увидеть полный текст оператора DDL.
Это наиболее элегантный способ получения DDL для таблиц и индексов с использованием SQL*Plus. Если нужны операторы DDL для объектов базы данных, следует применять пакет DBMS_METADATA. Разумеется, извлечь все типы DDL для объектов базы данных можно с помощью OEM Database Control - описание.
