- 参数设置
—兼容oracle设置,静态参数，重启生效。
alter system set 'COMPATIBLE_MODE' =2 SPFILE;—创建的表是否为list表,当前会话生效。动态参数。
alter session set 'LIST_TABLE'= 0;—控制文件转换为文本文件。
./dmctlcvt TYPE=1 SRC=/dm8/DAMENG/dm.ctl DEST=/dm8/DAMENG/dm.txt
select * from v$bufferpool;— 修改缓冲区buffer参数
alter system set 'BUFFER'=500 spfile;— 执行commit不会对缓冲区刷盘，数据检查时才会刷盘。
—sql缓冲区大小修改alter system set 'CACHE_POOL_SIZE'=300 spfile;
select * from v$cacheitem;
select * from v$cachesql;
select * from v$cachepln;
select * from v$cachers;—字典缓冲区：
select * from v$dynamic_tables t where T.NAME like '%DICT%';
SELECT * FROM SYS."V$DICT_CACHE_ITEM";
select * from v$parameter where name like 'DICT_BUF_SIZE';—内存池大小。
select * from v$mem_pool;—调整排序区大小
alter system set 'SORT_BUF_SIZE'=100 spfile;—查看进程
select * from v$process;
select * from v$threads;- 表空间管理
system roll main temp hmain
select * from v$parameter t where name like 'TEMP%';—设置临时表空间大小
alter system set 'TEMP_SIZE'=100 SPFILE;—创建表空间;页大小的4096倍
create TABLESPACE tbs DATAFILE 'TBS.DBF' SIZE 36;
alter TABLESPACE tbs RENAME DATAFILE 'TBS01.DBF' TO '/dm8/data/DM/TBS/TBS01.DBF';
create table t_test(id int,name VARCHAR(20)) TABLESPACE tbs;
insert into t_test(id, name) values(1, 'aaa');
insert into t_test(id, name) values(2, 'aaa');
insert into t_test(id, name) values(3, 'aaa');
insert into t_test(id, name) values(4, 'aaa');
insert into t_test(id, name) values(5, 'aaa');
commit;
select * from SYSDBA.T_TEST;—迁移表空间数据文件:
alter tablespace tbs offline;
alter TABLESPACE tbs RENAME DATAFILE 'TBS01.DBF' TO '/dm8/data/DM/TBS/TBS01.DBF';
alter TABLESPACE tbs RENAME DATAFILE 'TBS02.DBF' TO '/dm8/data/DM/TBS/TBS02.DBF';
alter tablespace tbs online;- 联机日志
—DM 数据库联机日志自动切换,不能手工切换。
—数据字典:
select * from v$rlogfile;
select * from v$rlog;—修改联机日志大小
ALTER database RESIZE LOGFILE '/dm8/DAMENG/DAMENG01.log' TO 300;
ALTER database RESIZE LOGFILE '/dm8/DAMENG/DAMENG02.log' TO 300;—修改联机日志文件路径(迁移联机日志文件)
alter database mount;
alter database RENAME LOGFILE 'DM01.log' TO '/dm8/data/DM/REDO/DM01.log';
alter database RENAME LOGFILE 'DM02.log' TO '/dm8/data/DM/REDO/DM02.log';
alter database RENAME LOGFILE 'DM03.log' TO '/dm8/data/DM/REDO/DM03.log';
alter database open;- 密码策略
select * from v$parameter where name ='PWD_POLICY';
alter SYSTEM SET 'PWD_POLICY'=15 BOTH;—创建用户
—创建用户(注意特殊符号加引号)
create user hrtest IDENTIFIED by "Dameng@123" DEFAULT TABLESPACE TBS;- 用户管理
—用户锁定和解锁：
alter user hr ACCOUNT UNLOCK;
alter user hr ACCOUNT LOCK;—修改用户的默认表空间：
alter user hr DEFAULT TABLESPACE dmtbs;—删除用户:
drop user if EXISTS hr;
drop user if EXISTS hr CASCADE ;- 权限管理
grant create table to hrtest ;—赋予对象查询权限
grant select on dmhr.employee TO hrtest;—回收权限
revoke CREATE TABLE FROM hrtest;
revoke select on dmhr.employee FROM hrtest;—查看当前用户权限。
select * from session_privs;—赋予权限精确到列
grant select (employee_id,employee_name) on dmhr.employee to hrtest;
select employee_id,employee_name FROM dmhr.EMPLOYEE;—开启其他模式的授予权限
alter SYSTEM set 'ENABLE_DDL_ANY_PRIV' = 1 both;
grant create any table to hrtest;
grant create any index to hrtest;- 角色管理
select * from dba_roles;
create ROLE r1;
grant create table to r1;
grant r1 TO hrtest;—角色禁用启用
sp_set_role('R1',0) --禁用—赋予权限可转授
grant select on "DMHR"."EMPLOYEE" to "r2" WITH grant option;—回收时加上CASCADE
revoke select on dmhr.employee from r2 CASCADE;- 模式管理
select * from SYSOBJECTS t where t."TYPE$" ='SCH'; --查看模式
select * from SYSOBJECTS t where t."SUBTYPE$" ='USER'; --查看用户—查看模式和用户的关系
select a.id, a.name, b.id, b.name from SYSOBJECTS a, SYSOBJECTS b where a.pid = b.id and a."TYPE$" = 'SCH';—创建模式
create schema hrtest01 AUTHORIZATION HRTEST;
create table hrtest01.t_test(id int ,name VARCHAR(20));—切换模式
set SCHEMA dmhr;—删除模式
drop SCHEMA IF EXISTS HRTEST01;
drop SCHEMA IF EXISTS HRTEST01 CASCADE; --级联删除模式下对象，生产环境慎用。- 管理表
select * from v$parameter t where name ='LIST_TABLE';create TABLE hrtest.t_testpid(pid int,pname VARCHAR,sex BIT,logtime DATETIME) tablespace TBS;create table hrtest.t_emp as select * from DMHR.EMPLOYEE where 1=0;l—ike 创建表（只复制表结构，不复制约束、主外键等信息）:
create table hrtest.t_emp01 like DMHR.EMPLOYEE;—添加字段
alter TABLE hrtest.T_TESTPID ADD COLUMN email VARCHAR(20);—修改字段类型：
alter table hrtest.t_testpid modify email varchar(50);—删除字段：
alter table hrtest.t_testpid drop logtime;—对字段添加默认值（大表不建议添加字段时给默认值）：
alter table hrtest.t_testpid add COLUMN logtime datetime DEFAULT sysdate;- 数据的导入
SQL> start /dm8/backup/JOB.sql
SQL> ` /dm8/backup/JOB.sql- 管理约束
约束类型：
NOT NULL：非空约束
UNIQUE：唯一约束
PRIMARY KEY：主键约束 (唯一约束+非空约束)
FOREIGN KEY：外键约束
CHECK：检验约束
select * from hrtest.t_testpid;
alter table hrtest.t_testpid modify pname not null;--非空约束
alter table hrtest.t_testpid add CONSTRAINT uk_testpid_email unique (email);--唯一键
alter table HRTEST.T_TESTPID ADD CONSTRAINT pk_pidtest_pid PRIMARY KEY(pid);--主键
alter table HRTEST.T_TESTPID ADD CONSTRAINT chk_pidtest_salary CHECK (salary>=2000);--检查
alter table hrtest.t_test add CONSTRAINT fk_test_id FOREIGN KEY(id) REFERENCES hrtest.t_testpid(pid);--外键约束（外键引用两一张表的主键或者唯一键）—约束的禁用和启用、删除
alter table hrtest.t_test disable CONSTRAINT fk_test_id;
alter table hrtest.t_test enable CONSTRAINT fk_test_id;
alter table hrtest.t_test drop CONSTRAINT fk_test_id;—管理索引
create index ix_emp01_employeename ON HRTEST.T_EMP01(EMPLOYEE_NAME);--创建
alter index HRTEST.IX_EMP01_EMPLOYEENAME MONITORING USAGE; --开启索引监控
alter index HRTEST.IX_EMP01_EMPLOYEENAME NOMONITORING USAGE; --关闭索引监控
select * from v$object_usage;
alter index HRTEST.IX_EMP01_EMPLOYEENAME rebuild ONLINE;--索引重建
drop index HRTEST.ix_emp01_employeename;--删除索引- 视图管理
create VIEW hrtest.v_emp
as
SELECT a.EMPLOYEE_ID,a.EMPLOYEE_NAME,a.EMAIL,a.PHONE_NUM
FROM dmhr.employee a
where a.DEPARTMENT_ID=1001;- 数据备份恢复
—dmrman备份
RMAN> backup database '/dm8/DM/dm.ini';
RMAN> restore database '/dm8/DM/dm.ini' from backupset '/dm8/backup/****';
RMAN> recover database '/dm8/data/DM/dm.ini' with archivedir '/dm8/arch';
RMAN> recover database '/dm8/data/DM/dm.ini' update db_magic;—逻辑备份
./dexp SYSDBA/SYSDBA directory=/dm8/backup/ file=fulldexp.dmp log=fulldexp.log full=y
./dimp sysdba/SYSDBA directory=/dm8/backup/ file=fulldexp.dmp log=fulldexp.log full=y—执行完全检查点
checkpoint(100); --执行完全检查点
评论
本文评论功能已关闭。