oracle11g数据库工作中常用的10条命令

1、在服务器上登录数据库

[ora12c@gac-oadev-db01 ~]$ sqlplus user01/user01pwd
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 7 15:42:18 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 

2、查询表dual

SQL> select 8*9 from dual;
8*9
----------
72

3、查询当前用户拥有哪些表

SQL> select count(*) from user_tables;
COUNT(*)
----------
0

4、创建表

SQL> create table test01(it int);
Table created.

5、向表里面插入数据，记得使用commit提交。

SQL> insert into test01 values (100);
1 row created.
SQL> commit;
Commit complete.

6、更新表中的某条数据，记得commit提交。

SQL> update test01 set it=200;
1 row updated.
SQL> commit;
Commit complete.

7、删除表中的某条数据，根据指定的条件，记得commit；

SQL> delete from test01 where it=200;
1 row deleted.
SQL> commit;
Commit complete.

8、sqlplus方式退出数据库的登录状态

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

9、查询表的定义和结构，使用desc即可：

[ora12c@gac-oadev-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 7 15:44:36 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT tablespace_name, used_percent
2 FROM dba_tablespaces
3 WHERE used_percent > 90;
WHERE used_percent > 90
*
ERROR at line 3:
ORA-00904: "USED_PERCENT": invalid identifier
SQL> desc dba_tablespaces;
Name Null? Type
----------------------------------------- -------- ----------------------------
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)

10、查询数据库所有表空间名称等信息，可以通过该命令查看表空间是否正常，是否ONLINE状态。

SQL> SELECT tablespace_name,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
AOLX ONLINE
APPSD ONLINE
AOLD ONLINE
AISSEMAIL ONLINE
AISSED ONLINE
AII ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
AITOSD ONLINE
AIPPSD ONLINE
AISSEI ONLINE
AISAFD ONLINE
AICLAD ONLINE
AIPCFD ONLINE
AISCAD ONLINE
AIMISD ONLINE
AIAIRD ONLINE
LKPCTD ONLINE
AIOMSD ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
AIEPSD ONLINE
AIEHSD ONLINE
COGNOS241D ONLINE
BUDGETD ONLINE
GOSALES_TS ONLINE
COGNOS241 ONLINE
AEMAILD ONLINE
AIOES5D ONLINE
AIARCD ONLINE
AIAMS ONLINE
AISAPP ONLINE
TABLESPACE_NAME STATUS
------------------------------ ---------
AIFAP ONLINE
AISSEEMAILD ONLINE
35 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[ora12c@gac-oadev-db01 ~]$