*************
사용자 스키마
*************
스키마는 데이터베이스 객체의 논리적인 모음이다. 스키마 이름을 통해 객체를 식별할 수 있다는 것이며, 스키마 객체를 물리적으로 분리된 저장공간에 저장하지 않는다. 객체는 하나의 스키마에만 존재할 수 있으며 동시에 다른 스키마에 존재할 수 없다. 스키마는 사용자가 생성할 수 없으며, 데이터베이스 사용자 생성 시 해당 사용자는 사용자 이름과 동일한 이름의 단일 스키마를 소유하게 된다. 스키마에는 테이블, 시리얼 및 트리거가 포함된다. 스키마의 객체는 동일한 스키마 내에서 고유한 이름을 가져야 하지만 다른 스키마의 객체와 중복되는 이름을 가질 수 있다.
객체에 접근하려면 사용자는 "스키마 이름.테이블 이름"의 경로 표현식을 사용해야 한다. 사용자의 스키마가 객체의 스키마와 (소유자) 동일한 경우 경로 표현식에서 스키마 이름을 생략할 수 있다. 스키마 이름을 생략하면 해당 사용자의 스키마 이름이 사용된다. "스키마 이름.테이블 이름"의 경로 표현식을 사용하더라도 사용자는 해당 객체를 사용할 수 있는 권한이 있어야 한다.
.. note::
11.2 버전 이후부터 "스키마 ≒ 데이터베이스" 개념이 "스키마 ≒ 사용자"로 변경되었다. 11.2 버전 이전에는 데이터베이스에 단일 스키마만 가능했지만 11.2 버전부터는 사용자 별로 스키마를 사용할 수 있다. 따라서 테이블 이름만으로는 다른 사용자가 소유한 테이블에 접근할 수 없으며, 스키마 (소유자) 이름을 접두사로 붙여서 사용해야 해당 테이블에 접근할 수 있다.
기본 예제 데이터베이스인 demodb에서는 다음과 같은 차이점이 있다.
.. code-block:: shell
csql -u dba demodb
.. code-block:: sql
SELECT name FROM athlete LIMIT 1;
ERROR: before ' ; '
Unknown class "dba.athlete".
SELECT name FROM public.athlete LIMIT 1;
name
======================
'Fernandez Jesus'
테이블, 시리얼 및 트리거를 관리하는 시스템 테이블에 (_db_class, db_serial, db_trigger) unique_name 칼럼이 추가되었다. unique_name 칼럼은 스키마 이름이 접두사로 붙은 이름을 저장한다. _db_class의 unique_name 칼럼에서 시스템 테이블 이름은 스키마 이름이 접두사로 붙지 않는다.
.. code-block:: shell
csql -u public demodb
.. code-block:: sql
CREATE TABLE table_1 (column_1 INTEGER);
CREATE TABLE table_2 (column_1 INTEGER);
CREATE TRIGGER trigger_1 AFTER INSERT ON table_1 EXECUTE INSERT INTO table_2 VALUES (obj.column_1);
.. code-block:: shell
csql -u dba demodb
.. code-block:: sql
SELECT unique_name, class_name, owner.name FROM _db_class ORDER BY unique_name;
unique_name class_name owner.name
==============================================================
'_db_attribute' '_db_attribute' 'DBA'
'_db_auth' '_db_auth' 'DBA'
'_db_charset' '_db_charset' 'DBA'
'_db_class' '_db_class' 'DBA'
'_db_collation' '_db_collation' 'DBA'
...
'db_attr_setdomain_elm' 'db_attr_setdomain_elm' 'DBA'
'db_attribute' 'db_attribute' 'DBA'
'db_auth' 'db_auth' 'DBA'
'db_authorization' 'db_authorization' 'DBA'
'db_authorizations' 'db_authorizations' 'DBA'
...
'public.athlete' 'athlete' 'PUBLIC'
'public.code' 'code' 'PUBLIC'
'public.event' 'event' 'PUBLIC'
'public.game' 'game' 'PUBLIC'
'public.history' 'history' 'PUBLIC'
SELECT unique_name, name, owner.name FROM db_serial ORDER BY unique_name;
unique_name name owner.name
=========================================================
'public.athlete_ai_code' 'athlete_ai_code' 'PUBLIC'
'public.event_no' 'event_no' 'PUBLIC'
'public.stadium_no' 'stadium_no' 'PUBLIC'
SELECT unique_name, name, owner.name FROM db_trigger ORDER BY unique_name;
unique_name name owner.name
=============================================
'public.trigger_1' 'trigger_1' 'PUBLIC'
Day widened into its first perfection as we moved down the highroad toward a near fork whose right was to lead Harry and his solemn cortge southward, while the left should be our eastward course. Camille and I rode horseback, side by side, with no one near enough to smile at my sentimental laudations of the morning's splendors, or at her for repaying my eloquence with looks so full of tender worship, personal acceptance and self-bestowal, that to tell of them here would make as poor a show as to lift a sea-flower out of the sea; they call for piccolo notes and I am no musician. The little man elected to have a cab. When Bow Street was reached Prout had the satisfaction of finding that all his birds had been netted. He received the warm congratulations of his inspector modestly. Caloric or air engines. I did not feel very comfortable after what had happened to those soldiers who lost their lives so cruelly sudden, or in any case had been seriously wounded, while the officers took little notice of them. But it was desirable to behave as discreetly as possible, and so to get a permit to Maastricht. "Louvain, It will check up, that way, too, smiled Larry. "Me run away," thought Shorty, as they walked along. "Hosses couldn't drag me away. I only hope that house is 10 miles off." Reuben began to take off his coatyoung Realf drew back almost in disgust. "Well, would Robert have stolen money, or Albert disgraced your name, to get free, if you and your farm hadn't made them slaves? If you hadn't been a heartless slave-driver would George have died the other night alone on the Moor?or would Richard have taken advantage of a neighbour's charity to escape from you? Don't you see that your ambition has driven you to make slaves of your children?" "D?an't tell me," said Coalbran in the bar, "as it wurn't his fault. Foot-and-mouth can't just drop from heaven. He must have bought some furriners, and they've carried it wud 'em, surelye." But meantime a strange restlessness consumed her, tinctured by a horrible boldness. There were moments when she no longer was afraid of Handshut, when she felt herself impelled to seek him out, and make the most of the short time they had together. There could be no danger, for he was going so soon ... so few more words, so few more glances.... Thus her mind worked. "What mean you, woman?" quickly returned De Boteler; "do you accuse the keeper of my chase as having plotted against your son, or whom do you suspect?" Her face was shrivelled and yellow, and the dark full eyes that now, as it were, stood forth from the sunken cheeks, looked with a strange brightness on the scene, and seemed well adapted to stamp the character of witch on so withered a form. And perhaps there were few of those entirely uninterested in the matter who now gazed upon her, who would not have sworn that she merited the stake. "Thou art set over the people, and to the Lord's anointed I come to seek for justice." HoMEŷһëƬѸպ
ENTER NUMBET 0018szhoujinhua.com.cn
pipajiu.com.cn
www.etongmbh.com.cn
7410.net.cn
www.wjpg.com.cn
www.dfxt007.com.cn
www.fsgk691.com.cn
www.gpij.com.cn
www.huangame.com.cn
www.tlbus.com.cn