Наш эксперт определяет операционную систему, "убыстряет" работу приложений, объясняет особенности работы с NLS-параметрами и написание чисел по буквам.
Вопрос. Я как-то видел на вашем сайте, как кто-то показывал, как определить операционную систему, в которой работает утилита SQL*Plus; однако теперь я не могу найти эту информацию. У меня есть SQL-скрипт, который я использую для создания других SQL-скриптов. Я хочу расширить этот скрипт так, чтобы при его вызове на UNIX-платформе, на которой работает утилита SQL*Plus, он создавал бы как SQL-скрипт, так и скрипт командной оболочки (shell script).
Ответ. Я думаю, вы на сайте Ask Tom видели, как определить операционную систему, в которой работает сервер базы данных. Это можно сделать с помощью функции DBMS_UTILITY.PORT_STRING:
SQL> begin 2 dbms_output.put_line( 3 dbms_utility.port_string ); 4 end; 5 / Linuxi386/Linux-2.0.34-8.1.0
Это говорит вам не о том, в какой операционной системе работает ваш клиент SQL*Plus, а о том, в какой операционной системе работает сам сервер базы данных (они могут отличаться). Однако вам могут помочь два столбца представления V$SESSION - PROGRAM и PROCESS. Столбец PROGRAM в общем показывает имя программы клиента, которая соединена с сервером базы данных (но это имя может легко вводить в заблуждение, если файлы копируются с другими именами), а столбец PROCESS показывает идентификатор процесса клиента, соединенного с сервером базы данных - идентификатор процесса из самой клиентской машины. Оба этих столбца помогут вам выяснить имя клиентской операционной системы, в которой работает утилита SQL*Plus. Выполним следующий запрос:
SQL> select program, process. 2 from v$session 3 where sid = 4 (select sid 5 from v$mystat 6 where rownum = 1 7 ) 8 / PROGRAM PROCESS ------------ -------------- sqlplus.exe 704:416
В данном случае это - клиент Windows SQL*Plus, это подтверждается наличием ".exe" в имени программы, а также двоеточием (:) в идентификаторе процесса. Если же для подключения к этому же серверу базы данных я использую UNIX-клиента, я увижу что-то похожее на это:
SQL> select program, process . . . 8 / PROGRAM PROCESS ------------------------- -------------- sqlplus@host(TNS V1-V3) 10227
В имени программы отсутствует расширение ".exe", поскольку оно не используется в среде UNIX, а в идентификаторе процесса клиента отсутствует двоеточие, как это было у Windows-клиента.
Исходное интерактивное обсуждение и развитие это темы см. на сайте asktom.oracle.com/~tkyte/DetermineOS.html. Благодарю всех интерактивных участников за хорошие идеи.
"Убыстряем" работу приложений
Вопрос. У нас есть приложение, которое создает пользователей и определяет для них приватные синонимы. Иногда администратор системы безопасности выполняет множественные удаления пользователей, которым больше не нужен доступ к базе данных. Для удаления пользователя, у которого имеется около тысячи приватных синонимов может потребоваться около двух минут. Я неудовлетворен таким слишком медленным удалением вышедших из употребления учетных записей. Есть ли у вас какие-то предложения по повышению производительности выполнения оператора DROP USER, не считая перевода системы на использование публичных синонимов?
Ответ. Все дело в восприятии. Всякий раз когда я имею долго работающий процесс, я думаю, как его выполнять в фоновом режиме, чтобы "несчастливый" конечный пользователь никогда не ждал его завершения. Если конечный пользователь не должен ждать завершения процесса, он будет казаться ему сработавшим мгновенно.
Итак, будем выполнять долго работающий процесс в фоновом режиме, и конечный пользователь подумает: "Класс, в самом деле он работает быстро"! Я рекомендую выполнять оператор DROP USER имя_пользователя CASCADE следующим образом:
1. ALTER USER имя_пользователя LOCK; (Учетная запись блокируется, так что достигается "цель обеспечения безопасности".) 2. dbms_job.submit( l_job, 'execute immediate ''drop user a cascade'';' ); 3. commit;
Пользователь сразу же получает сообщение, что все в порядке. Блокирование учетной записи запрещает ей доступ к базе данных, а фактическое удаление схемы пользователя (на него может потребоваться какое-то время) начнется в фоновом режиме вскоре после выполнения оператора COMMIT (конечный пользователь не должен ожидать завершения удаления).
Я прибегаю к этому способу всегда, когда появляются предположительно долго выполняющиеся задачи - скрытие реальной работы, благодаря фоновому режиму, и немедленное продолжение работы конечных пользователей, так что они думают, приложение работает намного быстрее, чем это есть на самом деле.
Не работает параметр NLS_DATE_FORMAT
Вопрос. Почему параметр NLS_DATE_FORMAT (формат даты) в моем файле init.ora иногда не работает? Я устанавливаю его, но в моих приложениях формат даты по умолчанию не устанавливается правильно.
Ответ. Если в среде клиента установлены какие-либо NLS-параметры, они всегда перекрывают NLS-параметры на стороне сервера. Например, если клиент установит параметр NLS_LANG, все NLS-параметры на стороне сервера будут игнорироваться. Сервер будет использовать значения, указанные клиентом, и значения по умолчанию для всех других NLS-параметров, игнорируя установки в файле init.ora.
Обычно это происходит в Windows-клиентах. При инсталляции таких клиентов в реестре по умолчанию устанавливается параметр NLS_LANG. Решить вашу проблему можно одним из следующих способов:
- установить в реестре на клиенте параметр NLS_DATE_FORMAT;
- вставить в ваше приложение оператор ALTER SESSION SET nls_date_format=ваш_формат, выполняемый непосредственно после подключения к серверу;
- использовать триггер AFTER LOGON, похожий на показанный ниже.
Лично я предпочитаю второй способ. Если ваше приложение зависит от конкретного формата даты, устанавливаемого по умолчанию, то его следует запрашивать явно. Причина: если вы в одной системе базы данных инсталлируете два приложения с конфликтующим форматами даты, вы не сможете их использовать без явной установки форматов дат. Лучше всего, чтобы приложения не зависели от конкретных установок форматов по умолчанию в файле init.ora.
create or replace trigger
data_logon_trigger
after logon
ON DATABASE
begin
execute immediate
'alter session
set nls_date_format =
''your format here'' ';
end;
/
Написание чисел по буквам
Вопрос. Я пытаюсь писать числа по буквам. То есть, я хочу чтобы число 123 печаталось как one hundred twenty-three. Есть ли для этого соответствующие функции?
Ответ. Верите или нет, почти все это есть. Есть формат дат 'Jsp', который позволяет писать по буквам юлианское представление даты:
SQL> select to_char(sysdate,'J'), 2 to_char(sysdate,'Jsp') 3 from dual; TO_CHAR ---------------- TO_CHAR(SYSDATE,'JSP') ------------------------------------------ 2453812 Two Million Four Hundred Fifty-Three Thousand Eight Hundred Twelve
Это работает и для чисел в пределах диапазона юлианских дат, иначе вы получите сообщение об ошибке:
ERROR at line 1: ORA-01854: julian date must be between 1 and 5373484
Немного творчества и я могу расширить этот диапазон (если число 5 373 484 недостаточно большое). Функция PL/SQL на листинге 1 показывает, как вы может сделать это же. Я предлагаю ознакомиться с интерактивным обсуждением этой темы на сайте asktom.oracle.com/~tkyte/SpellNumber.html, там же вы можете увидеть ее развитие, включая вклад участников в написание чисел по буквам на других языках.
| ЛИСТИНГ 1: функция PL/SQL для написания чисел по буквам. |
create or replace
function spell_number( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' thousand ', ' million ',
' billion ', ' trillion ',
' quadrillion ', ' quintillion ',
' sextillion ', ' septillion ',
' octillion ', ' nonillion ',
' decillion ', ' undecillion ',
' duodecillion ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
begin
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( to_number(substr(l_num, length(l_num)-2, 3)) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' ) // l_str(i) // l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
return l_return;
end;
/ |
Составление отчетов о свободном пространстве в базе данных
Вопрос. Мне нужно в утилите SQL*Plus выводить отчеты, которые для каждого табличного пространства показывают свободное пространство. Есть ли у вас работающий запрос, который умеет это делать?
Ответ. Мне приходилось делать это. В основном, мне нужно написать запрос, который показывает свободное пространство для каждого табличного пространства (группируя представление DBA_FREE_SPACE на уровне табличных пространств) и соединить его с запросом, который показывает выделенное пространство в каждом табличном пространстве (включая временные табличные пространства).
Проблема в том, что в представлении DBA_FREE_SPACE свободное пространство показывается на уровне экстентов, а в представлениях DBA_DATA_FILES и DBA_TEMP_FILES показывается выделенное пространство в файлах данных табличных пространств. Перед объединением этих представлений мне нужно сгруппировать их данные на уровне табличных пространств. Для этого хорошо подходят вложенные представления, поэтому я буду использовать их в запросе, показанном на листинге 2. Кроме того, для приспособления запроса к табличным пространствам, которые полностью заполнены (в представлении DBA_FREE_SPACE отсутствуют записи о них), поэтому для получения полного ответа я буду использовать внешнее соединение.
| ЛИСТИНГ 2: составление отчета о свободном пространстве в каждом табличном пространстве. |
set linesize 121
-------------------------------------------------------------------------------
-- free.sql
--
-- This SQL Plus script lists freespace by tablespace
-------------------------------------------------------------------------------
column dummy noprint
column pct_used format 999.9 heading "%/Used"
column name format a19 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "Kbytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
column max_size format 999,999,999 heading "MaxPoss/Kbytes"
column pct_max_used format 999. heading "%/Max/Used"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select (select decode(extent_management,'LOCAL','*',' ') //
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) //
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/ |
Этот запрос позволяет получить:
- Tablespace Name. Имя табличного пространства. Начальная звездочка (*) в этом имени указывает, что табличное пространство - локально-управляемое, а начальный пробел означает, что оно - старомодное табличное пространство, управляемое по словарю данных. Если второй символ имени - a, это означает, что для управления табличным пространством используется технология автоматического управления пространством сегментов Automatic Segment Space Management (ASSM), а если второй символ имени - M, это означает, что табличное пространство управляется вручную (для управления использованием пространства применяются параметры pctused, freelists и т.д.).
- Kbytes. Выделенное пространство в табличном пространстве; сумма килобайтов, потребляемых всеми файлами данных, которые находятся в нем.
- Used. Пространство в табличном пространстве, которое используется какими-то сегментами.
- Free. Свободное пространство в табличном пространстве, которое не выделено никаким сегментами.
- % Used. Отношение свободного пространства к выделенному.
- Largest. Размер наибольшего непрерывного набора доступных блоков (главным образом полезно для табличных пространств, управляемых по словарю данных). Если это число для такого табличного пространства меньше следующего выделяемого экстента какого-то объекта, произойдет сбой с сообщением "out of space" (нехватка пространства), даже если столбец FREE показывает наличие большого объема свободного пространства.
- MaxPoss Kbytes. Максимальный размер файла данных при его автоматическом расширении (предложение AUTOEXTEND). (Замечание: этот размер может быть меньше выделенного размера!)
- % Max Used. Сколько пространства уже использовано (в процентах от максимального размера).
Преобразование представлений чисел в другие системы счисления 1Вопрос. Как преобразовывать числа в какие-то другие системы счисления (скажем, с основанием 2 или 16) и наоборот?
Ответ. В ответе две части. Начиная с СУБД Oracle8i, функции TO_CHAR и TO_NUMBER могут преобразовывать числа с основанием 10 (десятичная система счисления) в числа с основанием 16 (шестнадцатеричная система счисления) и наоборот:
SQL> select to_char(123,'XX') to_hex,
2 to_number('7B','XX') from_hex
3 from dual
4 /
TO_ FROM_HEX
------ -----------------
7B 123
Если вам нужны другие системы счисления, такие как восьмеричная (с основанием 8) или двоичная (с основанием 2), вы можете сделать это очень легко, используя язык PL/SQL; на самом деле, я также буду преобразовывать и числа с основанием 16. Сначала я напишу функцию, которая преобразовывает положительные десятичные числа в числа с любым другим основанием - до основания 36 (расширение алгоритма, используемого для представления чисел в разных системах счисления, вплоть до шестнадцатеричной системы счисления). См. листинг 3.
| ЛИСТИНГ 3: преобразование десятичных чисел в числа с другим основанием (до основания 36). |
create or replace function to_base( p_dec in number, p_base in number )
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(50) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) // l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/ |
Теперь мне нужна соответствующая функция для обратного преобразования чисел с любым заданным основанием в десятичные числа. Она показана на листинге 4.
| ЛИСТИНГ 4: преобразование чисел с другим основанием в десятичные числа. |
create or replace function to_dec
( p_str in varchar2,
p_from_base in number default 16 ) return number
is
l_num number default 0;
l_hex varchar2(50) default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
begin
if (p_from_base = 16)
then
l_num := to_number( p_str, rpad('x',63,'x') );
else
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base+instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
end if;
return l_num;
end to_dec;
/ |
Для удобства я для наиболее распространенных преобразований использую небольшую функцию, показанную на листинге 5.
| ЛИСТИНГ 5: распространенные функции преобразования чисел в разные системы счисления. |
create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_char( p_dec, 'fm'//rpad('x',63,'x') );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/ |
Привилегия SYSDBA
Вопрос. Мне нужно в среде Microsoft Windows Server 2003 запретить пользователям, которые имеют роль oradba подключение без пароля к серверу моей базы данных. Когда я подключаюсь как администратор (член группы oradba) я могу, указывая AS SYSDBA, подключиться к серверу с любым паролем, даже с пустым.
Ответ. На самом деле, с технической точки зрения, вы использовали пароль. Вы вошли в операционную систему, введя пароль.
Привилегия SYSDBA - чрезвычайно мощная привилегия. Когда вы подключаетесь, указывая AS SYSDBA, то используется аутентификация на уровне операционной системы; аутентификация на уровне базы данных не требуется. По сути, она нужна для подключения, когда еще не запущен экземпляр сервера базы данных. Вам нужно ограничивать возможности учетных записей, которые находятся в этой группе. Удалите из нее пользователей, которые не должны иметь такие чрезмерно большие возможности.