前言:工作中的使用笔记心得,可以作为参考。当前使用的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;