Scott's Blog

学则不固, 知则不惑

0%

Oracle 基本信息速查

发表于 分类于 阅读次数： Valine：

一些关于 Oracle 基本信息查询的常用 sql。

数据库信息

1
2
3
4
5
6
7
8
-- 查询数据库版本
SELECT * FROM v$version;

-- 查询当前数据库名
select name from v$database;

-- 查询当前数据库实例名
select instance_name from v$instance;

表空间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 表空间信息
-- 参考: https://techgoeasy.com/how-to-check-tablespace-in-oracle-database/
-- 查看表空间
SELECT TABLESPACE_NAME "TABLESPACE", EXTENT_MANAGEMENT,FORCE_LOGGING,
BLOCK_SIZE,
SEGMENT_SPACE_MANAGEMENT
FROM DBA_TABLESPACES;

-- 查看表空间文件地址
SELECT
FILE_NAME, 
BLOCKS, 
TABLESPACE_NAME
FROM DBA_DATA_FILES;

-- 检查表空间文件大小，输入表空间名字
-- 结果为 MB
Select (sum(bytes)/1024/1024) Space_allocated
from dba_data_files
where tablespace_name=upper('&tname');

Schema 与用户

1
2
3
4
5
6
7
8
-- Schema 与用户的区别
-- A schema is a collection of database objects (used by a user.). 
-- Schema objects are the logical structures that directly refer to the database’s data.
-- A user is a name defined in the database that can connect to and access objects. 
-- Schemas and users help database administrators manage database security.
-- 一个用户有一个缺省的schema，其schema名就等于用户名，当然一个用户还可以使用其他的schema。
-- 查用户信息，包括 schema
SELECT * FROM sys.dba_users;
1
2
3
4
5
6
7
8
-- 查看所有用户
select * from dba_users; 
select * from all_users; 
select * from user_users;

-- 查看用户或角色系统权限
select * from dba_sys_privs; 
select * from user_sys_privs; (查看当前用户所拥有的权限)

如何查询Oracle中所有用户信息

查索引

1
2
3
4
5
6
7
8
9
10
11
12
13
-- user index
select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from USER_INDEXES

-- all index
select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES

-- all index filter by table name
select INDEX_NAME, TABLE_OWNER, TABLE_NAME, UNIQUENESS from ALL_INDEXES
where table_owner like '%DC%'

-- all index group by table owner
select table_owner, COUNT(TABLE_OWNER) from ALL_INDEXES
group by table_owner;

表与视图

有哪些表和视图

1
2
3
4
5
6
7
8
9
10
11
12
-- list all user's table
select table_name from all_tables order by table_name

-- list all views
select view_name from all_views where view_name like 'GRA%' order by view_name;

-- check table length
select table_name, num_rows from all_tables;

-- create table from another view or table
create table prs7.t_gra_stores_mapping_dc as (select * from prs4.GRA_STORE_MAPPING_INFO_DC);

检查表占用空间

1
2
3
4
5
select
segment_name,
sum(bytes)/1024/1024/1024 GB 
from user_segments 
where segment_type='TABLE' and segment_name=upper('&TABLE_NAME') group by segment_name;

查看执行计划

1
2
3
4
explain plan for
select a, b, c
from table_name
select * from TABLE(dbms_xplan.display);

Oracle 文档阅读