查看Oracle实例有哪些用户?
涉及的表为dba_users
- [oracle@rtest ~]$ sqlplus /nolog
- SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 2 11:05:49 2013
- Copyright (c) 1982, 2011, Oracle. All rights reserved.
- idle> conn /as sysdba
- Connected.
- sys@TESTDB> desc dba_users
- Name Null? Type
- ----------------------------------------------------- -------- ------------------------------------
- USERNAME NOT NULL VARCHAR2(30)
- USER_ID NOT NULL NUMBER
- PASSWORD VARCHAR2(30)
- ACCOUNT_STATUS NOT NULL VARCHAR2(32)
- LOCK_DATE DATE
- EXPIRY_DATE DATE
- DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
- TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
- CREATED NOT NULL DATE
- PROFILE NOT NULL VARCHAR2(30)
- INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
- EXTERNAL_NAME VARCHAR2(4000)
- PASSWORD_VERSIONS VARCHAR2(8)
- EDITIONS_ENABLED VARCHAR2(1)
- AUTHENTICATION_TYPE VARCHAR2(8)
查看用户状态:
- sys@TESTDB> select username,account_status from dba_users;
- USERNAME ACCOUNT_STATUS
- ------------------------------ --------------------------------
- SYS OPEN
- SYSTEM OPEN
- OUTLN EXPIRED & LOCKED
- MGMT_VIEW EXPIRED & LOCKED
- FLOWS_FILES EXPIRED & LOCKED
- MDSYS EXPIRED & LOCKED
- ORDSYS EXPIRED & LOCKED
- EXFSYS EXPIRED & LOCKED
- DBSNMP EXPIRED & LOCKED
- WMSYS EXPIRED & LOCKED
- APPQOSSYS EXPIRED & LOCKED
- APEX_030200 EXPIRED & LOCKED
- OWBSYS_AUDIT EXPIRED & LOCKED
- ORDDATA EXPIRED & LOCKED
- CTXSYS EXPIRED & LOCKED
- ANONYMOUS EXPIRED & LOCKED
- SYSMAN EXPIRED & LOCKED
- XDB EXPIRED & LOCKED
- ORDPLUGINS EXPIRED & LOCKED
- OWBSYS EXPIRED & LOCKED
- SI_INFORMTN_SCHEMA EXPIRED & LOCKED
- OLAPSYS EXPIRED & LOCKED
- SCOTT EXPIRED & LOCKED
- ORACLE_OCM EXPIRED & LOCKED
- XS$NULL EXPIRED & LOCKED
- BI EXPIRED & LOCKED
- PM EXPIRED & LOCKED
- MDDATA EXPIRED & LOCKED
- IX EXPIRED & LOCKED
- SH EXPIRED & LOCKED
- DIP EXPIRED & LOCKED
- OE EXPIRED & LOCKED
- APEX_PUBLIC_USER EXPIRED & LOCKED
- HR EXPIRED & LOCKED
- SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED
- SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED
- 36 rows selected.
比如解锁sh账户,sh账户为OCP考试需要用到的用户,SH账户里涉及的多张表在OCP有涉及考试。
目前SH的账户的状态为:EXPIRED & LOCKED
用以下命令给SH账户解锁:
sys@TESTDB> alter user sh account unlock; --解开LOCKED
sys@TESTDB> alter user sh identified by sh;--解开EXPIRED
- sys@TESTDB> alter user sh account unlock;
- User altered.
- sys@TESTDB> select username,account_status from dba_users;
- USERNAME ACCOUNT_STATUS
- ------------------------------ --------------------------------
- SYS OPEN
- SYSTEM OPEN
- SH EXPIRED
- OUTLN EXPIRED & LOCKED
- MGMT_VIEW EXPIRED & LOCKED
- FLOWS_FILES EXPIRED & LOCKED
- MDSYS EXPIRED & LOCKED
- ORDSYS EXPIRED & LOCKED
- EXFSYS EXPIRED & LOCKED
- DBSNMP EXPIRED & LOCKED
- WMSYS EXPIRED & LOCKED
- APPQOSSYS EXPIRED & LOCKED
- APEX_030200 EXPIRED & LOCKED
- OWBSYS_AUDIT EXPIRED & LOCKED
- ORDDATA EXPIRED & LOCKED
- CTXSYS EXPIRED & LOCKED
- ANONYMOUS EXPIRED & LOCKED
- SYSMAN EXPIRED & LOCKED
- XDB EXPIRED & LOCKED
- ORDPLUGINS EXPIRED & LOCKED
- OWBSYS EXPIRED & LOCKED
- SI_INFORMTN_SCHEMA EXPIRED & LOCKED
- OLAPSYS EXPIRED & LOCKED
- SCOTT EXPIRED & LOCKED
- ORACLE_OCM EXPIRED & LOCKED
- XS$NULL EXPIRED & LOCKED
- BI EXPIRED & LOCKED
- PM EXPIRED & LOCKED
- MDDATA EXPIRED & LOCKED
- IX EXPIRED & LOCKED
- DIP EXPIRED & LOCKED
- OE EXPIRED & LOCKED
- APEX_PUBLIC_USER EXPIRED & LOCKED
- HR EXPIRED & LOCKED
- SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED
- SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED
- 36 rows selected.
- sys@TESTDB> alter user sh identified by sh;
- User altered.
- sys@TESTDB> select username,account_status from dba_users;
- USERNAME ACCOUNT_STATUS
- ------------------------------ --------------------------------
- SYS OPEN
- SYSTEM OPEN
- SH OPEN
- OUTLN EXPIRED & LOCKED
- MGMT_VIEW EXPIRED & LOCKED
- FLOWS_FILES EXPIRED & LOCKED
- MDSYS EXPIRED & LOCKED
- ORDSYS EXPIRED & LOCKED
- EXFSYS EXPIRED & LOCKED
- DBSNMP EXPIRED & LOCKED
- WMSYS EXPIRED & LOCKED
- APPQOSSYS EXPIRED & LOCKED
- APEX_030200 EXPIRED & LOCKED
- OWBSYS_AUDIT EXPIRED & LOCKED
- ORDDATA EXPIRED & LOCKED
- CTXSYS EXPIRED & LOCKED
- ANONYMOUS EXPIRED & LOCKED
- SYSMAN EXPIRED & LOCKED
- XDB EXPIRED & LOCKED
- ORDPLUGINS EXPIRED & LOCKED
- OWBSYS EXPIRED & LOCKED
- SI_INFORMTN_SCHEMA EXPIRED & LOCKED
- OLAPSYS EXPIRED & LOCKED
- SCOTT EXPIRED & LOCKED
- ORACLE_OCM EXPIRED & LOCKED
- XS$NULL EXPIRED & LOCKED
- BI EXPIRED & LOCKED
- PM EXPIRED & LOCKED
- MDDATA EXPIRED & LOCKED
- IX EXPIRED & LOCKED
- DIP EXPIRED & LOCKED
- OE EXPIRED & LOCKED
- APEX_PUBLIC_USER EXPIRED & LOCKED
- HR EXPIRED & LOCKED
- SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED
- SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED
- 36 rows selected.
查看SH用户有哪些表?
- sh@TEST0924> select * from tab;
- TNAME TABTYPE CLUSTERID
- ------------------------------ ------- ----------
- 1_EMP TABLE
- BIN$51XUts/lTCDgQwEAAH/BlA==$0 TABLE
- CAL_MONTH_SALES_MV TABLE
- CHANNELS TABLE
- COSTS TABLE
- COUNTRIES TABLE
- CUSTOMERS TABLE
- DIMENSION_EXCEPTIONS TABLE
- DR$SUP_TEXT_IDX$I TABLE
- DR$SUP_TEXT_IDX$K TABLE
- DR$SUP_TEXT_IDX$N TABLE
- DR$SUP_TEXT_IDX$R TABLE
- EMP_1 TABLE
- EMP_2 TABLE
- EMP_3 TABLE
- FWEEK_PSCAT_SALES_MV TABLE
- INVOICE TABLE
- NEW_SALES TABLE
- PRICE_LIST TABLE
- PRODUCTS TABLE
- PROFITS VIEW
- PROMOTIONS TABLE
- SALES TABLE
- SALES_TRANSACTIONS_EXT TABLE
- SUPPLEMENTARY_DEMOGRAPHICS TABLE
- TIMES TABLE
- TRANSACTIONS TABLE
- TRANSACTIONS1 TABLE
- 28 rows selected.