Oracle 基础SQL

臭皮匠、

已于 2023-12-22 09:50:55 修改

阅读量849

点赞数 18

文章标签： sql 笔记数据库

于 2023-12-11 16:17:12 首次发布

本文链接：https://blog.csdn.net/m0_62160083/article/details/134380012

版权

前言：工作中的使用笔记心得，可以作为参考。当前使用的Oracle版本11.2.0.1.0，查询sql为

select * from v$version ;

1、表空间相关

2、用户相关

3、导入导出相关

4、常用SQL

5、其他

一、表空间：

Oracle表空间是一个逻辑存储单元，用于组织和管理Oracle数据库中的物理数据文件表空间就是存放表数据的地方，表空间包含了存储在其中的数据库对象，其中包含表、视图、索引、存储过程、函数和触发器等）

-- 1.1、创建新表空间。创建新表空间 Hong ，表空间文件名为 Hong01.dbf ，初始表空间大小为100M，超过大小后自动扩展，最小100M，最大1G。上限32G。（一个文件上限32G，一个表空间可以有多个表空间文件）

CREATE TABLESPACE Hong DATAFILE 'Hong01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G;

-- 1.2、查询默认表空间 USERS 文件所在的路径 (新增表空间文件使用)

select file_name from dba_data_files where tablespace_name = 'USERS';

-- 1.3、在 Hong 表空间新增表空间文件,默认100m,自动扩展，最小100m，最大1G

ALTER TABLESPACE Hong ADD DATAFILE '/home/oracle/app/oradata/orcl/Hong02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G;

-- 1.4、删除 Hong 表空间（删除表空间会删除表空间内的所有数据）

-- 将 Hong 表空间状态设置为 OFFLINE ，然后删除（启用为  ALTER TABLESPACE Hong ONLINE;）
ALTER TABLESPACE Hong OFFLINE;
DROP TABLESPACE Hong INCLUDING CONTENTS AND DATAFILES;

二、用户

-- 2.1、创建一个 Honguser 用户，密码为123456，并指定表空间为 Hong 。

CREATE USER Honguser IDENTIFIED BY "123456" ACCOUNT UNLOCK DEFAULT TABLESPACE Hong;

-- 2.2、授予 Honguser 用户 dba,CONNECT,RESOURCE 权限：

GRANT dba,CONNECT,RESOURCE TO Honguser;

-- 2.3、谨慎操作。删除 Honguser 用户，包含表及数据（需要登录非当前用户且有BDA权限）。

-- DROP USER Honguser CASCADE;

-- 扩展：针对oracle用户，查询 HONGUSER 用户下所有对象
SELECT OBJECT_NAME, OBJECT_TYPE, TABLESPACE_NAME FROM ALL_OBJECTS O LEFT JOIN DBA_SEGMENTS S ON O.OBJECT_NAME = S.SEGMENT_NAME AND O.OWNER = S.OWNER WHERE O.OWNER = 'HONGUSER';

三、Oracle数据导出导入（均是oracle用户下执行，不是sql命令）

-- 3.1、老版本使用的命令，exp导出，imp导入，owner是需要导出数据的用户名，full所有的意思。（导出的文件在all_directories表DIRECTORY_PATH的路径）

exp SYSTEM/123456@192.168.223.223:1521/orcl file=20231113.dmp owner=HONGUSER
imp NEW1/123456@192.168.223.223:1521/orcl file=EXP.dmp full=y

-- 3.2、数据泵方式，一般适用于11G及以上,（DATA_PUMP_DIR 来源于 SELECT * FROM all_directories; )，密码加双引号是防止有特殊字符不能识别，schemas就是用户对象，expdb导出

expdp 'SYSTEM/"123456"'@192.168.223.223:1521/orcl directory=DATA_PUMP_DIR dumpfile=NEW.dmp schemas=HONGUSER

-- 3.3、impdb导入，remap_schema=导出时用户名：导入时用户名，remap_tablespace=导出时表空间:导入时表空间，如果一个dmp文件导出时存在多个表空间，就多加几个remap_tablespace

impdp 'SYSTEM/"123456"'@192.168.223.223:1521/orcl directory=DATA_PUMP_DIR dumpfile=NEW.dmp remap_schema=HONGUSER:NEW1 remap_tablespace=HONG2:NEW remap_tablespace=HONG3:NEW remap_tablespace=eids_range:NEW

-- 3.4、只导出导入一张表，remap_schema=导出时用户名：导入时用户名

expdp 'HONGUSER/"123456"'@192.168.223.223:1521/orcl directory=DATA_PUMP_DIR dumpfile=baseorg_export.dmp tables=baseorg
impdp 'HONGUSER/"123456"'@192.168.223.223:1521/orcl directory=DATA_PUMP_DIR dumpfile=baseorg_export.dmp remap_schema=HONGUSER:USER01

四、常用SQL

-- 4.1、创建表：（创建一个user_table表，包含id，username，namecode，tel，EMAIL和CREATE_TIME六个字段，其中 not null 表示不能为空，CONSTRAINT pk_user_table_id PRIMARY KEY (ID) 意思是ID唯一性约束，这个表不能出现相同的两个ID）

CREATE TABLE USER_TABLE (
    ID NUMBER NOT NULL,
    USERNAME VARCHAR2(50),
    NAMECODE VARCHAR2(50),
    TEL VARCHAR2(20),
    EMAIL VARCHAR2(100),
    CREATE_TIME    TIMESTAMP(6),
    CONSTRAINT PK_USER_TABLE_ID PRIMARY KEY (ID)  );

-- 4.2、表加注释的SQL语句：

-- COMMENT ON TABLE 表名 IS '表注释';
-- 示例，给 user_table 表加上中文注释“用户表”。

COMMENT ON TABLE user_table IS '用户表';

-- 4.3、新增表字段并对表字段添加注释的SQL语句：

ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件];
COMMENT ON COLUMN 表名.字段名 IS '注释内容';

-- 例如，要在表 "user_table" 中添加 "test01 " 字段，数据类型为 "VARCHAR2(50)"，并设置为非空。再给"test01 " 字段添加注释 "测试01"。可以使用以下SQL语句：
ALTER TABLE USER_TABLE ADD TEST01 VARCHAR2(50) NOT NULL;
COMMENT ON COLUMN USER_TABLE.TEST01 IS '测试01';

-- user_table 表全量注释
COMMENT ON COLUMN USER_TABLE.ID IS 'UUID';
COMMENT ON COLUMN USER_TABLE.USERNAME IS '用户名称';
COMMENT ON COLUMN USER_TABLE.NAMECODE IS '用户code';
COMMENT ON COLUMN USER_TABLE.TEL IS '电话';
COMMENT ON COLUMN USER_TABLE.EMAIL IS '邮箱';
COMMENT ON COLUMN USER_TABLE.CREATE_TIME IS '创建时间';

-- 4.4、删除表字段的SQL语句：

ALTER TABLE 表名 DROP COLUMN 字段名;

-- 示例删除user_table的TEST01字段
ALTER TABLE user_table DROP COLUMN TEST01;

-- 4.5、增删改查

-- 1、新增
INSERT INTO USER_TABLE (ID, USERNAME, NAMECODE, TEL, EMAIL, CREATE_TIME)
VALUES ('202312', '张三', '511023', '13566778899', 'ZHANGSAN@OUTLOOK.COM', TO_DATE('2023-11-01 00:00:00', 'YYYY-MM-DD HH24.MI.SS'));

-- 2、根据ID删除刚刚创建的数据
DELETE FROM USER_TABLE WHERE ID = '202312';

-- 3、修改，根据ID来查找出刚刚新增的数据，然后修改用户名为 李四
UPDATE USER_TABLE SET NAME = '李四' WHERE ID = '202312';

-- 4、查询 user_table 表所有数据：
SELECT t.* FROM user_table t ;
-- 4.2、根据条件精确查询某数据,且只展示id列和username列，其他列不展示
SELECT t.id, t.username FROM user_table t WHERE t.username = '条件 可以是用户姓名';

-- 扩展：删除表中所有数据，保留表的结构和定义，只能用于表。
TRUNCATE TABLE USER_TABLE ;  -- 操作前请备份

-- 4.6、查询锁表并强制解除锁表

--查询锁表
SELECT
    b.sid AS "会话ID",              -- 会话ID
    b.serial# AS "会话序列号",      -- 会话序列号
    c.owner AS "所有者",            -- 表的所有者
    c.object_name AS "对象名称",    -- 表名
    b.status AS "会话状态",          -- 会话状态
    b.osuser AS "操作系统用户",      -- 操作系统用户
    b.machine AS "连接数据库的机器名"  -- 连接数据库的机器名
FROM v$locked_object a              -- 包含被锁对象的信息
JOIN v$session b ON a.session_id = b.sid  -- 包含会话信息
JOIN all_objects c ON a.object_id = c.object_id;  -- 包含对象信息

--强制解除锁表
ALTER SYSTEM KILL SESSION 'sid就是会话ID,serial#就是会话序列号' IMMEDIATE;

五、其他（用的很少的）

-- 5.1、表空间查询，新建，删除

--  1.1、查询表空间使用情况 sql 1
SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名",
       D.TOT_GROOTTE_MB AS "表空间大小(G)",
       D.NUM_FILES AS "文件数量",
       D.AUTO_EXTENSIBLE AS "自动扩展",
       D.MAX_SIZE_GB AS "最大大小(G)",
       D.FILE_PATH AS "表空间文件全路径",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES AS "已使用空间(G)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' AS "使用比",
       F.TOTAL_BYTES AS "空闲空间(G)",
       F.MAX_BYTES AS "最大块大小(G)"
  FROM (SELECT DD.TABLESPACE_NAME,
               COUNT(*) AS NUM_FILES,
               DECODE(SUM(DECODE(DD.AUTOEXTENSIBLE, 'YES', 1, 0)), COUNT(*), 'YES', 'NO') AS AUTO_EXTENSIBLE,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) AS TOT_GROOTTE_MB,
               MAX(ROUND(DD.MAXBYTES / (1024 * 1024 * 1024), 2)) AS MAX_SIZE_GB,
               LISTAGG(DD.FILE_NAME) 
               WITHIN GROUP(ORDER BY DD.TABLESPACE_NAME) AS FILE_PATH
          FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) AS TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 2) AS MAX_BYTES
          FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;

--  1.2、查询表空间使用情况 sql 2
SELECT UPPER(F.TABLESPACE_NAME) AS "表空间名",
       D.TOT_GROOTTE_MB AS "表空间大小(G)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES AS "已使用空间(G)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') || '%' AS "使用比",
       F.TOTAL_BYTES AS "空闲空间(G)",
       F.MAX_BYTES AS "最大块大小(G)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) AS TOTAL_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 2) AS MAX_BYTES
          FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 2) AS TOT_GROOTTE_MB
          FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1;



-- 2、创建新表空间
-- 2.1、创建新表空间 Hong ，表空间文件名为 Hong01.dbf ，初始表空间大小为100M，超过大小后自动扩展，最小100M，最大1G。上限32G。（一个文件上限32G，一个表空间可以有多个表空间文件）
CREATE TABLESPACE Hong DATAFILE 'Hong01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G;

-- 2.2、查询默认表空间 USERS 文件所在的路径 (新增表空间文件使用)          
select file_name from dba_data_files where tablespace_name = 'USERS';

-- 2.3、在 Hong 表空间新增表空间文件,默认100m,自动扩展，最小100m，最大1G        
ALTER TABLESPACE Hong ADD DATAFILE '/home/oracle/app/oradata/orcl/Hong02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 1G;

-- 2.4、将 Hong 表空间状态设置为 OFFLINE （ALTER TABLESPACE Hong ONLINE;）
ALTER TABLESPACE Hong OFFLINE;
-- 2.5、删除 Hong 表空间 
-- DROP TABLESPACE Hong INCLUDING CONTENTS AND DATAFILES;

-- 5.2、创建时间分区

-- 建表时候创建天分区
-- DROP TABLE USER_TABLE;
CREATE TABLE USER_TABLE (
    ID NUMBER NOT NULL,
    USERNAME VARCHAR2(50),
    NAMECODE VARCHAR2(50),
    TEL VARCHAR2(20),
    EMAIL VARCHAR2(100),
    CREATE_TIME    TIMESTAMP(6) )
PARTITION BY RANGE (CREATE_TIME) INTERVAL ( NUMTODSINTERVAL (1, 'DAY') )
(PARTITION P2023MONTH01 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')));
-- USER_TABLE 表的 CREATE_TIME 列创建索引
CREATE INDEX CREATE_TIME ON USER_TABLE (CREATE_TIME);
-- USER_TABLE 表的 ID 列创建主键约束
ALTER TABLE USER_TABLE  ADD PRIMARY KEY (ID)  USING INDEX ;

-- 如果需要更改分区时间，将  NUMTODSINTERVAL (1, 'DAY')  替换为如下时间即可。
 NUMTODSINTERVAL (1, 'DAY')    转换为1天的时间间隔。
 NUMTODSINTERVAL (7, 'DAY')    转换为7天的时间间隔。
 NUMTOYMINTERVAL (1, 'MONTH')    转换为1个月的时间间隔。
 NUMTOYMINTERVAL (1, 'YEAR')    转换为1年的时间间隔。

-- 5.3、表空间问题，需要移动表空间使用该sql

--0、表空间问题，将当前用户移动到别的表空间sql语句：
-- 0.2、oracle查询 Hong 表空间都有那些对象再使用
SELECT owner,segment_name,segment_type,tablespace_name FROM dba_segments  WHERE  tablespace_name = 'Hong';

-- 0.2、将用户 Honguser 的默认表空间更改为 Hong；
ALTER USER Honguser DEFAULT TABLESPACE Hong;
-- 0.3、查询用户 Honguser 使用的默认表空间。
SELECT username, default_tablespace FROM dba_users WHERE username = 'Honguser';

-- 0.4、查询 Hong 表空间的表，生成移动 表 到 Hong01 表空间的 SQL 语句
SELECT 'ALTER TABLE ' || table_name || ' MOVE TABLESPACE Hong01;' AS sql_statement FROM user_tables WHERE tablespace_name = 'Hong';
-- 0.5、查询 Hong 表空间的 LOB段，生成移动 LOB段 到 Hong01 表空间的 SQL 语句
SELECT 'ALTER TABLE ' || table_name || ' MOVE LOB(' || column_name || ') STORE AS (TABLESPACE Hong01);' AS sql_statement FROM user_lobs WHERE tablespace_name = 'Hong';
-- 0.6、查询 Hong 表空间的 LOB索引，生成移动 LOB索引 到 Hong01 表空间的 SQL 语句
SELECT 'ALTER TABLE ' || table_name || ' MOVE LOB(' || column_name || ') STORE AS (TABLESPACE Hong01 (TABLESPACE Hong01));' AS sql_statement FROM user_lobs WHERE tablespace_name = 'Hong';
-- 0.7、查询 Hong 表空间的 索引，生成移动 索引 到 Hong01 表空间的 SQL 语句
SELECT 'ALTER INDEX ' || index_name || ' REBUILD TABLESPACE Hong01;' AS sql_statement FROM user_indexes WHERE tablespace_name = 'Hong';

-- 5.4、删除用户下的对象

-- 查询用户下所有表
SELECT table_name FROM user_tables;
  -- 删除表：
DROP table your_view_name;

-- 查询用户下所有视图
SELECT object_name FROM user_objects WHERE object_type = 'VIEW';
  -- 删除视图：
DROP VIEW your_view_name;

-- 查询用户下所有索引
SELECT index_name FROM user_indexes;
  -- 删除索引：
DROP INDEX your_index_name;

-- 查询用户下所有存储过程
SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE';
  -- 删除存储过程：
DROP PROCEDURE your_procedure_name;

-- 查询用户下所有函数
SELECT object_name FROM user_objects WHERE object_type = 'FUNCTION';
  -- 删除函数：
DROP FUNCTION your_function_name;

-- 查询用户下所有触发器
SELECT trigger_nameFROM user_triggers;
  -- 删除触发器：
DROP TRIGGER your_trigger_name;