![](https://img-blog.csdnimg.cn/20201014180756757.png?x-oss-process=image/resize,m_fixed,h_64,w_64)
数据库
文章平均质量分 70
每天努力一点点,哈哈
本人从事酒店it,所以接触到oracle产品。后来发现,我没有oracle support账号,好多问题自己无法解决。还是放弃吧。回到最初我玩的三维软件吧,本人从Maya7开始接触,后来就没从事相关工作,现在捡起来从新玩玩。
展开
-
db19序列
【代码】db19序列。原创 2023-05-07 08:53:20 · 82 阅读 · 0 评论 -
表空间相关命令
表空间相关命令。原创 2023-04-16 13:57:18 · 395 阅读 · 0 评论 -
db19密钥库和加密
PDB重新启动,则必须先在PDB中打开密钥库,然后才能访问数据。重新启动CDB,则必须在CDB和PDB 中打开密钥库。在根容器中创建并激活一个主密钥。使用 TDE 的密钥库。原创 2023-03-22 17:01:09 · 331 阅读 · 0 评论 -
PL SQL(变量)
Declarev_test VARCHAR2(10);BEGINv_test := 'Li Hao';dbms_output.put_line(v_test);END;使用 SELECT INTO 语句来初始化变量DECLAREv_salary number(5);BEGINselect salary into v_salary from employeeswhere employee_id=100;dbms_output.put_line(v_sal原创 2021-11-14 15:09:18 · 563 阅读 · 0 评论 -
oracle官方例子 面向开发人员3 性能
alter session set WORKAREA_SIZE_POLICY = auto;create table colours as select 'red' colour from dual union all select 'blue' colour from dual union all select 'green' colour from dual union all select 'orange' colour from dual...原创 2021-11-04 15:28:44 · 99 阅读 · 0 评论 -
oracle官方例子 面向开发人员2 表统计
create table bricks ( brick_id integer not null primary key, colour_rgb_value varchar2(10) not null, shape varchar2(10) not null, weight integer not null);create table colours ( colour_rgb_value varchar2(10...原创 2021-11-04 13:26:34 · 159 阅读 · 0 评论 -
oracle官方例子 面向开发人员1 获取和读取执行计划
里面的中文是网页翻译的准备create table bricks ( colour varchar2(10), shape varchar2(10));create table colours ( colour varchar2(10), rgb_hex_value varchar2(6));create table cuddly_toys ( toy_name varchar2(20), colour varc...原创 2021-11-04 09:57:43 · 122 阅读 · 0 评论 -
资源管理器 PDB 性能配置文件
DECLARE l_plan VARCHAR2(30) := 'test_cdb_prof_plan';BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.create_cdb_plan( plan => l_plan, comment => 'A test...原创 2021-09-06 17:25:08 · 327 阅读 · 0 评论 -
PDB锁定配置文件
创建了两个 PDB 锁定配置文件CREATE LOCKDOWN PROFILE default_pdb_lockdown;CREATE LOCKDOWN PROFILE pdb2_specfic_lockdown;添加一些限制ALTER LOCKDOWN PROFILE default_pdb_lockdown DISABLE FEATURE = ('NETWORK_ACCESS');ALTER LOCKDOWN PROFILE pdb2_specfic_lockdown DIS原创 2021-09-05 15:17:09 · 143 阅读 · 0 评论 -
扩展字符串(ORA-00910)
show PARAMETERS max_string_sizealter system set max_string_size=extended;ls -l $ORACLE_HOME/rdbms/admin/utl32k.sql@$ORACLE_HOME/rdbms/admin/utl32k.sqlshow PARAMETERS max_string_sizeselect table_name,column_name,securef...原创 2021-09-02 14:30:49 · 450 阅读 · 0 评论 -
sql查询时不区分大小写查到方法
测试的表进行查询select * from t2 where xingming='LiHao';select * from t2 where xingming='Lihao';alter session set nls_sort=generic_baseletter;alter session set nls_comp=ansi;select * from t2 where xingming='Lihao';select * from t2 ...原创 2021-09-02 11:39:31 · 2339 阅读 · 0 评论 -
表的在线移动表空间
ALTER SESSION SET CONTAINER=pdb2;CREATE TABLESPACE users DATAFILE '/u02/oradata/CDB1/pdb2/users01.dbf'SIZE 100M AUTOEXTEND ON NEXT 10M;SELECT tablespace_name FROM dba_tablespaces;SELECT property_valueFROM database_propertiesWHERE property_na.原创 2021-09-02 09:35:04 · 150 阅读 · 0 评论 -
db19解锁scott用户
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY aabb123 FILE_NAME_CONVERT=('/u02/oradata/CDB1/pdbseed', '/u02/oradata/CDB1/pdb2');ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;ALTER PLUGGABLE DATABASE pdb2 SAVE STATE;ALTER SES..原创 2021-09-01 15:13:51 · 58 阅读 · 0 评论 -
SQL执行计划
INSERT INTO test_1 VALUES ( 2 , 'Val 2' ) ;INSERT INTO test_1 VALUES ( 1 , '99999' ) ;INSERT INTO test_1 VALUES ( 3 , 'Val 3' ) ;INSERT INTO test_1 VALUES ( 4 , 'Val 4' ) ;COMMIT打开执行计划Set autotrace on;在次进行查询创建索引...原创 2021-08-31 11:22:38 · 52 阅读 · 0 评论 -
RMAN使用日志序列号恢复重做日志文件(op-db)
backup database plus archivelog;list backup of database;select group#,sequence#,bytes/1024/1024,members,status from v$log;UNUSED:从未写入在线重做日志。这是刚刚添加的重做日志的状态CURRENT:当前重做日志。这意味着重做日志处于活动状态。重做日志可以打开或关闭。ACTIVE:日志处于活动状态但不是当前日志。它是崩溃恢复所必需的。它可能用于...原创 2021-08-24 17:38:09 · 177 阅读 · 0 评论 -
RMAN0和1级备份 数据库时间点恢复(opera db)
查看现在备份list backup of database summary;进行0级备份backup incremental level 0 database plus archivelog;查看备份list backup of database summary;select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; 2021-08-23 17:10:04创建测试用户CR...原创 2021-08-24 09:46:48 · 310 阅读 · 0 评论 -
数据库备份rman和恢复数据文件(opera db)
list backup of database summary;list backup;创建备份backup database plus archivelog;TAG20210821T144631创建测试用户和表CREATE TABLE departments ( department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, department_name VARCHA...原创 2021-08-23 14:56:03 · 157 阅读 · 1 评论 -
pfile和spfile(opera数据库)
show parameter spfile;show parameter pfile;create spfile from pfile;create pfile from spfile;生成位置spfile启动select decode(count(*),1,'spfile','pfile')from v$spparameter where rownum=1 and isspecified ='TRUE';使用pfile启动startu...原创 2021-08-22 13:34:32 · 154 阅读 · 0 评论 -
冷备份和恢复,痛苦的控制文件恢复(opera数据库)
开启归档SHUTDOWN IMMEDIATEstartup mountalter database archivelog;select name from v$controlfile;SELECT name from v$datafile;D:\ORACLE\ORADATA\OPERA\CONTROL01.CTLC:\ORACLE\ORADATA\OPERA\CONTROL02.CTLshow parameter spfile...原创 2021-08-19 17:40:38 · 116 阅读 · 0 评论 -
自定义SQL提示样子
显示实例set sqlprompt "_connect_identifier"显示用户set sqlprompt "_user>"set sqlprompt "&_zidingyi>"显示版本信息set sqlprompt "_o_version"显示用户和实例set sqlprompt "&_user@&_connect_identifier >"set sqlprompt "_user _privile..原创 2021-08-19 14:36:35 · 158 阅读 · 0 评论 -
数据库横版的制作
不知道怎么切换过去原创 2021-08-18 17:30:09 · 77 阅读 · 0 评论 -
自动运行数据库检查(在opera db上)
运行方法一(进去运行脚本)select name,open_mode from v$database;archive log list;运行方法二(不进去运行)自动运行(把运行的SQL写进去)D:\ORACLE\11204\sqlplus\adminselect name,open_mode from v$database;archive log list;select name from v$datafile;select name from v$c..原创 2021-08-18 14:44:20 · 175 阅读 · 0 评论 -
db18c 安全新功能
配置钱包根位置和密钥库类型配置 CDB 密钥库的根目录位置ALTER SYSTEM SET WALLET_ROOT = '/u01/app/oracle/admin/cdb1/tde_keystore' SCOPE=SPFILE;重新启动实例并重新打开 PDBSHUTDOWN IMMEDIATESTARTUPALTER PLUGGABLE DATABASE ALL OPEN;SHOW PARAMETER wallet_rootALTER SYSTEM ...原创 2021-08-17 11:10:04 · 152 阅读 · 0 评论 -
四种方法恢复控制文件
丢了一个控制文件方法一恢复原来删除的控制文件(位置要一样)show parameter control_file删除控制文件rm /u02/oradata/CDB1/control01.ctlSHUTDOWN ABORT;STARTUP;SHUTDOWN ABORT;cd /u01/app/oracle/fast_recovery_area/CDB1cp control02.ctl /u02/oradata/CDB1/control0...原创 2021-08-15 15:39:44 · 1271 阅读 · 0 评论 -
使用替换变量弄表空间
查询表空间select tablespace_name,file_name,bytes/1024/1024 from dba_data_fileswhere tablespace_name='&biaokongjian'增加数据文件alter tablespace &biaoming add datafile '&kongjianming' size &daxiao;删除数据文件select fil...原创 2021-08-12 13:23:33 · 72 阅读 · 0 评论 -
db19检查
ps -ef | grep smonps -ef | grep tnslsnrctl statustnsping cdb1select file_name from dba_data_files;select instance_name,status from v$instance;select name,open_mode from v$database;select max(sequence#) from v$archived_lo...原创 2021-08-11 17:27:29 · 481 阅读 · 0 评论 -
GoldenGate安装
mkdir /u01/app/oracle/product/ggexport GG_HOME=/u01/app/oracle/product/ggexport NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"mkdir /u01/softunzip V983658-01.zip -d /u01/soft/cd /u01/soft/fbo_ggs_Linux_x64_shiphome/Disk1/...原创 2021-08-09 17:42:30 · 279 阅读 · 0 评论 -
db19迁移到新的机器上
hostnamectl set-hostname testps -ef | grep pmonenv|grep ORAenv|grep ORAps -ef | grep pmonvim listener.oraLISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.0.1...原创 2021-07-25 17:02:41 · 97 阅读 · 1 评论 -
SQL Developer Web
http://localhost:8080/ords/sql-developer可以进行导出ALTER SESSION SET CONTAINER=PDB1;alter user hr identified by hr account unlock;原创 2021-07-18 15:12:26 · 360 阅读 · 1 评论 -
数据库表中的数据显示在网页上
没我想的漂亮CREATE USER ordstest IDENTIFIED BY ordstest;GRANT "CONNECT" TO ordstest;GRANT "RESOURCE" TO ordstest;GRANT UNLIMITED TABLESPACE TO ordstestCREATE TABLE EMP ( EMPNO NUMBER(4,0), ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE),...原创 2021-07-15 17:42:44 · 1386 阅读 · 0 评论 -
ASM的spfile创建和 重命名ASM目录
创建spfileasm_diskgroups=‘OCR’,‘DATA’,‘FRA’instance_type=‘asm’large_pool_size=12Mremote_login_passwordfile=‘EXCLUSIVE’ASM_DISKSTRING=’/dev/oracleasm/disks/*’sqlplus / as sysasmshow parameter spfilealter diskgroup OCR mount;create spfile=’+O原创 2021-06-07 16:13:00 · 407 阅读 · 0 评论 -
db19数据加密
mkdir -p ORACLEBASE/admin/{ORACLE_BASE}/admin/ORACLEBASE/admin/{ORACLE_SID}/walletalter system set WALLET_ROOT=’/u01/app/oracle/admin/oradb/wallet’ scope=spfile;shutdown immediate;startupshow parameter walletshow parameter tdealter system set t原创 2021-06-02 14:44:00 · 297 阅读 · 0 评论 -
db19-rman详细教程三
比较压缩SHOW COMPRESSION ALGORITHM;run {ALLOCATE CHANNEL c1 DEVICE TYPE DISK;BACKUP DATABASE TAG ‘DB_WANBEI’;}LIST BACKUPSET TAG ‘DB_WANBEI’;DELETE BACKUPSET TAG ‘DB_WANBEI’;SET COMPRESSION ALGORITHM ‘LOW’;BACKUP AS COMPRESSED BACKUPSET DATABASE原创 2021-06-02 10:16:40 · 85 阅读 · 0 评论 -
db19-rman详细教程二
标记RMANBACKUP TABLESPACE USERS TAG ‘LIHAO’;LIST BACKUPSET TAG “LIHAO”;DELETE BACKUPSET TAG ‘LIHAO’;中配置保留策略SHOW RETENTION POLICY;基于冗余的保留策略CONFIGURE RETENTION POLICY TO REDUNDANCY 2;BACKUP TABLESPACE USERS TAG ‘TEST’;查看过期的备份REPORT OBSOLETE原创 2021-05-31 17:20:19 · 541 阅读 · 1 评论 -
db19-rman详细教程一
非存档模式ARCHIVE LOG LIST;SHUTDOWN IMMEDIATEstartup mount;ALTER DATABASE NOARCHIVELOG;ALTER DATABASE OPEN;ARCHIVE LOG LIST;BACKUP DATABASE PLUS ARCHIVELOG;存档模式SHUTDOWN IMMEDIATEstartup mount;ALTER DATABASE ARCHIVELOG;ALTER DATABASE OPEN;原创 2021-05-30 14:16:10 · 172 阅读 · 0 评论 -
安装DB19在安装GI,在把数据迁移到ASM磁盘里
yum install -y oracle-database-preinstall-19cid oraclepasswd oracleORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_SID=oradb; export ORACLE_SIDORACLE_HOME=$ORACLE_BASE/product/19.3.0/db_1; export ORACLE_HOMENLS_DATE_FORMAT="DD-MON-YYYY HH...原创 2021-05-27 13:42:02 · 96 阅读 · 1 评论 -
数据导出expdp
D:\dataCREATE OR REPLACE DIRECTORY test_dir AS ‘D:\data’;GRANT READ, WRITE ON DIRECTORY test_dir TO hr;expdp hr/hr@pdb1 tables=EMPLOYEES directory=TEST_DIR dumpfile=EMPLOYEES.dmp logfile=expdp EMPLOYEES.logexpdp hr/hr@pdb1 schemas=HR directory=TES原创 2021-05-10 13:26:10 · 1089 阅读 · 0 评论 -
oracle字符串/字符函数
INITCAP返回char,每个单词的首字母大写,所有其他字母小写。SELECT INITCAP(‘wo shi shui, wo zai na’) “首字母大写” FROM DUAL;SELECT INITCAP(‘WO SHI SHUI, WO ZAI NA’) “首字母大写” FROM DUAL;LOWER:所有字母均为小写SELECT LOWER (‘WO SHI SHUi, WO Zai na’) “所有字母均为小写” FROM DUAL;SELECT LOWER (‘WO SHI原创 2021-04-18 14:01:47 · 144 阅读 · 0 评论 -
oracle 中的实时统计
CREATE TABLE tab1 (id NUMBER,description VARCHAR2(50));创建一个过程来截断测试表,向其中填充一些数据并收集所有表统计信息。CREATE OR REPLACE PROCEDURE reset_tab1 ASBEGINEXECUTE IMMEDIATE ‘TRUNCATE TABLE tab1’;INSERT /*+ APPEND / INTO tab1 (id, description)SELECT level,原创 2021-04-08 09:55:09 · 564 阅读 · 0 评论 -
windows闪回一(三个例子)
例子一CREATE TABLE flashback_query_test ASSELECT level AS id,‘dummy’ || level AS descriptionFROM dualCONNECT BY level <= 4;variable v_scn number;variable v_timestamp varchar2(20);BEGINSELECT current_scn, TO_CHAR(SYSTIMESTAMP, ‘YYYY-MM-DD HH24:原创 2021-04-04 16:26:50 · 154 阅读 · 0 评论