Oracle随记

-- 通过rowid 快速检索数据是怎么做到的？

-- 登录sqlplus
sqlplus /nolog;

conn /as sysdba;

conn system/manager;

show user;

create table student as select * from all_objects;

select object_id from student where object_id = 29;

select /*+full(student)*/ object_id from student where object_id = 29;

-- 查看回滚段相关参数
show parameters undo;

-- 查看SGA
show parameters sga;

--查看PGA
show parameters pga;

-- 查看共享池
show parameters shared_pool_size;

-- 产看数据缓冲区
show parameters db_cache_size;

-- 查看日志缓冲区
show parameters log_buffer;

-- 内存参数
show parameters memory;

-- 修改属性
alter system set <parameter_name> = <value> scope = memory|spfile|both[sid = <sid_name>]

net share c$=c:
net share c$ /del

alter system set sga_target = 1000M scope = spfile;

-- oracle 实例名
show parameters instance_name;

-- 查看日志归档功能状态
archive log list;

-- 更改数据库的归档模式需要重启数据库，将数据库置于mount状态
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;

-- windows ORA-01012: not logged on
shutdown abort; -- 强制关闭数据库实例

-- 数据库启动分为三步
startup nomount; -- show parameter spfile - E:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA 记录控制文件位置
alter database mount;
-- 参数文件：控制文件位置
-- 控制文件：数据文件，日志文件，检查点等信息
alter database open;

-- 数据库关闭
shutdown immediate;

-- 参数文件文职
show parameter spfile;

-- 控制室文件位置
show parameter control;

-- 数据文件位置
select file_name from dba_data_files;

-- 日志文件的位置
select group#,member from v$logfile;

-- 归档文件位置
show parameter recovery;

-- 告警日志文件
show parameter dump;

-- 物理体系结构。逻辑结构（组织体系结构）分为 tablespace,segment,extent,block

-- 将共享池清空
alter system flush shared_pool;

-- 查询sql的语句，HASH，解析次数，执行次数等
select t.sql_text,t.sql_id,t.PARSE_CALLS,t.EXECUTIONS from v$sql t;
select t.sql_text,t.sql_id,t.PARSE_CALLS,t.EXECUTIONS from v$sql t where t.sql_text like '%from student%';

-- 设置日志关闭nologging，parallel 16 表示用到机器的16个CPU
create __ table t nologging parallel 64 as select rownum from dual connect by level <= 10;

-- 表空间分类：系统表空间，回滚段表空间，临时表空间，用户表空间

-- lob和long 的区别

-- 指定表空间建表
create table (id int) tablespace tbs_test;

-- 一般操作系统OS块大小为512k,数据库快一般设置为操作系统OS块容量的整数倍，这样可以减少IO操作
-- 数据块包括：数据块头（common and variable header）、表目录区（table directory）、行目录区（row directory）、可用空间区（free space）、行数据区（row data） 这5个部分

-- 查看block的大小
show parameter db_block_size;
-- 通过表空间试图dba_tablespaces查看block大小
select block_size from dba_tablespaces where tablespace_name = 'SYSTEM';

-- 创建表空间
create tablespace TBS_LJB
DATAFILE 'E:\oracle_tablespace\DATAFILE\TBS_LJB_01.DBF' SIZE 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

-- 不知道
col file_name formate a50
set linesize 366
-- 查看表空间
select file_name,tabLespace_name,autoextensible,bytes 
from DBA_DATA_FILES
where TABLESPACE_NAME = 'TBS_LJB'
ORDER BY SUBSTR(FILE_NAME,-12)

select default_tablespace from user_users;

select name from v$datafile;

Oracle 为用户指定表空间语法是alter user maclean default tablespace users。

Oracle 修改用户的表空间语法是alter table TABLE_NAME move tablespace TABLESPACENAME。

-- 修改表空间

-- 创建临时表空间
create temporary tablespace temp_ljb
tempfile 'E:\oracle_tablespace\DATAFILE\Tmp_LJB_01.DBF'size 100M;

-- 查看临时表空间
select file_name,bytes,autoextensible,tablespace_name from dba_temp_files where tablespace_name = 'TEMP_LJB';

-- 创建回滚段表空间
create undo tablespace undotbs2 datafile 'E:\oracle_tablespace\DATAFILE\UNDBS2.DBF' size 100M;

-- 查看回滚段表空间
select file_name,tablespace_name,autoextensible,bytes/1024/104
from DBA_DATA_FILES where TABLESPACE_NAME = 'UNDOTBS2'
ORDER BY SUBSTR(FILE_NAME, -12);

-- 查看系统表空间
select file_name,tablespace_name,autoextensible,bytes/1024/104
from DBA_DATA_FILES where TABLESPACE_NAME like 'SYS%'
ORDER BY SUBSTR(FILE_NAME, -12);

--
select tablespace_name,contents from dba_tablespaces where tablespace_name in ('TBS_LJB','TEMP_LJB','UNDOTBS2','SYSTEM','SYSAUX')
select * from dba_tablespaces where tablespace_name in ('TBS_LJB','TEMP_LJB','UNDOTBS2','SYSTEM','SYSAUX')

--查看Oracle数据库的用户名和密码
--运行 cmd 按如下输入命令
sqlplus / as sysdba ---------以sys登陆          超级用户（sysdba）
alter user 用户名 account unlock; --------- 解除锁定(必须带“;”号)
alter user 用户名 identified by 密码; -------------修改密码
--然后用你改好的密码登陆就行

-- 加入ljb 用户存在，先删除
drop user ljb cascade;
--创建用户，并将先前创建的表空间TBS_LJB和临时表空间temp_lib作为ljb用户的默认使用空间
create user ljb
identified by ljb
default tablespace TBS_LJB
temporary tablespace TEMP_LJB;
-- 授权，暂且将最大的权限分给ljb用户
grant dba to ljb
-- 可以登录ljb用户了
connect ljb/ljb

-- oracle 最小的逻辑单位是块（block），而最小的苦熬站单位是区（extent）

-- 查询数据字典获取extent相关信息
create table t (id int) tablespace TBS_LJB;
select * from t;
insert into t values(1);
select segment_name,segment_type,tablespace_name,bytes/1024/1024,blocks from user_extents where segment_name = 'T';

-- 查询数据字典获取segment 相关信息
select segment_name,segment_type,tablespace_name,bytes/1024/1024,blocks,extents from user_segments where segment_name = 'T';

-- 创建索引后查看段
create index idx_id on t(id);
select segment_name,segment_type,tablespace_name,bytes/1024/1024,blocks,extents from user_segments where segment_name = 'IDX_ID';

-- 块的大小在创建表空间的时候决定，需要在创建表空间时修改

show parameter cache_size;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
-- 这里意味这可以创建2，4，8，16，32KB 的块大小

-- 设置设置设置块大小
alter system set db_16k_cache_size = 100M;

-- 查看修改后块的大小
create tablespace TBS_LJB_16K
blocksize 16K
datafile 'E:\oracle_tablespace\DATAFILE\TBS_LJB_16K_01.DBF' size 100M
autoextend on
extent management local
segment space management auto;
select tablespace_name,block_size from dba_tablespaces where tablespace_name in ('TBS_LJB','TBS_LJB_16K') ;

-- PPCTFREE参数可针对段进行设置

-- 设置extent 每分配的大小
create tablespace TBS_LJB2
datafile 'E:\oracle_tablespace\DATAFILE\TBS_LJB2_01.DBF' size 100M
autoextend on
extent management local
uniform size 10M
segment space management auto;
-- 查看区的分配情况
create table t2 (id int) tablespace TBS_LJB2;
select * from t;
select * from t2;
insert into t2 values(2);
select * from user_segments;

select segment_name,extent_id,tablespace_name,bytes/1024/1024,blocks from user_extents where segment_name = 'T2'

-- 查看表空间已用空间大小？我怎么觉得时剩余空间-有问题
select bytes/1024/1024 from dba_free_space where tablespace_name = 'TBS_LJB';
select sum(bytes/1024/1024) from dba_free_space where tablespace_name = 'TBS_LJB';

-- 查看表空间总容量
select sum(bytes/1024/1024) from dba_data_files where tablespace_name= 'TBS_LJB';
select sum(bytes)/1024/1024 from dba_data_files where tablespace_name= 'TBS_LJB';
select * from dba_data_files;

-- 当extent扩展方式为NO时，该区被存满时，则无法继续插入数据
select autoextensible from dba_data_files where tablespace_name= 'TBS_LJB';
-- 解决方式一：增加数据文件，扩展表空间
alter tablespace TBS_LJB ADD DATAFILE 'E:\oracle_tablespace\DATAFILE\TBS_LJB_02.DBF'SIZE 100M;
-- 查看表空间大小
select bytes/1024/1024 from dba_free_space where tablespace_name = 'TBS_LJB';
-- 解决方案二：把表空间设置为自动扩展
col file_name format a50
select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files where tablespace_name = 'TBS_LJB';
alter database datafile 'E:\oracle_tablespace\DATAFILE\TBS_LJB_02.DBF' autoextend on;

-- 删除表空间：如果表空间有数据 需添加including contents and datafiles 表示要删除数据和对应的数据文件 （linux 及 unix 操作系统下增加and datafile 关键字可自动删除数据文件，而windows环境下需要手工删除）
drop tablespace TBS_LJB including contents and datafiles;
create tablespace TBS_LJB
DATAFILE 'E:\oracle_tablespace\DATAFILE\TBS_LJB_01.DBF'
autoextend on
extent management local
segment space management auto;
select file_name,tablespace_name,autoextensible,bytes/1024/1024 from dba_data_files where tablespace_name = 'TBS_LJB';

-- next 64k 表示每次以64k进行扩展，基本上等同于uniform 64k 的功能，控制表空间的最大尺寸不超过5G
create table space TBS_LJB3
datafile 'E:\oracle_tablespace\DATAFILE\TBS_LJB3_01.DBF'
autoextend on
next 64k
maxsize 5G;

-- oracle 10 以上版本 是可以取消这两行命令的，系统默认是区的本地管理和段的自动管理，早期版本中，区的管理是依据数据字典的，导致系统产生大量的递归调用，随后的版本改为通过区上的位图来标记管理区的扩展性能得以极大的提升，oracle 9i是oracle提供了这个新功能，而oracle10以上完全取消了数据字典管理功能。
extent management local
segment space management auto;

-- 查看当前使用的回滚段表空间
show parameter undo;
-- 查看回滚段表空间有哪些
select tablespace_name,status from dba_tablespaces where contents = 'UNDO';
select * from dba_tablespaces;
-- 查看回归段表空间的大小
select tablespace_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name in ('UNDOTBS1','UNDOTBS2') group by tablespace_name;

-- 切换回滚段表空间
alter system set undo_tablespace = undotbs2 scope = both;
show parameter undo;

-- 删除回滚段表空间
drop tablespace undotbs2;
*
第 1 行出现错误:
ORA-30013: 还原表空间 'UNDOTBS2' 当前正在使用中
alter system set undo_tablespace = undotbs1 scope = both;
drop tablespace undotbs2;

-- 回滚段表空间特点是，数据库中可以建多个，但正在使用的只能有一个，临时表空间的特点是，数据库可以建多个，却可以被同时使用

-- 查看临时表空间有哪些
select tablespace_name,sum(bytes)/1024/1024 from dba_temp_files group by tablespace_name;

--- 查看当前正在使用临时表空间
select default_tablespace,temporary_tablespace from dba_users where username = 'LJB';
select * from dba_users where username = 'LJB';
select default_tablespace,temporary_tablespace from dba_users where username = 'SYSTEM';

-- 切换临时表空间
alter user system temporary tablespace TEMP_LJB;
alter user system temporary tablespace TEMP;
-- alter database defualt temporary tablespace temp_ljb;

select temporary_tablespace,COUNT(*) from dba_users group by temporary_tablespace;
select * from dba_users;

-- oracle 10g 以后推出的临时表空间组，可以做到为统一用户的不同session设置不同的临时表空间，这样可以说缓解IO竞争方面再次迈出了大大一步
-- 查看临时表空间组
select * from dba_tablespace_groups;
-- 创建临时表空间组
create temporary tablespace temp1_1 tempfile 'E:\oracle_tablespace\DATAFILE\TEMP1_1.DBF' size 100M
TABLESPACE GROUP TMP_GRP1;
create temporary tablespace temp1_2 tempfile 'E:\oracle_tablespace\DATAFILE\TEMP1_2.DBF' size 100M
TABLESPACE GROUP TMP_GRP1;
-- 将已存在的临时表空间，移动临时表空间组中
alter tablespace temp_ljb tablespace group TMP_GRP1;

-- 将登录用户ljb的默认临时表空间更改为临时表空间组
alter user ljb temporary tablespace tmp_grp1;
alter user ljb temporary tablespace TEMP;

-- 查看用户的临时表空间
select * from dba_users where username = 'LJB';
-- 在ljb用户登录开启多个session同时执行如下排序操作，命令为；
select a.table_name,b.table_name from all_tables a,all_tables b order by a.TABLE_NAME; -- ora-01652：临时表空间不足
select * from all_tables;

-- 重启实例可以释放临时表空间

-- 虽然都是同一用户即ljb用户登录的，但是不同的session都是自动分配到了不同的临时表空间
select username,session_num,tablespace from v$sort_usage;
select * from v$sort_usage;

-- 消除行迁移的一个简单的方法是数据重建

-- 查看表存在行迁移
-- 首先建chained_rows
sqlplus "/as sysdba";
@?/rdbms/admin/utlchain.sql;
-- 以下命令针对产生行迁移的数据插入到 chained_rows表中
analyze table table_name list chained rows into chained_rows;
-- 然后查询 table_name 可知有多少行产生了行迁移
select count(*) from chained_rows where table_name = 'table_name';
-- 对存在行迁移的表解决方式；1、重建表。2、对PCTFREE 做适当的调整；

-- 查询表产生了多少日志
select a.name, b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.name = 'redo size';
-- 该脚本是利用v$statname 和,v$mystat 两个动态性能视图来跟踪当前session操作产生的日志量，使用方法很简单：首先先执行该脚本，查看日志大小，随即执行你的更新语句，再执行该脚本返回的日志大小，两者相减
-- 其中该视图需先 sysdba 登录授权后方可执行
grant all on v_$mystat to ljb;
grant all on v_$statname to ljb;

-- 创建视图，方便后续直接用select * from v_redo_size进行查询；
create or replace view v_redo_size as select a.name, b.value from v$statname a,v$mystat b where a.STATISTIC# = b.STATISTIC# and a.name = 'redo size';

-- delete 无法释放空间 truncate table table_name 可以释放表空间
-- delete 将数据块的记录删除了，但是块依然保留，oracle再查询的时候依然会去查询这些块，而trucate 是一种释放高水平位的动作，这些块被回收，空间也就被释放了

-- truncate 分区表
alter table table_name truncate partition '分区名';

-- oracle 为了减少访问路径提供了两种主要的技术，一种是索引技术，另一种则是分区技术。

-- 索引组织表：通过索引读后，避免回表

-- 避免使用order by 可以再需排序的列创建索引

-- 有序散列聚簇表：可以保证数据顺序插入，展现时无需再有排序动作；

-- 全局临时表：分为两中国：一种是居于会话的全局临时表（on commit preserve rows）,一种是基于事务的全局临时表（on commit delete rows）
-- 创建基于会话的全局看临时表
create global temporary table table_name_transaction on commit delete rows as select * from dba_object where 1=2;
-- 创建基于事务的全局临时表
create global temporary table table_name_transaction on commit preserve rows as select * from dba_object where 1=2;
-- 全局临时表的两大特性：一是，高效的删除数据，居于事务的全局临时表commit 或者 session 连接推出后，临时表记录自动删除；基于会话的全局临时表则是sessio连接退出后，临时表记录自动删除。二是，针对不同的会话数据独立，不同的session访问临时表，看到的结果不同；

-- 切换session ???????????
select * from v$mystat where rownum = 1;

-- 索引是由 根块（Root），茎块（Branch）， 叶子块（leaf） 三部分组成，主要存储key column value(缩影列具体值)，以及能具体定位到数据块所在位置的rowid（注意区分索引块和数据块）
-- 其中的 叶子块（leaf） 主要存储可key column value (索引列具体值) 以及能具体定位到数据块所在位置的rowid

-- 查询索引的高度
select index_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor from user_ind_statistics; -- where table_name in ('table_name')

-- 表的字段越少，全表扫描的开销就越少

-- 分区表的索引一般分两种：一种是全局索引，一种是局部索引

-- 查看聚合因子：聚合因子越小，回表查询的效率就越高

select index_name,blevel,leaf_blocks,num_rows,distinct_keys,clustering_factor,clustering_factor from user_ind_statistics; -- where table_name in ('table_name')


-- 另一种跟踪工具
set linesize 1000;
alter session set statistics_level = all;
-- 执行你的sql,完事之后，执行下面的语句
-- select * from t2;
select * from table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

-- distinct 排序技术采用的是 HASH UNIQUE 执行计划显示 SORT UNIQUE,如果是等值查询则会消除排序，执行计划显示 SORT UNIQUE NOSORT

-- INDEX FAST FULL SCAN 和 INDEX FULL SCAN的区别是，前者可以同时读多个索引块，后者需一个一个读，前者不保证有序，后者可以消除排序

-- 即便删除：在原有外键的基础上增加 on delete cascade
alter table T_C drop constraint FK_T_C;
alter table T_C add constraint FK_T_C foreign key (FID) references T_P (ID) on delete cascade;

-- 组合索引：等值列在前可以提高效率
-- 位图索引：

-- BTREE 索引存储的是列值，而位图索引村的是比特值

-- 哈希谅解不支持不等值连接<>，不支持> 和 < 的连接方式，也不支持LIKE 的连接方式
-- 排序合并连接不支持<> 的连接条件，也不支持LIKE 的连接条件，但是比起HASH 连接，支持面要广一些，支持> 之类的连接条件
-- 嵌套循环就是支持所有的SQL连接条件写法，没有任何的限制

-- 查看SID
select INSTANCE_NAME from v$instance;