搜索到
29
篇与
的结果
-
Oracle 备份脚本 完整备份run { #### 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup as compressed backupset database format 'D:\rmanbackup\db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }增量备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 as compressed backupset database format 'D:\rmanbackup\inc1_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }差异备份run { # 分配备份通道 allocate channel c1 type disk; allocate channel c2 type disk; # 备份数据文件 backup incremental level 1 differential as compressed backupset database format 'D:\rmanbackup\diff_db_%T_%U.bak'; # 备份数据文件完成后，关闭所有通道 release channel c1; release channel c2; # 重新分配通道备份控制文件和 SPFILE allocate channel c3 type disk; backup current controlfile format 'D:\rmanbackup\ctl_%T_%U.bak'; backup spfile format 'D:\rmanbackup\sp_%T_%U.bak'; # 释放备份控制文件和 SPFILE 的通道，然后重新分配通道用于 crosscheck 和 delete 操作 release channel c3; # 分配新通道 c4 用于 crosscheck 和 delete 操作 allocate channel c4 type disk; crosscheck archivelog all; crosscheck backup; # 备份所有归档日志文件 backup as compressed backupset archivelog all format 'D:\rmanbackup\arch_%T_%U.bak'; # 清理指定时间之前的归档日志和备份文件 delete noprompt archivelog all completed before 'sysdate-7'; delete noprompt backup completed before 'sysdate-1'; release channel c4; }
-
Oracle 部分命令介绍 Oracle学习记录表空间和数据文件表空间分类系统表空间system表空间system表空间用来存储整个数据库的数据字典表（data dictionary table），该表空间不能被损坏，一旦损坏，数据库将无法打开。SQL> select ts#,name from v$tablespace where name='SYSTEM'; TS# NAME ---------- -------------------------------------------------------------------------------- 0 SYSTEM SQL>sysaux表空间从oracle 10g开始，oracle将工具放到SYSAUX,减轻system的压力。SYSAUX表空间不影响系统，但是会影响性能。SQL> select ts#,name from v$tablespace where name='SYSAUX'; TS# NAME ---------- -------------------------------------------------------------------------------- 1 SYSAUX SQL>非系统表空间undoundo表空间中会自动分配undo段，这些undo段用来保存事务中的DML语句的undo信息，也就是来保存数据在被修改之前的值。undo数据用来：1）事务的回滚；2）实例恢复(回滚)；3）一致性读时需要构造CR块；SQL> select ts#,name from v$tablespace where name='UNDOTBS1'; TS# NAME ---------- -------------------------------------------------------------------------------- 2 UNDOTBS1 SQL> show parameter undo_tablespace; NAME TYPE VALUE ------------------------------------ -------------------- ------------------------------ undo_tablespace string UNDOTBS1 SQL>查看回滚段信息：SQL> select * from v$rollname; USN NAME CON_ID ---------- -------------------- ---------- 0 SYSTEM 0 1 _SYSSMU1_1261223759$ 0 2 _SYSSMU2_27624015$ 0 3 _SYSSMU3_2421748942$ 0 4 _SYSSMU4_625702278$ 0 5 _SYSSMU5_2101348960$ 0 6 _SYSSMU6_813816332$ 0 7 _SYSSMU7_2329891355$ 0 8 _SYSSMU8_399776867$ 0 9 _SYSSMU9_1692468413$ 0 10 _SYSSMU10_930580995$ 0 11 rows selected. SQL>temptemp表空间，即临时表空间，用来存放用户排序，分组等操作时的数据信息。SQL> select ts#,name from v$tablespace where name='TEMP'; TS# NAME ---------- -------------------- 3 TEMP SQL>users从oracle 10g开始，oracle将用户数据信息单独存放到users表空间中。SQL> select ts#,name from v$tablespace where name='USERS'; TS# NAME ---------- -------------------- 4 USERS SQL>用户表空间SQL> select ts#,name from v$tablespace where name not in('SYSTEM','SYSAUX','TEMP','USERS','UNDOTBS1'); TS# NAME ---------- -------------------- 6 HR SQL>表空间操作创建表空间SQL> create tablespace ORA_TEST_DAT datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' size 1G autoextend on next 200M maxsize 2G; Tablespace created. SQL>删除表空间不能删除的表空间为系统表空间，正在用的undo表空间，默认临时/永久表空间SQL> drop tablespace ORA_TEST_DAT including contents and datafiles; Tablespace dropped. SQL>修改表空间权限SQL> alter tablespace ORA_TEST_DAT read only; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT read write; Tablespace altered. SQL>脱机表空间SQL> alter tablespace ORA_TEST_DAT offline; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT online; Tablespace altered. SQL>表空间改名sysaux system不能改名用户表空间users，undo表空间，temp表空间最好不要改名，因为系统中部分参数定义名还是以原来的表空间名，下次数据库启动会报错。SQL> alter tablespace ORA_TEST_DAT rename to ORA_TEST_DAT01; Tablespace altered. SQL> alter tablespace ORA_TEST_DAT01 rename to ORA_TEST_DAT; Tablespace altered. SQL>表空间说明system 表空间必须online 必须是read writesysaux 表空间可以offline 不能read onlyundo 表空间不能offline 不能read only只读表空间的对象可以删除，但是不能被update和insert数据文件操作修改数据文件自动扩展SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' autoextend off; Database altered. SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' autoextend on; Database altered. SQL>修改数据文件大小SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' resize 100M; Database altered. SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT01.DBF' resize 1G; Database altered. SQL>添加数据文件SQL> alter tablespace ORA_TEST_DAT add datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' size 200M autoextend off; Tablespace altered. SQL>删除数据文件SQL> alter database datafile '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF' offline drop; Database altered. SQL>数据文件重命名(必须要在归档模式下)如果数据库只在mont状态，那么只要在操作系统级别移动数据文件，执行数据库文件改名，启动数据库即可SQL> alter tablespace ORA_TEST_DAT offline; Tablespace altered. SQL> ho cp '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF' SQL> alter database rename file '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT02.DBF' to '/u01/app/oracle/oradata/ORADB/ORA_TEST_DAT03.DBF'; Database altered. SQL> alter tablespace ORA_TEST_DAT online; Tablespace altered. SQL>注意事项修改数据文件ALTER DATABASE 语句修改单独的 DataFileALTER TABLESPACE 语句修改所有的 DataFileARCHIVRLOG 模式下的更改 DataFile只有在 ARCHIVELOG 模式下才可使用 ALTER DATABASE 来更改DataFileALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;由于在 NOARCHIVELOG 模式下，数据文件脱机后会造成数据的遗失，所以只能使用 ALTER DATABASE 语句下带有 DATAFILE 和 OFFLINE DROP 子句的选项将该 DataFile 直接取消，例如该 DataFile 只包含临时段数据，并没有备份时：alter database datafile '/u02/oracle/rbdb1/users3.dbf' offline drop;alter tablespace ....offline,会对数据文件进行检查点，并冻结数据文件SCN表空间 online 时，Oracle 会取得当前 SCN，解冻 offline 文件 SCN，和当前 SCN同步tablespace offline 有几种选项可供选择: normal, temporary,immediate, for recovery,而在 datafile 中则没有这些选项。数据文件reuse参数如果 file 已经存在，并且在创建时指定了 file size，那么就重用原文件，并应用新的 size，如果没有指定 file size，则保留原有的大小如果 file 不存在，oracle 将忽略该参数如果 Oracle 使用了已经存在的 file，那么之前 file 里的数据将全部丢失offline dropoffline drop 并不会 drop datafile， 仅仅是将 datafile 标记为 offline， 我们 online之后还可以 recover 回来。alter database datafile '/usr/ORA_TEST_DAT01.dbf' offline drop;该命令不会删除数据文件，只是将数据文件的状态更改为recover。 offline drop命令相当于把一个数据文件至于离线状态，并且需要恢复，并非删除数据文件。 数据文件的相关信息还会存在数据字典和控制文件中归档模式下，alter database datafile 'file_name' offline 和 offline drop 没有什么区别。 因为offline 之后多需要进行recover 才可以online。alter database datafile '/usr/ORA_TEST_DAT01.dbf' online;recover datafile '/usr/ORA_TEST_DAT01.dbf';alter tablespace ORA_TEST_DAT drop datafile '/usr/ORA_TEST_DAT01.dbf';该语句会删除控制文件和磁盘上的文件，删除之后的原数据文件序列号可以重用。该语句只能是datafile online的时候才可以使用。如果说对应的数据文件已经是offline for drop，那么仅针对 dictionary managed tablespaces 可用。控制文件控制文件概述二进制文件，初始化大小由CREATE DATABASE指定,可以使用RMAN备份记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等在参数文件中描述其位置，个数等等。通常采用分散放开，多路复用的原则。在mount阶段被读取，open阶段一直被使用维护数据库一致性(数据库启动时会比较控制文件与联机日志文件中的ckpt,即起始scn号，如相等则正常启动，否则需要介质恢复)一个控制文件只能属于一个数据库控制文件的任意修改将写入到初始化参数中指定的所有控制文件中，读取时则仅读取第一个控制文件控制文件只能连接一个数据库，控制文件的大小一般不要超过MB,最多为个，最少一个，互为镜像控制文件中包含的内容数据库的名字、ID、创建的时间戳表空间的名字联机日志文件、数据文件的位置、个数、名字联机日志的Sequence号码检查点的信息撤销段的开始或结束归档信息备份信息查看控制文件信息列出实例中所有控制文件的名字及状态信息SQL> select * from v$controlfile; STATUS --------------------- NAME -------------------------------------------------------------------------------- IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS CON_ID --------- ---------- -------------- ---------- /u01/app/oracle/oradata/ORADB/control01.ctl NO 16384 646 0 /u01/app/oracle/oradata/ORADB/control02.ctl NO 16384 646 0 STATUS --------------------- NAME -------------------------------------------------------------------------------- IS_RECOVE BLOCK_SIZE FILE_SIZE_BLKS CON_ID --------- ---------- -------------- ---------- SQL>列出所有参数的位置及状态信息SQL> select name,value from v$parameter where name='control_files'; NAME -------------------------------------------------------------------------------- VALUE -------------------------------------------------------------------------------- control_files /u01/app/oracle/oradata/ORADB/control01.ctl, /u01/app/oracle/oradata/ORADB/contr ol02.ctl SQL>列出控制文件中记录的部分信息SQL> select * from v$CONTROLFILE_RECORD_SECTION; TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE 316 1 1 0 0 0 0 CKPT PROGRESS 8180 11 0 0 0 0 0 REDO THREAD 256 8 1 0 0 0 0 REDO LOG 72 16 3 0 0 3 0 DATAFILE 520 100 8 0 0 18 0 FILENAME 524 2298 11 0 0 0 0 TABLESPACE 180 100 7 0 0 7 0 TEMPORARY FILENAME 56 100 1 0 0 1 0 RMAN CONFIGURATION 1108 50 0 0 0 0 0 LOG HISTORY 56 292 9 1 9 9 0 OFFLINE RANGE 200 163 2 1 2 2 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- ARCHIVED LOG 584 28 0 0 0 0 0 BACKUP SET 96 170 0 0 0 0 0 BACKUP PIECE 780 209 0 0 0 0 0 BACKUP DATAFILE 200 245 0 0 0 0 0 BACKUP REDOLOG 76 215 0 0 0 0 0 DATAFILE COPY 736 200 0 0 0 0 0 BACKUP CORRUPTION 44 371 0 0 0 0 0 COPY CORRUPTION 40 409 0 0 0 0 0 DELETED OBJECT 20 818 1 1 1 1 0 PROXY COPY 928 246 0 0 0 0 0 BACKUP SPFILE 124 131 0 0 0 0 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- DATABASE INCARNATION 56 292 2 1 2 2 0 FLASHBACK LOG 84 2048 0 0 0 0 0 RECOVERY DESTINATION 180 1 0 0 0 0 0 INSTANCE SPACE RESERVATION 28 1055 1 0 0 0 0 REMOVABLE RECOVERY FILES 32 1000 0 0 0 0 0 RMAN STATUS 116 141 0 0 0 0 0 THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0 0 MTTR 100 8 1 0 0 0 0 DATAFILE HISTORY 568 57 0 0 0 0 0 STANDBY DATABASE MATRIX 400 128 128 0 0 0 0 GUARANTEED RESTORE POINT 256 2048 0 0 0 0 0 TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID ------------------------------------------------------------------------------------ ----------- ------------- ------------ ----------- ---------- ---------- ---------- RESTORE POINT 256 2108 0 0 0 0 0 DATABASE BLOCK CORRUPTION 80 8384 0 0 0 0 0 ACM OPERATION 104 64 11 0 0 0 0 FOREIGN ARCHIVED LOG 604 1002 0 0 0 0 0 PDB RECORD 780 10 0 0 0 0 0 AUXILIARY DATAFILE COPY 584 128 0 0 0 0 0 MULTI INSTANCE REDO APPLY 556 1 0 0 0 0 0 PDBINC RECORD 144 113 0 0 0 0 0 TABLESPACE KEY HISTORY 108 151 0 0 0 0 0 42 rows selected. SQL>列出控制文件的名字、状态、位置等SQL> SHOW PARAMETER CONTROL_FILES NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ control_files string /u01/app/oracle/oradata/ORADB/ control01.ctl, /u01/app/oracle /oradata/ORADB/control02.ctl SQL>使用STRINGS命令来查看控制文件中的具体内容[oracle@oracle ~]$ strings /u01/app/oracle/oradata/ORADB/control01.ctl备份控制文件到平面文件(然后查看控制文件中的具体内容)SQL> alter database backup controlfile to trace as '/home/oracle/ctl.txt'; Database altered. SQL>转储控制文件内容(查看控制文件中的具体内容)
-
Oracle 部分报错处理 Oracle报错处理处理Oracle数据库启动报错 ORA-27104报错内容：system-defined limits for shared memory was misconfigured查看报错日志（我的日志位置，根据自身环境确定）cat /u01/app/oracle/diag/rdbms/oradb/oradb/alert/log.xml错误内容如下：<msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>System cannot support SGA size of 2352 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Total system memory configured is 1838 MB. </txt> </msg> <msg time='2021-02-20T02:44:26.252-05:00' org_id='oracle' comp_id='rdbms' type='UNKNOWN' level='16' host_id='oracle' host_addr='fd4a:8abb:ad4::cfb' pid='3810'> <txt>Instance maximum shared memory size should be less than 1654 MB. </txt>我的环境中，根据报错可以看出配置的SGA相关配置的内存太大了，要求小于1654MB解决办法：数据库不能启动，需要调整编辑参数文件来修改相关的SGA值我的spfile文件位置是：/u01/app/oracle/product/19c/dbs[oracle@oracle dbs]$ pwd /u01/app/oracle/product/19c/dbs [oracle@oracle dbs]$ ls hc_oradb.dat init20210220.ora init.ora lkORADB orapworadb spfileoradb.ora [oracle@oracle dbs]$先生成一个临时spfile文件来修改配置SQL> create pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora' from spfile; File created. SQL> !vi /u01/app/oracle/product/19c/dbs/init20210220.ora打开后配置如下：oradb.__data_transfer_cache_size=0 oradb.__db_cache_size=1778384896 oradb.__inmemory_ext_roarea=0 oradb.__inmemory_ext_rwarea=0 oradb.__java_pool_size=0 oradb.__large_pool_size=16777216 oradb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment oradb.__pga_aggregate_target=822083584 oradb.__sga_target=2466250752 oradb.__shared_io_pool_size=134217728 oradb.__shared_pool_size=520093696 oradb.__streams_pool_size=0 oradb.__unified_pga_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/oradb/adump' *.audit_trail='db' *.compatible='19.0.0' *.control_files='/u01/app/oracle/oradata/ORADB/control01.ctl','/u01/app/oracle/oradata/ORADB/control02.ctl' *.db_block_size=8192 *.db_name='oradb' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=oradbXDB)' *.local_listener='LISTENER_ORADB' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=782m *.processes=320 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2346m *.undo_tablespace='UNDOTBS1'将 oradb.__sga_target= 和 .sga_target= 这两个参数修改到推荐值范围，然后保存。保存之后使用心得spfile启动数据库SQL> startup pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora'; ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL>现在数据库能启动成功了，还需要将修改后的pfile文件覆盖原spfile文件SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> create spfile from pfile='/u01/app/oracle/product/19c/dbs/init20210220.ora'; File created. SQL> startup ORACLE instance started. Total System Global Area 1426059296 bytes Fixed Size 8896544 bytes Variable Size 352321536 bytes Database Buffers 1056964608 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL>完成修改archivelog报错 ORA-00265执行过强制关闭数据库的操作就会出现的错误解决步骤：将数据库起到open状态，正常关闭shutdown immediate，再起到mount状态SQL> alter database open ;------启动数据库到open状态 Database altered. SQL> shutdown immediate------正常关闭immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount;------启动到mount状态 ORACLE instance started. Total System Global Area 830930944 bytes Fixed Size 2257800 bytes Variable Size 536874104 bytes Database Buffers 289406976 bytes Redo Buffers 2392064 bytes Database mounted. SQL> alter database archivelog;------执行更改archivelog Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 7 Current log sequence 7
-
锁等待及阻塞查询 锁等待及阻塞查询锁等待查询数据库等待时间和实际执行时间的相对百分比select * from v$sysmetric a where a.METRIC_NAME in ('Database CPU Time Ratio', 'Database Wait Time Ratio') and a.INTSIZE_CSEC = (select max(intsize_csec) from v$sysmetric);查询数据库中过去30分钟引起最多等待的sql语句select ash.USER_ID, u.username, sum(ash.WAIT_TIME) ttl_wait_time, s.SQL_TEXT from v$active_session_history ash, v$sqlarea s, dba_users u where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and ash.SQL_ID = s.SQL_ID and ash.USER_ID = u.user_id group by ash.USER_ID, s.SQL_TEXT, u.username order by ttl_wait_time desc;查询数据库中的等待事件select event, count(*) from v$session_wait group by event order by count(*) desc;查询数据库过去15分钟最重要的等待事件select ash.EVENT, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time from v$active_session_history ash where ash.SAMPLE_TIME between sysdate - 15 / 2880 and sysdate group by event order by total_wait_time desc;在过去15分钟哪些用户经历了等待select s.SID, s.USERNAME, sum(ash.WAIT_TIME + ash.TIME_WAITED) total_wait_time from v$active_session_history ash, v$session s where ash.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and ash.SESSION_ID = s.SID group by s.SID, s.USERNAME order by total_wait_time desc;查询等待时间最长的对象select a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT, sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time from v$active_session_history a, dba_objects d where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and a.CURRENT_OBJ# = d.object_id group by a.CURRENT_OBJ#, d.object_name, d.object_type, a.EVENT order by total_wait_time desc;查询过去15分钟等待时间最长的sql语句select a.USER_ID, u.username, s.SQL_TEXT, sum(a.WAIT_TIME + a.TIME_WAITED) total_wait_time from v$active_session_history a, v$sqlarea s, dba_users u where a.SAMPLE_TIME between sysdate - 30 / 2880 and sysdate and a.SQL_ID = s.SQL_ID and a.USER_ID = u.user_id group by a.USER_ID, s.SQL_TEXT, u.username order by total_wait_time desc;哪些SQL消耗更多的IOselect * from (select s.PARSING_SCHEMA_NAME, s.DIRECT_WRITES, substr(s.SQL_TEXT, 1, 500), s.DISK_READS from v$sql s order by s.DISK_READS desc) where rownum < 20查看哪些会话正在等待IO资源SELECT username, program, machine, sql_id FROM V$SESSION WHERE EVENT LIKE 'db file%read';查看正在等待IO资源的对象SELECT d.object_name, d.object_type, d.owner FROM V$SESSION s, dba_objects d WHERE EVENT LIKE 'db file%read' and s.ROW_WAIT_OBJ# = d.object_id;查看redo日志切换频率Select round(FIRST_TIME, 'DD'), THREAD#, Count(SEQUENCE#) From v$log_history Group By round(FIRST_TIME, 'DD'), THREAD# Order By 1, 2 SELECT trunc(first_time) "Date", to_char(first_time, 'Dy') "Day", count(1) "Total", SUM(decode(to_char(first_time, 'hh24'),'00',1,0)) "h0", SUM(decode(to_char(first_time, 'hh24'),'01',1,0)) "h1", SUM(decode(to_char(first_time, 'hh24'),'02',1,0)) "h2", SUM(decode(to_char(first_time, 'hh24'),'03',1,0)) "h3", SUM(decode(to_char(first_time, 'hh24'),'04',1,0)) "h4", SUM(decode(to_char(first_time, 'hh24'),'05',1,0)) "h5", SUM(decode(to_char(first_time, 'hh24'),'06',1,0)) "h6", SUM(decode(to_char(first_time, 'hh24'),'07',1,0)) "h7", SUM(decode(to_char(first_time, 'hh24'),'08',1,0)) "h8", SUM(decode(to_char(first_time, 'hh24'),'09',1,0)) "h9", SUM(decode(to_char(first_time, 'hh24'),'10',1,0)) "h10", SUM(decode(to_char(first_time, 'hh24'),'11',1,0)) "h11", SUM(decode(to_char(first_time, 'hh24'),'12',1,0)) "h12", SUM(decode(to_char(first_time, 'hh24'),'13',1,0)) "h13", SUM(decode(to_char(first_time, 'hh24'),'14',1,0)) "h14", SUM(decode(to_char(first_time, 'hh24'),'15',1,0)) "h15", SUM(decode(to_char(first_time, 'hh24'),'16',1,0)) "h16", SUM(decode(to_char(first_time, 'hh24'),'17',1,0)) "h17", SUM(decode(to_char(first_time, 'hh24'),'18',1,0)) "h18", SUM(decode(to_char(first_time, 'hh24'),'19',1,0)) "h19", SUM(decode(to_char(first_time, 'hh24'),'20',1,0)) "h20", SUM(decode(to_char(first_time, 'hh24'),'21',1,0)) "h21", SUM(decode(to_char(first_time, 'hh24'),'22',1,0)) "h22", SUM(decode(to_char(first_time, 'hh24'),'23',1,0)) "h23" FROM V$log_history group by trunc(first_time), to_char(first_time, 'Dy') Order by 1;查询阻塞和被阻塞的session，支持RAC，按instance分组SELECT a.INST_ID, a.sid, a.SERIAL#, a.USERNAME, a.SQL_ID, a.PROGRAM, a.EVENT, a.BLOCKING_SESSION, a.WAIT_TIME_MICRO FROM GV$SESSION A WHERE (A.INST_ID, a.SID) in (select b.BLOCKING_INSTANCE, b.BLOCKING_SESSION from gv$session b) union all select a.INST_ID, a.sid, a.SERIAL#, a.USERNAME, a.SQL_ID, a.PROGRAM, a.EVENT, a.BLOCKING_SESSION, a.WAIT_TIME_MICRO FROM GV$SESSION A where a.BLOCKING_SESSION is not null order by BLOCKING_SESSION nulls first;最近30分钟内ASH采样到的等待事件排名，支持RAC,按instance分组select * from (select inst_id, rank() over(partition by inst_id order by cnt desc) rk, event, cnt from (select ash.INST_ID, ash.EVENT, count(*) CNT from gv$active_session_history ash where ash.SAMPLE_TIME > sysdate - 1 / 24 / 60 * 30 and event is not null group by ash.INST_ID, ash.EVENT)) where rk <= 10;某段事件内ASM采样到的等待事件排名，并查询采样中，产生此等待事件的SQL，支持rac，按 instance分组select a.inst_id, a.event, a.sql_id, a.sql_cnt, b.cnt event_cnt, sql_rk from (select inst_id, event, sql_id, sql_cnt, rank() over(partition by event order by sql_cnt desc) sql_rk from (select ash.INST_ID, ash.EVENT, ash.SQL_ID, count(*) sql_cnt from gv$active_session_history ash where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') > '2021-07-25 14:00:00' AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') < '2021-07-25 16:00:00' AND EVENT IS NOT NULL GROUP BY ASH.INST_ID, ASH.EVENT, ASH.SQL_ID)) A, (SELECT INST_ID, EVENT, CNT FROM (SELECT INST_ID, rank() over(partition by INST_ID order by cnt desc) rk, EVENT, CNT FROM (SELECT ASH.INST_ID, ASH.EVENT, COUNT(*) CNT FROM GV$ACTIVE_SESSION_HISTORY ASH where to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') > '2021-07-25 14:00:00' AND to_char(ash.SAMPLE_TIME, 'YYYY-MM-DD HH24:MI:SS') < '2021-07-25 16:00:00' AND EVENT IS NOT NULL GROUP BY ASH.INST_ID, ASH.EVENT)) WHERE RK <= 3) B WHERE A.INST_ID = B.INST_ID AND A.EVENT = B.EVENT AND A.SQL_RK <= 5 ORDER BY INST_ID, EVENT_CNT DESC, SQL_CNT DESC, SQL_RK;查看过去15天sql执行的hash_plan_id和资源消耗统计变化select sql_id, endtime, plan_hash_value, nvl2(exed, exed, 0) "执行次数", nvl2(ems, ems, 0) "单次执行时间(ms)", nvl2(cms, cms, 0) "平均CCWAIT(ms)", nvl2(ams, ams, 0) "平均APWAIT(ms)", nvl2(clms, clms, 0) "平均CMWAIT(ms)", nvl2(ioms, ioms, 0) "平均IOWAIT(ms)", nvl2(ctms, ctms, 0) "平均CPU_WAIT(ms)", nvl2(bfgets, bfgets, 0) "平均buffer gets", nvl2(drd, drd, 0) "平均disk reads", nvl2(sd, sd, 0) "平均sort", nvl2(fet, fet, 0) "平均fetch", nvl2(rpd, rpd, 0) "平均rows process", snap_id from (select a.sql_Id, to_char(b.end_interval_time, 'yyyymmdd hh24:mi:ss') endtime, a.executions_delta exed, a.plan_hash_value, round(decode(a.executions_delta,0,0,a.elapsed_time_delta / a.executions_delta) / 1000,2) ems, round(decode(a.executions_delta,0,0,a.ccwait_delta / a.executions_delta) / 1000,2) cms, round(decode(a.executions_delta,0,0,a.apwait_delta / a.executions_delta) / 1000,2) ams, round(decode(a.executions_delta,0, 0,a.clwait_delta / a.executions_delta) / 1000,2) clms, round(decode(a.executions_delta,0, 0,a.iowait_delta / a.executions_delta) / 1000,2) ioms, round(decode(a.executions_delta, 0,0, a.cpu_time_delta / a.executions_delta) / 1000,2) ctms, round(decode(a.executions_delta, 0, 0,a.buffer_gets_delta / a.executions_delta),2) bfgets, round(decode(a.executions_delta,0, 0, a.disk_reads_delta / a.executions_delta),2) drd, round(decode(a.executions_delta,0,0, a.sorts_delta / a.executions_delta),2) sd, round(decode(a.executions_delta,0, 0,a.fetches_delta / a.executions_delta),2) fet, round(decode(a.executions_delta,0,0, a.rows_processed_delta / a.executions_delta), 4) rpd, b.snap_id from dba_hist_sqlstat a, dba_hist_snapshot b where sql_id = '62qcz0vz20a3t' and a.snap_id = b.snap_id and a.instance_number = b.instance_number --and b.instance_number=1 and a.executions_delta <> 0 and b.end_interval_time > sysdate - 15) order by snap_id;查询某个会话的锁select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;查询TMTX锁select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;查询数据库中的锁select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4; # 说明： # dba_hist_active_sess_history/v$active_session_history查看SQL执行历史情况 # v$session_wait/v$session_event查看数据库会话的等待事件 # v$sga_target_advice数据库sga优化顾问视图 # v$pga_target_advice数据库pga优化顾问视图 # v$memory_target_advice内存优化顾问视图 # dba_data_files查询表空间和对应数据文件 # v$session_longops记录执行超过5s的sql，可以查询一个SQL语句执行了多长时间以及还要执行多长时间 # v$fast_start_transactions/v$fast_start_servers可以查看所有回滚的事务 # v$log视图可以查找到联机重做日志的日志序列号v$archived_log视图或 # v$log_history视图中可以查找给定的归档重做日志的日志序列号 # user_tab_columns视图将显示当前用户模式下的每张表的每一列的详细数据情况 # gv$memory_dynamic_components记录内存变更的视图 # gv$sql_monitor SQL监视视图 # dba_tab_statistics表的信息统计**提供运维人员使用，查看长时间持有锁并阻塞其他会话session，生成处理语句SELECT '锁源session: '||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||', '||A_S.SCHEMANAME||', From: '||A_S.OSUSER||','||A_S.MACHINE||', '||A_S.PROGRAM||', Cur_SQL: '||A_S.SQL_ID||',Prev_SQL: '|| A_S.PREV_SQL_ID||', Status:'||A_S.STATUS||', Lock_Time: '||A.CTIME||'s.'||CHR(10)|| ' -> 等待锁session:'||B.SID||','||B_S.INST_ID||','||B_S.MACHINE||','||B_S.EVENT||', Blocked_SQL: '||B_S.SQL_ID||', Locked_ON: '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||', Lock_Mode: '||A.TYPE||','|| DECODE(A.LMODE, 0, '0,none', 1, '1,NULL', 2, '2,row-S(SS)', 3, '3,row-X(SX)', 4, '4,share(S)', 5, '5,S/Row-X(SSX)', 6, '6,exclusive(X)')||CHR(10)|| ' --> 查询锁定数据SQL: '||(DECODE(OBJ.OBJECT_TYPE, 'TABLE', 'SELECT * FROM '||OBJ.OWNER||'.'||OBJ.OBJECT_NAME||' WHERE ROWID = '''|| DBMS_ROWID.ROWID_CREATE(1, OBJ.DATA_OBJECT_ID, B_S.ROW_WAIT_FILE#, B_S.ROW_WAIT_BLOCK#, B_S.ROW_WAIT_ROW#)||''';', NULL))||CHR(10)|| ' --> 查询锁源操作SQL: SELECT DISTINCT ASH.SESSION_ID, ASH.SESSION_SERIAL#, ASH.MACHINE, ASH.PROGRAM, ASH.XID, ASH.SAMPLE_TIME, ASH.SQL_ID , ASH.SQL_OPNAME, ASH.SQL_EXEC_START, ASH.EVENT, Q.SQL_TEXT FROM GV$ACTIVE_SESSION_HISTORY ASH JOIN GV$SQL Q ON ASH.INST_ID=q.INST_ID AND ASH.SQL_ID = q.SQL_ID and ASH.SQL_PLAN_HASH_VALUE=q.PLAN_HASH_VALUE WHERE ASH.SESSION_ID = '|| A_S.SID ||' AND ASH.SESSION_SERIAL# = '|| A_S.SERIAL# ||' AND ASH.INST_ID = '|| A_S.INST_ID ||' ORDER BY 1, 2, 3, 4, 5, 6;'||CHR(10)|| ' --> 查询相关SQL文本: SELECT DISTINCT SQL_ID,SQL_TEXT FROM GV$SQL WHERE SQL_ID IN ('''||A_S.SQL_ID||''','''||A_S.PREV_SQL_ID||''','''||B_S.SQL_ID||''');'||CHR(10)|| ' --> 清理session命令: alter system disconnect session '''||A_S.SID||','||A_S.SERIAL#||',@'||A_S.INST_ID||''' immediate;' AS BLOCK_DETAIL FROM GV$LOCK A, GV$LOCK B, GV$SESSION A_S, GV$SESSION B_S, DBA_OBJECTS OBJ, DBA_USERS U WHERE A.ID1 = B.ID1 AND A.ID2 = B.ID2 AND A_S.USER#=U.USER_ID AND U.ORACLE_MAINTAINED='N' /* 排除Oracle内部账户持有的锁 */ AND A.CTIME >= 30 /* 持有锁时间超过 30s */ AND A.BLOCK > 0 /* 阻塞了其他的会话 */ AND B.REQUEST > 0 AND A_S.TYPE='USER' AND A.SID = A_S.SID AND A.INST_ID = A_S.INST_ID AND B.SID = B_S.SID AND B.INST_ID = B_S.INST_ID AND A_S.SID = B_S.FINAL_BLOCKING_SESSION AND A_S.INST_ID = B_S.FINAL_BLOCKING_INSTANCE AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+) ORDER BY A.INST_ID, A.SID; set pagesize 1000 linesize 1000; select count(*), machine, program, username, osuser from gv$session group by machine, program, username, osuser order by count(*) desc; set pagesize 1000 linesize 1000; select count(*) from gv$session;TOP20慢查询语句SELECT * FROM ( SELECT s.sql_text, s.sql_fulltext, s.sql_id, s.executions exectimes, ROUND(s.elapsed_time / 1000000, 2) exectime, ROUND(s.elapsed_time / 1000000 / s.executions, 2) avgexetime, s.first_load_time fathercreatetime, s.parsing_user_id userid, u.username username FROM v$sqlarea s LEFT JOIN all_users u ON s.parsing_user_id = u.user_id WHERE s.executions > 0 AND u.username = 'HISUSER' ORDER BY avgexetime DESC) WHERE rownum <= 20;数据库历史阻塞事件查询根据awr报告来观察某一时间段主要的等待事件select event,blocking_session,sql_id,count(*) from dba_hist_active_sess_history ash where sample_time>=to_timestamp('2023-12-20 10:00:00','yyyy-mm-dd hh24:mi:ss') and sample_time<=to_timestamp('2023-12-20 11:08:00','yyyy-mm-dd hh24:mi:ss') and event='SQL*Net message from client' group by event,blocking_session,sql_id;根据阻塞的SQL_ID查询到具体的SQL语句select sql_text from v$sql where sql_id='7521kc2bkpdh9';根据阻塞的SQL_ID查询其他更多的信息SELECT DISTINCT s.inst_id, s.machine, s.LOGON_TIME, s.PREV_EXEC_START FROM gv$session s WHERE s.sql_id = '03ukf2c56gs54' order BY s.PREV_EXEC_START; #=====================================# SELECT DISTINCT s.inst_id, s.machine, i.instance_name, i.host_name FROM gv$session s JOIN gv$instance i ON s.inst_id = i.inst_id WHERE s.sql_id = '03ukf2c56gs54'; #=====================================# SELECT DISTINCT s.inst_id, s.machine, i.instance_name, i.host_name, sys_context('USERENV', 'IP_ADDRESS') AS client_ip FROM gv$session s JOIN gv$instance i ON s.inst_id = i.inst_id WHERE s.sql_id = '03ukf2c56gs54'; #=====================================# SELECT DISTINCT s.machine FROM gv$session s JOIN v$sql q ON s.sql_id = q.sql_id WHERE q.sql_id = '11xapz031xfr4' AND s.username IS NOT NULL AND s.logon_time >= TO_DATE('2023-10-08 22:20:00', 'yyyy-mm-dd hh24:mi:ss') AND s.logon_time <= TO_DATE('2023-10-08 22:50:00', 'yyyy-mm-dd hh24:mi:ss'); #=====================================# SELECT DISTINCT s.machine FROM gv$session s WHERE s.sql_id = '11xapz031xfr4' AND s.logon_time <= TO_DATE('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') AND (s.sid, s.serial#) IN ( SELECT sid, serial# FROM gv$session WHERE end_time IS NULL OR end_time >= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') ); #=====================================# SELECT DISTINCT s.machine FROM gv$session s WHERE s.sql_id = '11xapz031xfr4' AND s.logon_time <= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') AND (s.sid, s.serial#) IN ( SELECT sid, serial# FROM gv$session WHERE end_time IS NULL OR end_time >= TO_TIMESTAMP('2023-10-08 22:30:00', 'yyyy-mm-dd hh24:mi:ss') ); #=====================================# SELECT s.SID, s.SERIAL#, s.USERNAME, s.STATUS, s.OSUSER, s.MACHINE, s.PROGRAM, q.SQL_TEXT FROM v$session s JOIN v$sql q ON s.SQL_ID = q.SQL_ID WHERE s.SID = 1723;
-
Oracle表空间操作 Oracle查询表空间使用率Oracle 数据库的表空间如果使用超过100%，会导致数据库死机通过 sql 查询当前数据库所有表空间的使用率：set pagesize 1000 linesize 1000; col TABLESPACE_NAME for a40; select tbs_used_info.tablespace_name, tbs_used_info.alloc_mb, tbs_used_info.used_mb, tbs_used_info.max_mb, tbs_used_info.free_of_max_mb, tbs_used_info.used_of_max || '%' used_of_max_pct from (select a.tablespace_name, round(a.bytes_alloc / 1024 / 1024) alloc_mb, round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) used_mb, round((a.bytes_alloc - nvl(b.bytes_free, 0)) * 100 / a.maxbytes) used_of_max, round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0)) / 1048576) free_of_max_mb, round(a.maxbytes / 1048576) max_mb from (select f.tablespace_name, sum(f.bytes) bytes_alloc, sum(decode(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes from dba_data_files f group by tablespace_name) a, (select f.tablespace_name, sum(f.bytes) bytes_free from dba_free_space f group by tablespace_name) b where a.tablespace_name = b.tablespace_name(+)) tbs_used_info order by tbs_used_info.used_of_max desc;查询表空间使用的数据文件SELECT tablespace_name, file_id, file_name, bytes, blocks FROM dba_data_files WHERE tablespace_name = 'table_spaceName';给表空间增加数据文件ALTER TABLESPACE table_spaceName ADD DATAFILE 'DatafilePath' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 31G;temp数据文件处理# 查询tempfile SELECT name, bytes/1024/1024 AS "MB" FROM v$tempfile ORDER BY bytes; # 修改位置 alter database rename file '+DATA/xxx.dbf' to 'xxx'; # 模糊查询 select file_id,file_name,bytes/1024/1024 MB,status,tablespace_name from dba_temp_files where file_name like '%temp%';
-
Windows Server 2016 评估版转正式版 WindowsServer 2016 Evaluation 评估版转正式版前些时间从微软官网下载了server2016，没注意，下载的是(14393.0.161119-1705.RS1_REFRESH_SERVER_EVAL_X64FRE_ZH-CN.ISO)评估版本，使用中发现不能激活，且180天过期，这里给出解决办法。首先检查系统版本DISM /online /Get-CurrentEdition发现版本是ServerStandradEval版本，即使通过激活服务器激活成功，桌面上还是会提醒180天过期解决步骤服务器上 ：开始 -- 运行 -- CMD(管理员模式)：DISM /online /Set-Edition:XXXXXX /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEulaEdition ID 自己填需要转换的版本，ProductKey 后面把序列号填上去就行。 例如： 标准版：ServerStandard DISM /online /Set-Edition:ServerStandard /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula 数据中心版：ServerDatacenter DISM /online /Set-Edition:ServerDatacenter /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula运行后，选择 Y ，重启操作系统。问题就解决了。此方法也适用于 Standard 和 DataCenter 互转参考序列号：Windows Server 2016 数据中心：CB7KF-BWN84-R7R2Y-793K2-8XDDGWindows Server 2016 标准版：WC2BQ-8NRM3-FDDYY-2BFGV-KHKQY
-
VMware 产品密钥 HV4WC-01087-1ZJ48-031XP-9A843NF0F3-402E3-MZR80-083QP-3CKM24F6FX-2W197-8ZKZ9-Y31ZM-1C3LZJZ2E9-6D2DK-XZQD0-632E4-33E7ZMZ48M-DNK56-ZZJD0-RTCE2-9321X0Y0AJ-4P29H-LZV81-59AQ2-C291V0A4GL-D7JD7-LZR10-M30G0-A36JF4C02A-DP2E2-AZ041-FH9X6-922J61G297-6W01H-TZAR1-7TAE6-1AY241C6N9-A4L01-MZ6R8-QA174-0CANZHG6QA-FU340-LZMV8-Y33Q2-0A4K20Y2FJ-02K1H-RZ2V0-723E2-13QNXMY61G-8H11N-XZU40-M205P-33XJE1F0QH-883E5-7Z5W8-HV9N6-03FKCJZ486-4020J-AZUQ9-7T05P-92VQ84C6XC-0WH46-HZ4Q9-UV0N2-2AAJ6MZ08J-DKH07-VZ2U0-Y83NM-9CVPUNV227-44K56-MZ9P9-2H8ZP-A38PAHF0HX-FRJ90-JZ5E1-M8952-3CF1Z0Y6G3-8R151-DZPT8-4UA56-13PNXNY4PP-8J38P-1Z8Z0-X03N6-031LD5F6N0-FR35Q-XZ6U0-4V25P-93G2KMA2XX-04J16-1ZX80-68052-32YHY4G6CN-D920L-CZ7W9-CHC5P-02ANM0A65P-00HD0-3Z5M1-M097M-22P7HMA241-0KLEP-4Z718-LR2Q6-A2474NA6FU-4J39N-AZDK0-A82G6-33E6R1Z06V-22JE5-0ZW10-G91EH-02A7DHA6VG-683DQ-YZUV0-2925K-ACW06JA4A7-48H84-QZYP9-QK0N4-1A0JUMZ0TJ-8V307-GZ919-QT876-13YPV0V0VU-06KD6-CZU48-Q2150-03WLUMY0R3-2W04J-9Z340-8CAQP-1AJ56MU4T8-AW09M-LZPZ9-18CGP-9CM0KHA08T-6H30P-DZKM0-8H0QP-92U6R0G6JR-8Y084-TZA71-HT374-C3ZHX1C0KQ-2KH0H-8ZQR1-P8356-0CK2G5A68Y-8GKDK-WZLG9-XVAX6-3CM2W4A6RF-A5L0M-CZ4V8-RT9Q4-13P04HF2CN-6WH9M-KZAA1-UK8QM-0CX424G4KX-680EP-2ZZC1-WT2N6-0AL3U1F6Z7-82K8M-XZ669-3T17M-AAWJ00Z25R-6F24J-0Z2L1-WC356-AA84ZMY00L-2YH9K-9ZA98-MR270-3AXKNJU6YR-8E09H-1ZCT9-PL3Z0-1AUKXMF0GM-8839J-7ZLV0-7V1EK-3CMQJJY6Z7-0P1E5-9ZHR0-PRANP-0296D0Z4F9-6U315-6ZMR0-RAAN6-3AFMC5A6WG-AWJEH-TZUL9-AH950-CADNHNY61U-AKK9M-CZNJ9-WA354-AAP76JF4Y7-26J0P-TZZ49-NK9ZH-0CPJJMV62H-AT20K-9Z280-DK2N0-9AVKvSphere 6 HypervisorHY0XH-D508H-081U8-JA2GH-CCUM24C4WK-8KH8L-H85J0-UHCNK-8CKQ8NV09R-2W007-08D38-CA956-33U28JU400-6EK4L-080V9-QT8EP-2KAQ2vSphere 6 Hypervisor for Embedded OEMsNC6HH-26J4N-48098-Y0AX0-1GA66HY0J0-6L28H-081W8-4TCN0-32RP00V012-002DJ-480T1-UHAE0-9GULF4G6WR-A0K4M-08420-J8CN6-8A2QAvSphere 6 Desktop HostMF00U-65K53-H8DF9-Q20ZH-26R40JV6D2-6YLDN-088C0-Q92N2-8Z00AHG49R-2234J-08EJ8-WC9E2-87H2DNV6TR-D4HD1-M84L1-P19QM-0PK3DvSphere 6 for vCloud Service Provider StandardJG2JK-DM01L-M8101-J8AZH-3LUH2JZ41H-0X153-H8579-A32ZK-16H204A0HU-DU084-H8990-C9376-16KHF1V0EA-FNK51-H85K8-H1AN6-2U8PDvCloud Suite 6 StandardHV0N8-AC382-H8ET1-631X6-23K10HU4EA-A52E2-H89V9-T295K-0V8Q4NZ4XH-2VK43-H8D30-6L974-97800NU2DH-DW145-H81Y8-KL0QP-9GAQDvCloud Suite 6 AdvancedMU2HU-DEHEQ-M85U0-N3356-07A5DHC0R0-AZJE3-08EF1-T99E6-86R32HG0JK-F4J1M-08E49-L91Q2-8K8325U40R-D615H-M8EZ8-D82ZP-12848vCloud Suite 6 EnterpriseJY04H-DFJ01-M8869-428EM-96R605Y04R-AYKE3-H8DR8-NK0EM-1UAL2NC682-0GJ4P-08EM1-32ANM-0AU6FNZ2JR-6U286-H81N0-8V3Z6-1FHH2vSphere with Operations Management 6 StandardHZ4J8-AWJ4Q-M8560-YT3Q6-0F052JU24R-67H53-H8DF1-4L17K-8U208MY6DK-0K1D2-M80T0-UR1Z4-8CK42JU4E2-2W085-M8D78-M0AZM-AARKFvSphere with Operations Management 6 EnterpriseJA60R-2JK84-M8DQ1-4L9N0-8VRP2MG4T2-FY29P-480F9-9H0X0-16K1D1U65R-2Y24N-M8DR1-U2C50-16HQ81V2HU-26KD1-08D60-LH9GK-CPU6FvSphere 6 FoundationJA6R2-DELEK-H8EP8-83AXP-07RQ80G6EA-07J5M-M8991-TJ0Z6-2U87DNU61R-ADKD2-488M9-CV8NH-A6K300Y050-A3L42-084C9-6LCGP-3AR3DvSphere 6 Foundation for Embedded OEMsHA2JK-42HD4-H80C0-79CZ0-1L012JY2E2-6W11L-H81K0-0A274-3ZU364G6HU-FF05N-085L1-R0ANM-C705F4Y2TA-2R08P-H80H9-N317H-1ZK16vSphere 6 Embedded EssentialsNY6M2-D5H8L-489D0-280Z0-2Q004HA080-FP203-M8830-XH9QM-3PU2DJF0NH-AGJ0P-H8EW0-2U85H-AU0QAHU09A-6F04H-08E90-G28Z6-1A808vSphere 6 Embedded Foundation1A6H8-DT2D3-H8DR8-5L9EK-2AH184V088-D93DL-H89H9-3V8QH-C7H3DJU28A-DR3DN-489H9-JC8XH-CZ0521U69U-0AL9L-08171-UHCXK-9622AvSphere 6 EssentialsNG2T0-AZ08N-484V0-KRC52-87K2DJY412-8MKEH-489U0-KL2Q0-0ZUQ80F44K-85J17-489U8-JH9N6-8F0604G28H-0CLDQ-H80J0-V3AZ0-862KAvSphere 6 Essentials for Retail and Branch Offices4U6NH-4D0EK-08EU9-JLCZ6-AU0J40G690-4F3EQ-M80G9-AR35M-AAUQ0NV29K-8PLE3-480U1-T9970-A6RJD1Z2MK-6T243-484J0-6K954-AYULDvSphere 6 Essentials PlusMU2MR-6HH1P-48E11-8J3G4-2Y8NFHY0JR-AHK4H-08EM8-W22X6-0KU3F4Z0RH-0YK55-48089-820Z6-AC852JZ4HR-DJJ50-M84G0-LJ0G4-A22QFvSphere 6 Essentials Plus for Retail and Branch Offices1Y24U-6K05Q-48989-PR9G2-CA262NV0XK-6ML00-H8D21-FR954-AG0ND1G2MA-A2201-48DJ1-HC2E0-3ZA141V65R-0ZJ55-08870-KLAQH-0UA3FvSphere 6 StandardMG2T2-8FK0Q-H88H0-6RC50-9LU58JU600-6YH91-M8DR8-D1CGP-330NDHV2WH-D2192-M84K1-RLC56-1A862HG6R2-8NH52-088H9-X33QP-C68KDvSphere 6 Standard for Embedded OEMs4F6XU-ADK50-488K0-UJ0ZM-1U8J80Z29R-A028L-M80J9-3H3N6-8UU2D1Y68K-AG206-M8E38-298N0-8VUJDHF252-0X391-H8029-H835M-33834vSphere 6 Enterprise5Y04A-8934J-M85P1-3K37H-9P04DJA2NH-2Z191-08501-NCAX2-2VK4F0F2T2-DCK93-M88M8-7J9E4-16RJ0NU0EK-05100-M80N9-501N2-8G81DvSphere 6 Enterprise for Embedded OEMs0V050-0XL9N-M85A8-LU1GK-0UA52NG6H2-FT186-H8EV8-UH9NP-1F2085Y400-4G2EJ-481W8-R80GM-2K2NF4Z44H-4R19K-H88T8-CT87P-0CKMDvSphere 6 Enterprise Plus1F6XH-8VJ9L-481Y9-L835P-CFHHA1G28U-AW18P-08189-72AE6-9URP84Y29R-D034H-48561-RLAG0-ACU40JU640-0JKE3-088J9-GRCXK-2PRH6永久,激活,许可证 -
Oracle 基础命令 ORACLE基础命令一、用户管理创建用户create user username identified by password;[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 13 16:15:13 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> create user owner identified by 123456; User created. SQL>修改用户alter user username identified by password;SQL> alter user owner identified by 123456;------修改用户owner 的密码为123456 User altered. SQL>删除用户drop user username;SQL> drop user owner;------删除用户owner User dropped. SQL>查询当前用户下的所有表SQL> select table_name from user_tables;------查找当前用户下的所有表 TABLE_NAME -------------------------------------------------------------------------------- USERSINFO USERSID SQL>二、为用户授权、撤销授权oracle提供三种标准角色（role）:connect/resource和dba. connect role(连接角色) 临时用户，特指不需要建表的用户，通常只赋予他们connect role. connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。 拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回 （session）和其他 数据的链（link)。 resource role(资源角色) 更可靠和正式的数据库用户可以授予resource role。 resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和 (cluster)。 dba role(数据库管理员角色) dba role拥有所有的系统权限 包括无限制的空间限额和给其他用户授予各种权限的能力。 为用户授权grant connect, resource to username;SQL> grant connect,resource to owner; Grant succeeded. SQL>撤销对用户的授权revoke connect, resource from username;SQL> revoke connect,resource from owner; Revoke succeeded. SQL>三、创建表空间创建语法create tablespace 表间名 datafile '数据文件名' size 表空间大小;SQL> create tablespace test datafile '/u01/app/oracle/oradata/ORCL/test_data.dbf' size 500M; Tablespace created. SQL>表空间授权用户alter user username identified by password default tablespace tablespace_name;SQL> alter user owner identified by 123456 default tablespace test; User altered. SQL>删除表空间删除表空间及对应表空间文件drop tablespace tablespace_name including contents and datafiles cascade constraint;SQL> drop tablespace test including contents and datafiles cascade constraint; Tablespace dropped. SQL>四、创建表Oracle数据类型char(n)varcahr2(n)number(m,n)datelongraw(n)lang rawblob/clob/nclobbfile1-2000 Byte1-4000 Bytem=整数位n=小数位sysdate无1-2000无图片、视频、声音txt文本无创建表语法SQL> create table usersInfo( 2 id varchar2(20) primary key, 3 name char(8) not null, 4 phone number(11) unique, 5 class char(10), 6 sex char(2) 7 ); Table created. SQL>查看表字段desc table_name;SQL> desc usersInfo;------查询usersInfo表中的所有字段及数据类型的长度 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL VARCHAR2(20) NAME NOT NULL CHAR(8) PHONE NUMBER(11) CLASS CHAR(10) SEX CHAR(2) SQL>添加表字段alter table table_name add (column_name data_type(length));SQL> alter table usersInfo add (age number(3));------添加字段age Table altered. SQL> desc usersInfo Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL VARCHAR2(20) NAME NOT NULL CHAR(8) PHONE NUMBER(11) CLASS CHAR(10) SEX CHAR(2) AGE NUMBER(3) SQL>修改表字段长度alter table table_name modify (column_name data_type(length));SQL> alter table usersInfo modify (sex char(3));------修改sex列数据长度为3 Table altered. SQL> desc usersInfo; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL VARCHAR2(20) NAME NOT NULL CHAR(8) PHONE NUMBER(11) CLASS CHAR(10) SEX CHAR(3) AGE NUMBER(3) SQL>修改表字段名alter table table_name rename column column_name to new_column_name;SQL> alter table usersInfo rename column ID to user_id;------将列名ID更改为USER_ID Table altered. SQL> desc usersInfo Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NOT NULL VARCHAR2(20) NAME NOT NULL CHAR(8) PHONE NUMBER(11) CLASS CHAR(10) SEX CHAR(3) AGE NUMBER(3) SQL>修改数据类型alter table table_name modify (column_name data_type(length));SQL> alter table usersInfo modify (USER_ID number(20));------修改USER_ID 数据类型为number(20) Table altered. SQL> desc usersInfo; Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NOT NULL NUMBER(20) NAME NOT NULL CHAR(8) PHONE NUMBER(11) CLASS CHAR(10) SEX CHAR(3) AGE NUMBER(3) SQL>删除表字段alter table table_name drop column column_name;SQL> alter table usersInfo drop column phone;------从usersInfo表中删除PHONE字段列 Table altered. SQL> desc usersinfo; Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NOT NULL NUMBER(20) NAME NOT NULL CHAR(8) CLASS CHAR(10) SEX CHAR(3) AGE NUMBER(3) SQL>添加主键约束alter table table_name add primary key (column_name);SQL> alter table usersInfo add primary key (user_ID);------添加主键约束，一张表只能存在一个主键 Table altered. SQL>删除主键约束alter table table_name drop primary key;alter table usersInfo drop primary key;------删除主键约束添加外键约束ALTER TABLE 从表名 ADD CONSTRAINT 约束名 FOREIGN KEY(column_name ) REFERENCES 主表名(column);SQL> alter table usersinfo add constraint u_id foreign key (user_id) references usersid (id); ------将主表usersid中的id列作为从表usersinfo中的列user_id的外键 Table altered. SQL>删除外键约束SQL> alter table usersinfo drop constraint u_id;------删除表usersinfo中名为u_id的外键 Table altered. SQL>五、表数据操作插入数据语法insert into table_name values (value1,value2,......);SQL> insert into usersInfo values ('10001','张三',12345678901,'三类','bo'); 1 row created. SQL> select * from usersInfo; ID NAME PHONE CLASS SEX ----------- ---------------- ----------- ------------------------ ------ 10001 张三 1.2346E+10 三类 bo SQL>修改语法update table_name set column_name=new_value where column_name=value;SQL> update usersInfo set CLASS='III' where ID='10001'; 1 row updated. SQL> selet * from usersInfo SP2-0734: unknown command beginning "selet * fr..." - rest of line ignored. SQL> select * from usersInfo; ID NAME PHONE CLASS SEX ----------- ---------------------- ------------- --------------------- ------------- 10001 张三 1.2346E+10 III bo SQL>删除语法delete teble_name where column=value;SQL> delete usersInfo where ID=10001; 1 row deleted. SQL> select * from usersInfo; no rows selected SQL>
