=Start=

缘由：

简单记录一下近期学习的Oracle数据库的简单操作方法，方便以后有需要的时候参考。

正文：

参考解答：

因为Oracle数据库和我们常见的MySQL数据库相比有些概念不太一样，概念的区别导致很多内容在习惯了MySQL的那一套之后不太容易理解Oracle的那套；再加上现在Oracle数据库对于我来说见的少、用的也少，所以只能是等后面有机会碰到了再学习、理解和记录了。

另外就是提一句，Oracle数据库的官方文档真的是很全面（国外商业产品这方面做的很好），通过阅读文档能解决你的绝大部分问题；如果你是付费用户，实在搞不定了直接找技术支持就行。不像大部分开源产品，文档几乎没有，网上的文章乱七八糟的，需要花很多的精力去筛选、验证（这都还不算代码的bug）。

• 数据库连接方法

# sqlplus命令格式
>sqlplus {username | /} [as sysdba]

# 直接输入 sqlplus 命令后面会要求交互式的输入用户名密码
C:\Users\Administrator>sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on 星期天 6月 5 16:32:02 2022

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

请输入用户名:  atest
输入口令:
上次成功登录时间: 星期天 6月  05 2022 16:31:01 +08:00

连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
断开

# 输入 sqlplus / as sysdba 命令表明使用操作系统认证方式(operating system authentication)
# 注意和上面相比，这次是没有要求输入用户名密码的
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期天 6月 5 20:31:30 2022

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> select ora_database_name from dual;

ORA_DATABASE_NAME
--------------------------------------------------------------------------------

TEST

SQL> select name from v$database;

NAME
------------------
TEST

SQL>
SQL> select user from dual;

USER
--------------------------------------------------------------------------------

SYS

SQL> conn atest as sysdba
输入口令:
已连接。
SQL>
SQL>

• 数据库常见查询

# 查看当前使用的数据库名称
select ora_database_name from dual;
select * from global_name;

# 查看当前的登录用户
select user from dual;

# 查看都有哪些用户
select username from all_users order by username;

# 查看都有哪些数据库
select name from v$database;

select * from dba_users; -- 返回的内容过多，对于新手不友好

# 查看当前数据库下都有哪些表
select tablespace_name from user_tablespaces;

SELECT owner, table_name FROM dba_tables WHERE owner = 'owner_name';
SELECT owner, table_name FROM dba_tables; --Viewing All Tables

SELECT owner, table_name FROM all_tables; --Viewing Tables Accessible by Current User

SELECT owner, table_name FROM user_tables; --Viewing Tables Owned by Current user

# 查看特定表的建表语句
select dbms_metadata.get_ddl('TABLE', 'table_name') from dual;
select dbms_metadata.get_ddl('TABLE', table_name) from user_tables;
describe table_name;

# 查看TABLESPACE的使用空间和剩余空间
SELECT a.tablespace_name "TABLESPACE_NAME", 
total "TOTAL", 
free "FREE", 
(total - free) "USED", 
total / (1024 * 1024 * 1024) "TOTAL(G)", 
free / (1024 * 1024 * 1024) "FREE(G)", 
(total - free) / (1024 * 1024 * 1024) "USED(G)", 
round((total - free) / total, 4) * 100 "USED %" 
FROM (SELECT tablespace_name, SUM(bytes) free 
FROM dba_free_space 
GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total 
FROM dba_data_files 
GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

# 查看特定TABLESPACE的表文件有哪些
SELECT tablespace_name, 
file_id, 
file_name, 
round(bytes/(1024*1024),0) total_space 
FROM dba_data_files 
where tablespace_name = 'USERS' 
ORDER BY tablespace_name;

参考链接：

Oracle数据库是什么？
https://www.w3cschool.cn/oraclejc/oraclejc-dxgu2qqt.html

Oracle 数据库连接
https://www.shulanxt.com/database/oracle/oraclejc-connectdb

3.8.3 Starting SQL*Plus and Connecting to the Database
https://docs.oracle.com/database/121/ADMQS/GUID-DE8A79BD-FAE4-4364-98FF-D2BD992A06E7.htm

Oracle Database 2 Day DBA
https://docs.oracle.com/database/121/ADMQS/GUID-6F9B427D-1298-4E42-A611-E7EA75FE018B.htm

1.3 Common Oracle DBA Tasks
https://docs.oracle.com/database/121/ADMQS/GUID-CC7F51A1-9F19-43CC-905B-C9E2006D7937.htm

Starting SQL*Plus and Connecting to the Database
https://docs.oracle.com/database/121/ADMQS/GUID-DE8A79BD-FAE4-4364-98FF-D2BD992A06E7.htm#ADMQS0361

Example 1-3 Connecting to a Local Database User
https://docs.oracle.com/database/121/ADMIN/dba.htm

How to Query Database Name in Oracle SQL Developer?
https://stackoverflow.com/questions/8978047/how-to-query-database-name-in-oracle-sql-developer

PLSQL, SQL*PLUS get current username?
https://stackoverflow.com/questions/23417522/plsql-sqlplus-get-current-username

How to display databases in Oracle 11g using SQL*Plus
https://stackoverflow.com/questions/3004171/how-to-display-databases-in-oracle-11g-using-sqlplus

Query to display all tablespaces in a database and datafiles
https://stackoverflow.com/questions/36209102/query-to-display-all-tablespaces-in-a-database-and-datafiles

DBA_TABLESPACES describes all tablespaces in the database.
https://docs.oracle.com/database/121/REFRN/GUID-B28A7D79-24E3-49B5-B948-7C2277CB1FB8.htm#REFRN23287

DBA_DATA_FILES describes database files.
https://docs.oracle.com/database/121/REFRN/GUID-0FA17297-73ED-4B5D-B511-103993C003D3.htm#REFRN23049

How to List All Tables in Oracle
https://chartio.com/resources/tutorials/how-to-list-all-tables-in-oracle/

Get list of all tables in Oracle?
https://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle

Find out free space on tablespace
https://stackoverflow.com/questions/7672126/find-out-free-space-on-tablespace

How to see list of databases in Oracle?
https://dba.stackexchange.com/questions/27725/how-to-see-list-of-databases-in-oracle

Difference between a user and a schema in Oracle?
https://stackoverflow.com/questions/880230/difference-between-a-user-and-a-schema-in-oracle

SYSDBA and SYSOPER System Privileges
https://docs.oracle.com/database/121/ADMQS/GUID-2033E766-8FE6-4FBA-97E0-2607B083FA2C.htm#ADMQS12004

How to get Oracle create table statement in SQL*Plus
https://stackoverflow.com/questions/937398/how-to-get-oracle-create-table-statement-in-sqlplus
https://stackoverflow.com/questions/26249892/how-can-i-generate-or-get-a-ddl-script-on-an-existing-table-in-oracle-i-have

Show the structure of the table in SQL
https://stackoverflow.com/questions/7356338/show-the-structure-of-the-table-in-sql

Oracle数据库中的账户的概念说明 7.1 About User Accounts
https://docs.oracle.com/database/121/ADMQS/GUID-434BA586-DDFC-4C37-B442-C9348C2853AE.htm#ADMQS071

Difference between Oracle and MySQL
https://www.geeksforgeeks.org/difference-between-oracle-and-mysql/

mysql和oracle的区别
https://zhuanlan.zhihu.com/p/39651803

Oracle and MySQL Compared
https://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm#CHDIIBJH

=END=