S-JIS[2005-05-15/2012-06-18] �ύX����

Oracle�I�u�W�F�N�g ���Ɖ�

Oracle9i,10g�̊e�����m�邽�߂̃e�[�u���E�r���[�̃����B


�����ێ����Ă���r���[

RDB�́A���������e�[�u���ŕێ����Ă���i���Ƃ������Ǝv���j�B
Oracle�̏ꍇ�Auser_*�Aall_*�Av$*�Ƃ������r���[�i�����ꕔ�̓e�[�u���j�ŎQ�Ƃł���B

�ÓI�f�[�^�E�f�B�N�V���i���E�r���[

user_*�͂��̃��[�U�[��p�̏��ŁAall_*�͑S���[�U�[�̏��B����user��all�̓y�A�ŁA�������e�̂��̂�����B
dba_*��sys���[�U�[�p�ias sysdba�t���Őڑ��j�B

�r���[�� ���e �ړ��� �X�V��
user_objects �I�u�W�F�N�g�S���i�Ƃ������S�I�u�W�F�N�g�j user all dba �@
user_tables
user_all_tables
user_tab_columns
�e�[�u�� user all dba 2012-06-18
user_indexes
user_ind_columns
user_ind_expressions
�C���f�b�N�X�i�����j user all dba 2010-07-29
user_constraints
user_cons_columns
���������� user all dba 2008-02-16
user_views �r���[ user all dba �@
user_mviews
user_mview_logs
�}�e���A���C�Y�h�E�r���[ user all dba �@
user_sequences �V�[�P���X user all dba �@
user_synonyms �V�m�j�� user all dba �@
user_types ���[�U�[��`�^ user all dba �@
user_db_links DB�����N user all dba �@
user_dimensions �f�B�����W���� user all dba �@
user_tab_partitions
user_part_key_columns
user_part_tables
user_ind_partitions
user_part_indexes
�p�[�e�B�V������` user all dba 2009-01-06
user_jobs �W���u�i��莞�Ԗ���PL/SQL�����s����j user all dba 2007-12-08
user_recyclebin ���T�C�N���r�� user �@ �@ 2007-12-13
user_procedures FUNCTION�APROCEDURE�Œ�`����Ă��郋�[�`�� user all dba 2007-09-22
user_arguments FUNCTION�APROCEDURE������ user all - 2008-01-27
user_source FUNCTION�APROCEDURE�AJAVA SOURCE�A���[�U�[��`�^���̃\�[�X user all dba �@
user_dependencies �I�u�W�F�N�g�i�֐��E�v���V�[�W���E�r���[���j�̈ˑ��֌W user �@ �@ 2008-01-08
user_triggers �g���K�[ user all dba �@
user_users ���[�U�[ user all dba �@
dba_data_files �\�̈�i�e�[�u���X�y�[�X�j�̃t�@�C���� - - dba 2006-07-08
user_free_space �\�̈�i�e�[�u���X�y�[�X�j���̋󂫗e�� user - dba �@
nls_session_parameters �Z�b�V�����p�����[�^ �i��v$nls_parameters�j - - - 2006-11-21

���I�p�t�H�[�}���X�E�r���[

v$�Ȃ񂿂��B

�I�u�W�F�N�g�� ���e ���� �֘A �X�V��
v$version �o�[�W�������i�����j �@ define 2007-12-11
v$option �g�p�”\�ȃI�v�V���� PARAMETER �I�v�V������ �p�[�e�B�V����
�r�b�g�}�b�v����
�����̌���
2009-12-24
VALUE �g�p�”�
v$parameter �p�����[�^�[ �@ show parameters 2009-01-06
v$nls_parameters �Z�b�V�����p�����[�^ �@ nls_session_parameters 2006-12-26
v$session �Z�b�V������� USERNAME ���[�U�[�� �Z�b�V�����폜���@ 2008-11-15
LOGON_TIME ���O�I������
LAST_CALL_ET �Ō�ɏ������s���Ă���̌o�ߎ���
SQL_ADDRESS ���s����SQL��\���l
SQL_ID ���s����SQL��\���l�iOracle10�ȍ~�j
v$transaction �g�����U�N�V������� �@ 2006-12-27
��$lock
v$locked_object
���b�N��� �@ 2009-03-02
v$sql ���s���ꂽSQL SQL_TEXT SQL���̐擪1000���� �@ 2010-10-23
ADDRESS SQL��\���l
SQL_ID SQL��\���l�iOracle10�ȍ~�j
CPU_TIME �P�ʂ́AOracle9i�ł̓~���b�B
Oracle10gR2�ł̓}�C�N���b�B
ELAPSED_TIME
v$object_usage �����̎g�p�� USED �C���f�b�N�X���g�p���ꂽ���ǂ��� �@ 2010-02-04

�I�u�W�F�N�g��`

�e�[�u���E�r���[���̃I�u�W�F�N�g�̒�`��m����@

SQL> desc ����

�I�u�W�F�N�g�̎�ނ�m����@

SQL> select object_name,owner,object_type
  2  from all_objects
  3  where object_name='EMP';

OBJECT_NAME                    OWNER                          OBJECT_TYPE
------------------------------ ------------------------------ ------------------
EMP                            SCOTT                          TABLE

�e�[�u����C���f�b�N�X��DDL��m����@

�e�[�u����C���f�b�N�X���̒�`��DDL�̌`�Ō��鎖���o����B[2008-10-04]

SQL> set long 9999
SQL> set pages 9999
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
�`
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
�`
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"
�e�[�u�� DBMS_METADATA.GET_DDL('TABLE','�e�[�u����','�I�[�i�[��')
�C���f�b�N�X DBMS_METADATA.GET_DDL('INDEX','�C���f�b�N�X��','�I�[�i�[��')
�r���[ ��user_views
�}�e�r���[ ��user_mviews
�V�[�P���X DBMS_METADATA.GET_DDL('SEQUENCE','�V�[�P���X��','�I�[�i�[��')
�t�@���N�V���� DBMS_METADATA.GET_DDL('FUNCTION','�֐���','�I�[�i�[��')
�v���V�[�W�� DBMS_METADATA.GET_DDL('PROCEDURE','�v���V�[�W����','�I�[�i�[��')
Java DBMS_METADATA.GET_DDL('JAVA_SOURCE','Java�\�[�X��','�I�[�i�[��')
�p�b�P�[�W DBMS_METADATA.GET_DDL('PACKAGE','�p�b�P�[�W��','�I�[�i�[��')
DBMS_METADATA.GET_DDL('PACKAGE_BODY','�p�b�P�[�W��','�I�[�i�[��')
�V�m�j�� DBMS_METADATA.GET_DDL('SYNONYM','�֐���','�I�[�i�[��')

����OBJECT_TYPE�iselect distinct object_type from all_objects;�j�ł��g�������B


�r���[

�r���[�̒�`���e���m�F������@�B[2007-12-28]

SQL> set long 1000
SQL> select text from user_views where view_name='VW_EMP';

TEXT
--------------------------------------------------------------------------------
select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
from emp

�ucreate view�v�ō�����Ƃ��́uAS�v�ȍ~�̓��e���o��i���s�����̂܂܁j�B
�uselect *�v�ō�������͍̂��ڂ����ꂼ��w�肳��Ă���悤���B

�i�r���[�̓��e�������Ă���jtext���ڂ�long�^�i�����•ϕ�����j�Ȃ̂ŁA�r���[��`���傫���Ƃ���SQL*Plus�ŕ\������Ɠr�؂��”\��������B
���������Ƃ��́A�uset long�v�ɂ����long�^��SQL*Plus�ł̕\��������傫���w�肷��΂悢�B

���}�e���A���C�Y�h�E�r���[�̒�`���e���m�F������@


���[�U�[

���݃��O�C�����Ă��郆�[�U�[��m����@

SQL> show user
���[�U�[��"HISHIDAMA"�ł��B
SQL> select user from dual;

USER
------------------------------
HISHIDAMA
SQL> select username,account_status from user_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HISHIDAMA                      OPEN

�쐬����Ă��郆�[�U�[�̈ꗗ

SQL> select * from all_users;

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------
SYS                                     0 02-05-12
		�`
SCOTT                                  59 02-05-12
HISHIDAMA                              61 05-05-15

31�s���I������܂����B

���[�U�[�������Ă��錠��

���[�U�[�ɕt�^����Ă������[�� [/2007-09-25]

SQL> select * from USER_ROLE_PRIVS;

USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
HISHIDAMA                      CONNECT                        NO  YES NO
HISHIDAMA                      EMP_ACCESS                     NO  YES NO
HISHIDAMA                      RESOURCE                       NO  YES NO
SQL> select * from dba_role_privs where grantee='HISHIDAMA';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
HISHIDAMA                      CONNECT                        NO  YES
HISHIDAMA                      RESOURCE                       NO  YES
HISHIDAMA                      EMP_ACCESS                     NO  YES

���[�U�[�ɕt�^����Ă��郍�[���ƁA���̋�̓I�Ȍ����i�V�X�e�������̂݁j [/2007-09-25]

SQL> select * from ROLE_SYS_PRIVS;

ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO

16�s���I������܂����B

���[�U�[�ɕt�^����Ă����V�X�e������ [2007-09-25]

SQL> select * from SESSION_PRIVS;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
EXECUTE ANY PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

15�s���I������܂����B

�•ʂɐݒ�����Ă����I�u�W�F�N�g�����i�e�[�u���A�N�Z�X���v���V�[�W�����s���j[2007-09-25]

SQL> select table_name,privilege,owner from USER_TAB_PRIVS_RECD;

TABLE_NAME                     PRIVILEGE                                OWNER
------------------------------ ---------------------------------------- ----------------------------
DEPT                           SELECT                                   SCOTT

���[���ɂ���ȕʂɐݒ�����Ă����I�u�W�F�N�g���� [2007-09-22/2007-09-25]
�v���V�[�W���̎��s�����ł��A�v���V�[�W������TABLE_NAME�̍��ɕ\�������B

SQL> select table_name,privilege,role from ROLE_TAB_PRIVS;

TABLE_NAME                     PRIVILEGE                                ROLE
------------------------------ ---------------------------------------- ----------------------------
EMP                            DELETE                                   EMP_ACCESS
EMP                            INSERT                                   EMP_ACCESS
EMP                            SELECT                                   EMP_ACCESS
EMP                            UPDATE                                   EMP_ACCESS
TEST_F                         EXECUTE                                  EMP_ACCESS

�����̃I�u�W�F�N�g�ɒN���A�N�Z�X�ł��邩�i�I�u�W�F�N�g�����j[2007-09-25]

SQL> select table_name,privilege,grantee from USER_TAB_PRIVS_MADE;
TABLE_NAME                     PRIVILEGE                                GRANTEE
------------------------------ ---------------------------------------- ----------------------------
DEPT                           SELECT                                   HISHIDAMA
EMP                            DELETE                                   EMP_ACCESS
EMP                            INSERT                                   EMP_ACCESS
EMP                            SELECT                                   EMP_ACCESS
EMP                            UPDATE                                   EMP_ACCESS
TEST_F                         EXECUTE                                  EMP_ACCESS
TEST_P                         EXECUTE                                  HISHIDAMA

7�s���I������܂����B

����

sysdba�i�Ⴆ��sys���[�U�[�j�ŎQ�Ƃ���B

���[���̈ꗗ

SQL> select * from dba_roles;

ROLE                           PASSWORD
------------------------------ --------
CONNECT                        NO
RESOURCE                       NO
DBA                            NO
		�`
SALES_HISTORY_ROLE             NO

30�s���I������܂����B

�����[�U�[�ɕt�^����Ă��錠��


Oracle�ڎ��֖߂� / �V�@�\�֖߂� / �Z�p�����֖߂�
���[���̑��M��F�Ђ�����

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@

�@