Oracle之SQL基础 1

课程视频：
Oracle数据库开发必备利器之SQL基础

登入数据库

F:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 11月 6 17:15:33 2019

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

请输入用户名:  system
输入口令:

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> connect sys
输入口令:
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


警告: 您不再连接到 ORACLE。

用户操作

查看登入用户

SQL> show user
USER 为 "SYSTEM"
SQL> --使用dba_user字典
SQL> desc dba_users;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------------

 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)

SQL> select username from dba_users;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
OUTLN
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS

USERNAME
------------------------------
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA

USERNAME
------------------------------
OLAPSYS
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
IX
SH
DIP
OE
APEX_PUBLIC_USER

USERNAME
------------------------------
HR
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR

已选择36行。

启用scott用户

SQL> alter user scott account unlock;

用户已更改。

SQL> --使用scott用户登入SQL Plus
SQL> --默认密码tiger
SQL> connect scott/tiger
已连接。
SQL> show user
USER 为 "SCOTT"

表空间

SQL> --查看用户的表空间
SQL> desc dba_tablespaces
ERROR:
ORA-04043: 对象 "SYS"."DBA_TABLESPACES" 不存在


SQL> conn system
输入口令:
已连接。
SQL> desc dba_tablespaces
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 MAX_SIZE                                           NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)
 RETENTION                                          VARCHAR2(11)
 BIGFILE                                            VARCHAR2(3)
 PREDICATE_EVALUATION                               VARCHAR2(7)
 ENCRYPTED                                          VARCHAR2(3)
 COMPRESS_FOR                                       VARCHAR2(12)

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
TEMPTEST1_TABLESPACE

已选择7行。

SQL> desc user_tablespaces
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NU