Oracle9i,10g�̊e�����m�邽�߂̃e�[�u���E�r���[�̃����B
RDB�́A���������e�[�u���ŕێ����Ă���i���Ƃ������Ǝv���j�B
Oracle�̏ꍇ�Auser_*�Aall_*�Av$*�Ƃ������r���[�i�����ꕔ�̓e�[�u���j�ŎQ�Ƃł���B
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 |
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 |
SQL> desc ����
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�̌`�Ō��鎖���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�[��') |
�V�m�j�� | DBMS_METADATA.GET_DDL('SYNONYM','����','�I�[�i�[��') |
����OBJECT_TYPE�iselect distinct object_type from all_objects;
�j�ł��g�������B
�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������@
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
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�[�ɕ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
�@
�@
�@
�@
�@
�@
�@
�@
�@
�@
�@
�@
�@
�@
�@
�@