用户查询
查询和用户相关的数据
创建用户
CREATE USER user
IDENTIFIED BY password
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
查询所有用户数
select username from dba_users;
查询当前实例名称
select instance_name from v$instance;
表空间查询
查询用户表空间和临时表空间相关的数据
用户表空间
创建表空间
CREATE TABLESPACE tbs_01
DATAFILE ‘demo.dbf‘
SIZE 40M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M
ONLINE
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M
SEGMENT SPACE MANAGEMENT AUTO;
查询表空间总大小
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)" FROM DBA_DATA_FILES
查询表空间剩余大小
SELECT
tablespace_name,
SUM(bytes) / (1024 * 1024) AS free_space
FROM
dba_free_space
GROUP BY
tablespace_name;
SELECT
a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM
sys.sm$ts_avail a,
sys.sm$ts_used b,
sys.sm$ts_free c
WHERE
a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
临时表空间
查询临时表空间大小
SELECT
TABLESPACE_NAME,
FILE_ID,
FILE_NAME,
BYTES/1024/1024 AS "SPACE(M)"
FROM
DBA_TEMP_FILES
WHERE
TABLESPACE_NAME = ‘TEMP‘;
创建临时表空间
create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/orcl/temp11.dbf‘ size 10M;
缩小临时表空间大小
alter database datapfile ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\TELEMT\TEMP01.DBF‘ resize 100M;
扩展临时表空间：
增大临时文件大小：
alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf‘ resize 100m;
将临时数据文件设为自动扩展：
alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf‘ autoextend on next 5m maxsize unlimited;
向临时表空间中添加数据文件：
alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf‘ size 100m;
表查询
查询当前登录用户下所有表名称
select table_name from user_tables;
查询某个表的大小，替换为你的TABLE_NAME
SELECT
SEGMENT_NAME,
round(SUM(BYTES/1024/1024),2)||‘M‘ memory_space
FROM
dba_segments
WHERE
segment_name=‘TABLE_NAME‘
group by SEGMENT_NAME
查询当前登录用户所有表的大小
SELECT
SEGMENT_NAME,
ROUND(SUM(BYTES/1024/1024),2)||‘M‘ memory_space
FROM
dba_segments
WHERE
segment_name IN (SELECT table_name FROM user_tables)
GROUP BY
SEGMENT_NAME
ORDER BY memory_space desc;
查询当前用户下的分区表
select table_name from user_tables where partitioned=‘YES‘
查询某个表的分区数
SELECT table_name,partition_name from user_tab_partitions where table_name = ‘TABLE_NAME‘
查询某个分区的数据
SELECT * FROM MODULEEVALUATIONHISTORY_PART PARTITION(SYS_P43)
Oracle表和表空间查询
标签：临时文件default名称and数据lanromoracleeva
本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：/chsoul/p/12856489.html
