oracle学习笔记

lijian972

已于 2023-07-11 11:32:20 修改

阅读量64

点赞数

文章标签： oracle 学习笔记

于 2023-07-10 23:24:57 首次发布

本文链接：https://blog.csdn.net/lijian972/article/details/131648986

版权

本文详细介绍了在Oracle数据库中如何创建表空间，设置自动扩展，创建用户并指定默认表空间，以及对用户进行授权。同时，讲解了数据库的整库和按用户、按表的导出与导入操作，以及查看表空间使用情况和锁定表的相关信息。此外，还提供了检查数据文件路径、扩容数据文件的方法和获取系统时间戳的SQL语句。

摘要由CSDN通过智能技术生成

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