Oracle数据库小白备忘
sqlplus相关
- 导入sql文件
在sqlplus中，导入一个sql文件，是使用@或者start。
如当前目录下有一个hello.sql，则可以使用
@hello.sql
或者
start hello.sql
来进行导入，功能类似于mysql里面的source。
- 退出编辑模式
当使用sqlplus编写存储过程，或者触发器，sqlplus会进入一种编辑模式，即前面一个行号，后面是一个输入区域。
退出这个编辑模式的字符是’/'。其它各种诸如EXIT、QUIT、Ctrl-C、Ctrl-D等都不行，只有/可以退出来。
帐号相关
- 创建帐号、授权
Oracle的权限角色很多，其中CONNECT和RESOURCE是对数据库进行增删改查的角色，SELECT_CATALOG_ROLE可以查询一些数据库的配置信息。
CREATE USER hello IDENTIFIED BY world;
GRANT CONNECT, RESOURCE SELECT_CATALOG_ROLE TO hello;
可以使用如下命令查询当前帐号的角色
select granted_role from user_role_privs;
还可以切换到sysdba，查询所有的角色，以及特定用户的角色。
查询所有可以使用的角色
select role from dba_roles;
查询特定用户的角色
select granted_role from dba_role_privs where grantee=‘HELLO’;
注意上面的grantee=后面的帐号需要大写
- 删除帐号
Oracle删除帐号可以加上CASCADE，即把帐号下面的表、视图等数据库对象都删除掉。
DROP USER hello CASCADE;
否则，就需要先删除帐号下面的所有数据库对象，之后再删除帐号。
- 帐号解锁
查看帐号锁定状态
select username,account_status,lock_date from dba_users where username=‘READER’;
解锁
alter user READER account unlock;
注意：在Oracle数据库中，username一列存储的值是大写的。做where查询的时候，需要注意。
Debezium相关
如果使用Debezium获取数据库的变更，用户除了要有CONNECT、RESOURCE角色以外，还需要有对数据库配置信息以及logminer相关的一些角色。
Debezium的官方文档有一个列表，如下：
sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdbaCREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/logminer_tbs.dbf'SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;exit;sqlplus sys/top_secret@//localhost:1521/ORCLPDB1 as sysdbaCREATE TABLESPACE logminer_tbs DATAFILE '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/logminer_tbs.dbf'SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;exit;sqlplus sys/top_secret@//localhost:1521/ORCLCDB as sysdbaCREATE USER c##dbzuser IDENTIFIED BY dbzDEFAULT TABLESPACE logminer_tbsQUOTA UNLIMITED ON logminer_tbsCONTAINER=ALL;GRANT CREATE SESSION TO c##dbzuser CONTAINER=ALL; GRANT SET CONTAINER TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$DATABASE to c##dbzuser CONTAINER=ALL; GRANT FLASHBACK ANY TABLE TO c##dbzuser CONTAINER=ALL; GRANT SELECT ANY TABLE TO c##dbzuser CONTAINER=ALL; GRANT SELECT_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; GRANT EXECUTE_CATALOG_ROLE TO c##dbzuser CONTAINER=ALL; GRANT SELECT ANY TRANSACTION TO c##dbzuser CONTAINER=ALL; GRANT LOGMINING TO c##dbzuser CONTAINER=ALL; GRANT CREATE TABLE TO c##dbzuser CONTAINER=ALL; GRANT LOCK ANY TABLE TO c##dbzuser CONTAINER=ALL; GRANT CREATE SEQUENCE TO c##dbzuser CONTAINER=ALL; GRANT EXECUTE ON DBMS_LOGMNR TO c##dbzuser CONTAINER=ALL; GRANT EXECUTE ON DBMS_LOGMNR_D TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$LOG TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$LOG_HISTORY TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$LOGMNR_LOGS TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$LOGMNR_PARAMETERS TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$LOGFILE TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$ARCHIVED_LOG TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$ARCHIVE_DEST_STATUS TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$TRANSACTION TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$MYSTAT TO c##dbzuser CONTAINER=ALL; GRANT SELECT ON V_$STATNAME TO c##dbzuser CONTAINER=ALL; exit;
数据相关
- 显示用户下的数据表
select table_name from user_tables;
- 批量创建数据表
BEGIN FOR i IN 1..5 LOOP -- 创建5个表 EXECUTE IMMEDIATE 'CREATE TABLE test_table_' || i || ' ( id NUMBER GENERATED BY DEFAULT AS IDENTITY, name VARCHAR2(100), age NUMBER, address VARCHAR2(255), created_at TIMESTAMP, clob_field CLOB, blob_field BLOB, number_field NUMBER, date_field DATE, varchar_field VARCHAR2(200), PRIMARY KEY (id) )'; END LOOP;
END;
/
- 批量插入测试数据
BEGIN-- 对每个表进行插入FOR i IN 1..5LOOP-- 插入100万条数据FOR j IN 1..1000000LOOPDECLAREl_blob BLOB;l_clob CLOB;BEGIN-- 初始化BLOB和CLOBDBMS_LOB.CREATETEMPORARY(l_blob, TRUE);DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);-- 填充CLOB字段DBMS_LOB.WRITEAPPEND(l_clob, LENGTH('This is a CLOB field for record ' || j),'This is a CLOB field for record ' || j);-- 填充BLOB字段（插入10K个字节的随机数据）FOR k IN 1..10000LOOPDBMS_LOB.WRITEAPPEND(l_blob, 1, CHR(MOD(DBMS_RANDOM.VALUE(0, 256), 256)));END LOOP;-- 插入数据EXECUTE IMMEDIATE 'INSERT INTO test_table_' || i || ' (name, age, address, created_at, clob_field, blob_field, number_field, date_field, varchar_field) VALUES (''Name '' || ' || j || ',' || MOD(j, 100) || ',''Address '' || ' || j || ',SYSTIMESTAMP,:clob_data,:blob_data,' || MOD(j, 1000) || ',SYSDATE,''Varchar value '' || ' || j || ')' USING l_clob, l_blob;-- 释放临时LOBDBMS_LOB.FREETEMPORARY(l_blob);DBMS_LOB.FREETEMPORARY(l_clob);END;END LOOP;END LOOP;
END;
/
- 查询数据库的dbid
select dbid from v$database;
维护相关
- 确定删除归档日志文件
不再需要的归档日志，可以清理掉，节省磁盘空间。
使用sqlplus查询归档日志
select name, completion_time, status from v$archived_log;
对归档文件进行操作前，先关闭数据库实例
shutdown immediate
进入命令行，删除不使用的归档日志
rm -rf ARCHI*
重新启动
startup
使用rman备份工具，清除失效的archivelog
connect target /
crosscheck archivelog all;
delete expired archivelog all;
另外，可以使用这些sql语句更改archivelog配置
alter system set db_archive_file_dest_size=50G;
shutdown immediate;
startup;
show parameter db_recorver;