22.数据字典
1.访问数据库字典区别
sys@ORCL 2023-02-26 16:20:31> select count(*) from dictionary;
COUNT(*)
----------
4913
Elapsed: 00:00:00.07
sys@ORCL 2023-02-26 16:20:40> desc dictionary;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME VARCHAR2(128)
COMMENTS VARCHAR2(4000)2.自己拥有的所有对象
--user_objects
--建立时间
--最后依次修改时间
--状态
hr@ORCLPDB01 2023-02-26 16:25:35> select object_name,object_type,created,status from user_objects order by object_type;
OBJECT_NAME OBJECT_TYPE CREATED STATUS
------------------------------ ----------------------- ------------------- -------
LOC_COUNTRY_IX INDEX 2023-02-12 23:34:24 VALID
DEPT_LOCATION_IX INDEX 2023-02-12 23:34:24 VALID
EMP_NAME_IX INDEX 2023-02-12 23:34:24 VALID
EMP_MANAGER_IX INDEX 2023-02-12 23:34:24 VALID
EMP_JOB_IX INDEX 2023-02-12 23:34:24 VALID
EMP_DEPARTMENT_IX INDEX 2023-02-12 23:34:24 VALID
JHIST_JOB_IX INDEX 2023-02-12 23:34:24 VALID
LOC_STATE_PROVINCE_IX INDEX 2023-02-12 23:34:24 VALID
LOC_CITY_IX INDEX 2023-02-12 23:34:24 VALID
JHIST_DEPARTMENT_IX INDEX 2023-02-12 23:34:24 VALID
JHIST_EMPLOYEE_IX INDEX 2023-02-12 23:34:24 VALID
REG_ID_PK INDEX 2023-02-12 23:34:24 VALID
DEPT_ID_PK INDEX 2023-02-12 23:34:24 VALID
COUNTRY_C_ID_PK INDEX 2023-02-12 23:34:24 VALID
JOB_ID_PK INDEX 2023-02-12 23:34:24 VALID
EMP_EMAIL_UK INDEX 2023-02-12 23:34:24 VALID
EMP_EMP_ID_PK INDEX 2023-02-12 23:34:24 VALID
LOC_ID_PK INDEX 2023-02-12 23:34:24 VALID
JHIST_EMP_ID_ST_DATE_PK INDEX 2023-02-12 23:34:24 VALID
ADD_JOB_HISTORY PROCEDURE 2023-02-12 23:34:24 VALID
SECURE_DML PROCEDURE 2023-02-12 23:34:24 VALID
EMPLOYEES_SEQ SEQUENCE 2023-02-12 23:34:24 VALID
DEPARTMENTS_SEQ SEQUENCE 2023-02-12 23:34:24 VALID
DEPT_DEPTID_SEQ SEQUENCE 2023-02-26 11:29:06 VALID
LOCATIONS_SEQ SEQUENCE 2023-02-12 23:34:24 VALID
JOB_HISTORY TABLE 2023-02-12 23:34:24 VALID
EMPLOYEES TABLE 2023-02-12 23:34:24 VALID
JOBS TABLE 2023-02-12 23:34:24 VALID
DEPARTMENTS TABLE 2023-02-12 23:34:24 VALID
LOCATIONS TABLE 2023-02-12 23:34:24 VALID
COUNTRIES TABLE 2023-02-12 23:34:24 VALID
REGIONS TABLE 2023-02-12 23:34:24 VALID
UPDATE_JOB_HISTORY TRIGGER 2023-02-12 23:34:24 VALID
SECURE_EMPLOYEES TRIGGER 2023-02-12 23:34:24 VALID
EMPVU80 VIEW 2023-02-26 11:08:07 VALID
SALVU50 VIEW 2023-02-26 11:12:51 VALID
EMP_DETAILS_VIEW VIEW 2023-02-12 23:34:24 VALID
--all_objects
--有权限访问的所有对象user_tables
hr@ORCLPDB01 2023-02-26 16:25:37> desc user_tables;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME NOT NULL VARCHAR2(128)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(128)
IOT_NAME VARCHAR2(128)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(128)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(30)
DROPPED VARCHAR2(3)
READ_ONLY VARCHAR2(3)
SEGMENT_CREATED VARCHAR2(3)
RESULT_CACHE VARCHAR2(7)
CLUSTERING VARCHAR2(3)
ACTIVITY_TRACKING VARCHAR2(23)
DML_TIMESTAMP VARCHAR2(25)
HAS_IDENTITY VARCHAR2(3)
CONTAINER_DATA VARCHAR2(3)
INMEMORY VARCHAR2(8)
INMEMORY_PRIORITY VARCHAR2(8)
INMEMORY_DISTRIBUTE VARCHAR2(15)
INMEMORY_COMPRESSION VARCHAR2(17)
INMEMORY_DUPLICATE VARCHAR2(13)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
EXTERNAL VARCHAR2(3)
HYBRID VARCHAR2(3)
CELLMEMORY VARCHAR2(24)
CONTAINERS_DEFAULT VARCHAR2(3)
CONTAINER_MAP VARCHAR2(3)
EXTENDED_DATA_LINK VARCHAR2(3)
EXTENDED_DATA_LINK_MAP VARCHAR2(3)
INMEMORY_SERVICE VARCHAR2(12)
INMEMORY_SERVICE_NAME VARCHAR2(1000)
CONTAINER_MAP_OBJECT VARCHAR2(3)
MEMOPTIMIZE_READ VARCHAR2(8)
MEMOPTIMIZE_WRITE VARCHAR2(8)
HAS_SENSITIVE_COLUMN VARCHAR2(3)
ADMIT_NULL VARCHAR2(3)
DATA_LINK_DML_ENABLED VARCHAR2(3)
LOGICAL_REPLICATION VARCHAR2(8)
hr@ORCLPDB01 2023-02-26 16:27:41> desc user_tab_columns;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
DATA_TYPE VARCHAR2(128)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(128)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(2000)
HIGH_VALUE RAW(2000)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
DEFAULT_ON_NULL VARCHAR2(3)
IDENTITY_COLUMN VARCHAR2(3)
EVALUATION_EDITION VARCHAR2(128)
UNUSABLE_BEFORE VARCHAR2(128)
UNUSABLE_BEGINNING VARCHAR2(128)
COLLATION VARCHAR2(100)
例子
hr@ORCLPDB01 2023-02-26 16:31:26> r
1 select column_name,data_type,data_length,data_precision,data_scale,nullable
2 from user_tab_columns
3* where table_name = 'EMPLOYEES'
COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_PRECISION DATA_SCALE N
----------------------------------- ------------------------------ ----------- -------------- ---------- -
EMPLOYEE_ID NUMBER 22 6 0 N
FIRST_NAME VARCHAR2 20 Y
LAST_NAME VARCHAR2 25 N
EMAIL VARCHAR2 25 N
PHONE_NUMBER VARCHAR2 20 Y
HIRE_DATE DATE 7 N
JOB_ID VARCHAR2 10 N
SALARY NUMBER 22 8 2 Y
COMMISSION_PCT NUMBER 22 2 2 Y
MANAGER_ID NUMBER 22 6 0 Y
DEPARTMENT_ID NUMBER 22 4 0 Yuser_constraints 描述约束信息
uesr_cons_columns 描述约束所在列信息
hr@ORCLPDB01 2023-02-26 16:31:27> desc user_constraints;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER VARCHAR2(128)
CONSTRAINT_NAME NOT NULL VARCHAR2(128)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(128)
SEARCH_CONDITION LONG
SEARCH_CONDITION_VC VARCHAR2(4000)
R_OWNER VARCHAR2(128)
R_CONSTRAINT_NAME VARCHAR2(128)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(128)
INDEX_NAME VARCHAR2(128)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
ORIGIN_CON_ID NUMBERhr@ORCLPDB01 2023-02-26 16:35:59> r
1 select constraint_name,constraint_type,search_condition,r_constraint_name,delete_rule,status
2 from user_constraints
3* where table_name = 'EMPLOYEES'
CONSTRAINT_NAME C SEARCH_CONDITION R_CONSTRAINT_NAME DELETE_RU STATUS
----------------------------------- - ----------------------------------- ------------------------------ --------- --------
EMP_DEPT_FK R DEPT_ID_PK NO ACTION ENABLED
EMP_JOB_FK R JOB_ID_PK NO ACTION ENABLED
EMP_MANAGER_FK R EMP_EMP_ID_PK NO ACTION ENABLED
EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL ENABLED
EMP_EMAIL_NN C "EMAIL" IS NOT NULL ENABLED
EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL ENABLED
EMP_JOB_NN C "JOB_ID" IS NOT NULL ENABLED
EMP_SALARY_MIN C salary > 0 ENABLED
EMP_EMAIL_UK U ENABLED
EMP_EMP_ID_PK P ENABLED查询列信息
hr@ORCLPDB01 2023-02-26 16:36:00> desc user_cons_columns;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OWNER NOT NULL VARCHAR2(128)
CONSTRAINT_NAME NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBERhr@ORCLPDB01 2023-02-26 16:37:50> select constraint_name,column_name
2 from user_cons_columns
3 where table_name = 'EMPLOYEES';
CONSTRAINT_NAME COLUMN_NAME
----------------------------------- -----------------------------------
EMP_LAST_NAME_NN LAST_NAME
EMP_EMAIL_NN EMAIL
EMP_HIRE_DATE_NN HIRE_DATE
EMP_JOB_NN JOB_ID
EMP_SALARY_MIN SALARY
EMP_EMAIL_UK EMAIL
EMP_EMP_ID_PK EMPLOYEE_ID
EMP_DEPT_FK DEPARTMENT_ID
EMP_JOB_FK JOB_ID
EMP_MANAGER_FK MANAGER_ID查询视图信息
desc user_views
select view_name from user_views;
select text_name from user_views where view_name = 'EMP_DETALLS_VIEW';查询序列信息
desc user_sequences;select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences;查询索引信息
desc user_indexes;--索引信息
desc user_ind_columns;--索引所在列信息
