My Oracle Cook Book-來(lái)自oralce菜鳥(niǎo)的學(xué)習(xí)筆記

準(zhǔn)備

用到的是oracle自帶的用戶和表
用戶為scott/tiger
步驟是：連接數(shù)據(jù)庫(kù)
-> sqlplus scott/tiger
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott.

初始認(rèn)證用戶

如果scott用戶沒(méi)有鏈接到數(shù)據(jù)庫(kù)，
通過(guò)數(shù)據(jù)庫(kù)認(rèn)證查看用戶的帳號(hào)狀態(tài)，有可能是scott用戶的狀態(tài)處于EXPIRED & LOCKED

1-1   顯示所有用戶的帳號(hào)狀態(tài)

以sysdba用戶登錄：
->sqlplus / as sysdba
查看所有用戶的帳號(hào)狀態(tài)查找的表為dba_users(視圖）;
SQL> desc dba_users;
Name                        Type           Nullable Default Comments                                
--------------------------- -------------- -------- ------- ---------------------------------------
USERNAME                    VARCHAR2(30)                    Name of the user                        
USER_ID                     NUMBER                          ID number of the user                   
PASSWORD                    VARCHAR2(30)   Y                Encrypted password                      
ACCOUNT_STATUS              VARCHAR2(32)                                                            
LOCK_DATE                   DATE           Y                                                        
EXPIRY_DATE                 DATE           Y                                                        
DEFAULT_TABLESPACE          VARCHAR2(30)                    Default tablespace for data             
TEMPORARY_TABLESPACE        VARCHAR2(30)                    Default tablespace for temporary tables
CREATED                     DATE                            User creation date                      
PROFILE                     VARCHAR2(30)                    User resource profile name              
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)   Y                User's initial consumer group           
EXTERNAL_NAME               VARCHAR2(4000) Y                User external name                      

（需要的表字段用紅色標(biāo)識(shí)）
SQL> select username,account_status from dba_users where username like 'S%';
 
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SYSMAN                         OPEN
SYS                            OPEN
SYSTEM                         OPEN
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
SCOTT                      EXPIRED & LOCKED
SH                             EXPIRED & LOCKED
注意scott用戶的account_status狀態(tài)
expired意思是用戶的密碼過(guò)期了。
Locked意思是用戶帳號(hào)已經(jīng)鎖定

1.2 解鎖用戶和處理用戶密碼過(guò)期

SQL> alter user scott identified by tiger account unlock;
 
User altered
 
SQL> select username,account_status from dba_users where username like 'S%';
 
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT                          OPEN
SYSMAN                         OPEN
SYS                            OPEN
SYSTEM                         OPEN
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
SH                             EXPIRED & LOCKED
 
6 rows selected
注意scott狀態(tài)已經(jīng)打開(kāi)
語(yǔ)句alter user scott identified by tiger account unlock；其中identified by tiger更新了密碼，account unlock 解鎖了scott用戶
注意狀態(tài)為OPEN狀態(tài)
以scott用戶登錄
SQL> conn scott/tiger
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0
Connected as scott

1.3查看用戶下的表（user_tables視圖）

SQL> desc user_tables;
Name                      Type         Nullable Default Comments                                                                                   
------------------------- ------------ -------- ------- ------------------------------------------------------------------------------------------
TABLE_NAME                VARCHAR2(30)                  Name of the table                                                                          
TABLESPACE_NAME           VARCHAR2(30) Y                Name of the tablespace containing the table                                                
CLUSTER_NAME              VARCHAR2(30) Y                Name of the cluster, if any, to which the table belongs                                    
IOT_NAME                  VARCHAR2(30) Y                Name of the index-only table, if any, to which the overflow or mapping table entry belongs
PCT_FREE                  NUMBER       Y                Minimum percentage of free space in a block                                                
PCT_USED                  NUMBER       Y                Minimum percentage of used space in a block                                                
INI_TRANS                 NUMBER       Y                Initial number of transactions                                                             
MAX_TRANS                 NUMBER       Y                Maximum number of transactions                                                             
INITIAL_EXTENT            NUMBER       Y                Size of the initial extent in bytes                                                        
NEXT_EXTENT               NUMBER       Y                Size of secondary extents in bytes                                                         
MIN_EXTENTS               NUMBER       Y                Minimum number of extents allowed in the segment                                           
MAX_EXTENTS               NUMBER       Y                Maximum number of extents allowed in the segment                                           
PCT_INCREASE              NUMBER       Y                Percentage increase in extent size                                                         
FREELISTS                 NUMBER       Y                Number of process freelists allocated in this segment                                      
FREELIST_GROUPS           NUMBER       Y                Number of freelist groups allocated in this segment                                        
LOGGING                   VARCHAR2(3)  Y                Logging attribute                                                                          
BACKED_UP                 VARCHAR2(1)  Y                Has table been backed up since last modification?                                          
NUM_ROWS                  NUMBER       Y                The number of rows in the table                                                            
BLOCKS                    NUMBER       Y                The number of used blocks in the table                                                     
EMPTY_BLOCKS              NUMBER       Y                The number of empty (never used) blocks in the table                                       
AVG_SPACE                 NUMBER       Y                The average available free space in the table                                              
CHAIN_CNT                 NUMBER       Y                The number of chained rows in the table                                                    
AVG_ROW_LEN               NUMBER       Y                The average row length, including row overhead                                             
AVG_SPACE_FREELIST_BLOCKS NUMBER       Y                The average freespace of all blocks on a freelist                                          
NUM_FREELIST_BLOCKS       NUMBER       Y                The number of blocks on the freelist                                                       
DEGREE                    VARCHAR2(10) Y                The number of threads per instance for scanning the table                                  
INSTANCES                 VARCHAR2(10) Y                The number of instances across which the table is to be scanned                            
CACHE                     VARCHAR2(5)  Y                Whether the table is to be cached in the buffer cache                                      
TABLE_LOCK                VARCHAR2(8)  Y                Whether table locking is enabled or disabled                                               
SAMPLE_SIZE               NUMBER       Y                The sample size used in analyzing this table                                               
LAST_ANALYZED             DATE         Y                The date of the most recent time this table was analyzed                                   
PARTITIONED               VARCHAR2(3)  Y                Is this table partitioned? YES or NO                                                       
IOT_TYPE                  VARCHAR2(12) Y                If index-only table, then IOT_TYPE is IOT or IOT_OVERFLOW or IOT_MAPPING else NULL         
TEMPORARY                 VARCHAR2(1)  Y                Can the current session only see data that it place in this object itself?                 
SECONDARY                 VARCHAR2(1)  Y                Is this table object created as part of icreate for domain indexes?                        
NESTED                    VARCHAR2(3)  Y                Is the table a nested table?                                                               
BUFFER_POOL               VARCHAR2(7)  Y                The default buffer pool to be used for table blocks                                        
ROW_MOVEMENT              VARCHAR2(8)  Y                Whether partitioned row movement is enabled or disabled                                    
GLOBAL_STATS              VARCHAR2(3)  Y                Are the statistics calculated without merging underlying partitions?                       
USER_STATS                VARCHAR2(3)  Y                Were the statistics entered directly by the user?                                          
DURATION                  VARCHAR2(15) Y                If temporary table, then duration is sys$session or sys$transaction else NULL              
SKIP_CORRUPT              VARCHAR2(8)  Y                Whether skip corrupt blocks is enabled or disabled                                         
MONITORING                VARCHAR2(3)  Y                Should we keep track of the amount of modification?                                        
CLUSTER_OWNER             VARCHAR2(30) Y                Owner of the cluster, if any, to which the table belongs                                   
DEPENDENCIES              VARCHAR2(8)  Y                Should we keep track of row level dependencies?                                            
COMPRESSION               VARCHAR2(8)  Y                Whether table compression is enabled or not                                                
DROPPED                   VARCHAR2(3)  Y                Whether table is dropped and is in Recycle Bin                                             
 
SQL> select table_name,tablespace_name from user_tables;
 
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SALGRADE                       USERS
BONUS                          USERS
EMP                            USERS
DEPT                           USERS

例子中主要適用EMP，DEPT倆個(gè)表

準(zhǔn)備結(jié)束。下一節(jié)開(kāi)始圍繞該表介紹我的orcle SQL菜鳥(niǎo)筆記