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;