1.创建表空间
create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
autoextend on
next 10m
解释:
waterboss 为表空间名称
datafile 用于设置物理文件名称
size 用于设置表空间的初始大小
autoextend on 用于设置自动增长,如果存储量超过初始大小,则开始自动扩容
next 用于设置扩容的空间大小
2.创建用户
create user wateruser
identified by itcast
default tablespace waterboss
wateruser 为创建的用户名
identified by 用于设置用户的密码
default tablesapce 用于指定默认表空间名称
3.用户赋权
grant dba to wateruser
给用户 wateruser 赋予 DBA 权限后即可登陆
4.整库导出与导入
整库导出命令
exp system/itcast full=y
添加参数 full=y 就是整库导出
执行命令后会在当前目录下生成一个叫 EXPDAT.DMP,此文件为备份文件。
如果想指定备份文件的名称,则添加 file 参数即可,命令如下
exp system/itcast file=文件名 full=y
整库导入命令
imp system/itcast full=y
此命令如果不指定 file 参数,则默认用备份文件 EXPDAT.DMP 进行导入
如果指定 file 参数,则按照 file 指定的备份文件进行恢复
imp system/itcast full=y file=water.dmp
IMP hn_yszx230321/1@10.104.65.181:1521/orcl file=f:\hn_yszx230602.dmp full=y
5.按用户导出与导入
按用户导出
exp system/itcast owner=wateruser file=wateruser.dmp
exp hn_yszx230321/1@10.104.65.181:1521/orcl file=f:\hn_yszx230602.dmp log=f:\hn_yszx230602.log full=y
按用户导入
imp system/itcast file=wateruser.dmp fromuser=wateruser
6.按表导出与导入
按表导出
exp wateruser/itcast file=a.dmp tables=t_account,a_area
用 tables 参数指定需要导出的表,如果有多个表用逗号分割即可
按表导入
imp wateruser/itcast file=a.dmp tables=t_account,a_area
7.查看表空间的使用情况
select
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from
(select tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3 from dba_tablespaces) c
where a.a1=b.b1 and c.c1=b.b1;
8.查看数据库所有表空间
备注:来源来源
select tablespace_name, sum(bytes)/1024/1024 proportion from dba_data_files group by tablespace_name;
9.查看具体表单所占空间
Select Segment_Name,Sum(bytes)/1024/1024 proportion From User_Extents Group By Segment_Name
10.查看所有表空间占用率
SELECT
c.ts#, c.name,
d.contents, d.extent_management,
e.file_bytes, c.used,
SUBSTR (c.used / e.file_bytes * 100, 1, 5) proportion --占比
FROM (SELECT name, ts#, SUM(used) used
FROM (SELECT a.allocated_space * (SELECT value -- 查询db_block_size当前值
FROM v$parameter
WHERE name = 'db_block_size') / 1024/ 1024 used,
b.ts#, b.name
FROM v$filespace_usage a, v$tablespace b
WHERE a.tablespace_id = b.ts#)
GROUP BY name, ts#
) c,dba_tablespaces d,
(SELECT ts#, SUM(bytes) / 1024/ 1024 file_bytes FROM v$datafile GROUP BY ts#) e
WHERE c.name = d.tablespace_name
AND e.ts# = c.ts#
ORDER BY ts#
11.查看具体dbf表空间占用率
sql1
select b.file_id,/*文件ID*/
b.tablespace_name,/*表空间*/
b.file_name,/*物理文件名*/
b.bytes,/*总字节数*/
(b.bytes-sum(nvl(a.bytes,0))) Used,/*已使用*/
sum(nvl(a.bytes,0)) surplus,/*剩余*/
sum(nvl(a.bytes,0))/(b.bytes)*100 proportion/*剩余百分比*/
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes
order by b.tablespace_name
sql2
SELECT a.tablespace_name "表空间名",
a.bytes / 1024 / 1024 "表空间大小(M)",
(a.bytes - b.bytes) / 1024 / 1024 "已使用空间(M)",
b.bytes / 1024 / 1024 "空闲空间(M)",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "使用比"
FROM (SELECT tablespace_name, sum(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) bytes, max(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY ((a.bytes - b.bytes) / a.bytes) DESC;
12.检查数据文件路径
Select * From dba_data_files t where t.TABLESPACE_NAME = 'CREAMMINT';
SELECT file_name,
tablespace_name,
bytes / 1024 / 1024 "bytes MB",
maxbytes / 1024 / 1024 "maxbytes MB"
FROM dba_data_files
WHERE tablespace_name = 'USER_DATA';
13.追加数据文件
Alter Tablespace CREAMMINT Add Datafile '/data3/oradata/creammint/creammint001.dbf' SIZE 20G;
alter tablespace GRP add datafile '/u01/app/oracle/oradata/ORCL/grp.dbf' size 35g
CREAMMINT和GRP 是tablespace。
14.扩容原数据文件
alter database datafile '/ssddata/oradata/creammint/creammint001.dbf' RESIZE 20G;
15.获取类似java中系统时间戳毫秒
方式1:
WITH TIME AS
(SELECT SYSTIMESTAMP(3) - TO_TIMESTAMP('1970-1-1 8', 'YYYY-MM-DD HH24') T
FROM DUAL)
SELECT EXTRACT(DAY FROM T) * 86400000 + EXTRACT(HOUR FROM T) * 3600000 +
EXTRACT(MINUTE FROM T) * 60000 + EXTRACT(SECOND FROM T) * 1000 AS MILLIONS
FROM TIME;
方式二
SELECT (TRUNC(SYSDATE, 'MI') - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) *
86400000 + EXTRACT(SECOND FROM SYSTIMESTAMP(3)) * 1000 AS MILLIONS
FROM DUAL;
方式三
SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 +
TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) AS MILLIONS
FROM DUAL;
备注来源:(https://blog.csdn.net/u012665434/article/details/79558666/)
16.查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode、以及解锁
SELECT l.session_id sid,
s.serial#,
l.locked_mode as 锁模式,
l.oracle_username as 登录用户,
l.os_user_name as 登录机器用户名,
s.machine as 机器名,
'ALTER SYSTEM KILL SESSION ''' || SESSION_ID || ', ' || SERIAL# || '''; ' as 关闭会话sql,
s.terminal as 终端用户名,
o.object_name as 被锁对象名,
s.logon_time as 登录数据库时间
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#;
17.liunx的oracle通过xshell连接登录oracle
--登录数据库
1.su - oracle --切换成oracle用户
2.lsnrctl start lsnrctl status lsnrctl stop --启动 查看 停止监听
3.export ORACLE_SID=orcl --设置示例名
4.sqlplus /nolog --sqlplus窗口
5.conn sys/ as sysdba --dba用户登录 需要输入密码
6.startup --启动oracle