青浦门户网站,松江团购做网站,土建网招聘信息,泰安房产网信息网官网oracle基础语法
在 Oracle 开发中#xff0c;客户端把 SQL 语句发送给服务器#xff0c;服务器对 SQL 语句进行编译、执行#xff0c;把执行的结果返回给客户端。常用的SQL语句大致可以分为五类#xff1a;数据定义语言#xff08;DDL#xff09;#xff0c;包括 CREAT…oracle基础语法
在 Oracle 开发中客户端把 SQL 语句发送给服务器服务器对 SQL 语句进行编译、执行把执行的结果返回给客户端。常用的SQL语句大致可以分为五类数据定义语言DDL包括 CREATE创建命令、 ALTER修改命令、 DROP删除命令等。数据操纵语言DML包括 INSERT插入命令、 UPDATE更新命令、 DELETE删除命令、 SELECT … FOR UPDATE查询等。数据查询语言DQL包括基本查询语句、 Order By 子句、 Group By 子句等。事务控制语言TCL包括 COMMIT提交命令、 SAVEPOINT保存点命令、ROLLBACK回滚命令。 数据控制语言DCL GRANT授权命令、 REVOKE撤销命令。select - 从数据库中提取数据update - 更新数据库中的数据delete - 从数据库中删除数据insert into - 向数据库中插入新数据create database - 创建新数据库altr database - 修改数据库create lable - 创建新表alter lable - 变更改变数据库表drop lable - 删除表create index - 创建索引搜索键drop index - 删除索引查看当前连接数据库的用户
show user查看数据库当前连接数
select count(*) from v$session;查看用户下的表
select * from tab;查看指定用户拥有的表
select table_name from dba_tables where ownerXIAOXIONG;用户切换
conn 用户名/密码 [as sysdba]conn sys/123456 as sysdba解锁用户
alter user sys(用户名) account unlock;alter user sys(用户名) identified by 123456(新密码);新建用户
create user xiaoxiong identified by 123456;create user student--用户名identified by 123456--密码default tablespace USERS--表空间名temporary tablespace temp --临时表空间名profile DEFAULT --数据文件默认数据文件account unlock; -- 账户是否解锁lock:锁定、unlock解锁)修改用户密码
alter user xiaoxiong identified by 123456789;删除用户
drop user xiaoxiong;赋予用户所有权限
grant dba to xiaoxiong;查看用户使用时间限制
select * from dba_profiles where resource_name PASSWORD_LIFE_TIME;查询密码有效时长
select * from dba_profiles where profileDEFAULT and resource_namePASSWORD_LIFE_TIME;查看所有用户密码到期日
select username,account_status,expiry_date,profile from dba_users;查看指定用户的过期时间
select username, expiry_date, created from dba_users where username XIAOXIONG;设置当前用户密码时长
alter profile default limit password_life_time 90;--90天期限
alter profile default limit password_life_time unlimited; --永久期限查看当前用户的系统权限和表级权限
select * from user_sys_privs;select * from user_tab_privs;查看用户下所有的表
select * from user_tables;查询当前用户所拥有的表的名称和所在的表空间名称
SELECT table_name,tablespace_name FROM user_tables;查询当前用户所拥有的所有对象的名称、类型、创建时间、状态等信息
SELECT object_name,object_type,created,status FROM user_objects;查询当前用户可以访问的表
SELECT table_name FROM all_tables;查询当前数据库中所有的表sys用户查看
SELECT table_name,tablespace_name,owner FROM dba_tables;查看当前用户的角色
select * from user_role_privs;赋予用户链接数据库权限
grant connect to xiaoxiong;赋予用户只可以创建实体但是没有创建数据结构的权限
grant resource to xiaoxiong;赋予用户登录的权限
grant create session to xiaoxiong;赋予用户表操作的权限
grant create table to xiaoxiong;赋予用户表空间操作的权限
grant unlimited tablespace to xiaoxiong;赋予用户访问任务表的权限 同理可以赋予update 和delete
grant select on table to xiaoxiong;取消用户对表操作的权限
rovoke create table from xiaoxiong;查询指定用户系统权限
select * from dba_sys_privs where grantee xiaoxiong;查询指定用户对象权限
select * from dba_tab_privs where grantee xiaoxiong;查询数据库名
select name from v$database;查询数据库实例名
select instance_name from v$instance;查看数据库归档状态
select log_mode from v$database;关闭数据库
shutdown immediate启动到mount
startup mount开启归档模式
alter database archivelog;打开数据库
alter database open;查看数据库运行状态
select status from v$instance;查看数据文件、控制文件、日志文件的物理路径
select * from dba_data_files;修改日志归档目录
alter system set log_archive_dest_1location/db/app/oracle/oradata/orcl;查看日志归档路径
show parameter log_archive_dest;查看归档日志格式
show parameter log_archive_format修改归档日志格式
alter system set log_archive_format archive_%t_%s_%r.log scopespfile;查看归档日志空间占用
show parameter db_recovery;创建表空间
create tablespace xiaoxiong datafile xiaoxiong.dbf size 20M;查询表空间的基本信息
SELECT tablespace_name,extent_management,segment_space_management,contents,status FROM dba_tablespaces;删除表空间
drop tablespace xiaoxiong;删除带内容的表空间
drop tablespace animal including contents and datafiles;查看表空间存放位置
select * from dba_data_files;查看所有表空间的大小
select tablespace_name ,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;查看所有表空间存放的服务器路径
select tablespace_name,file_name from dba_data_files;查询表空间
select name from v$datafile;查询表空间空闲大小
select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space;修改表空间大小
ALTER DATABASE datafile /db/app/oracle/product/11.2.0/dbs/daxiong.dbf RESIZE 50M;创建表
create table xiaoxiong(name varchar(12),age varchar(12)) tablespace daxiong;删除表
drop table xiaoxiong;插入表数据
insert into xiaoxiong(name,age) values(dada,22);批量插入表数据
INSERT ALL INTO da VALUES(11,aaa) INTO da VALUES(22,ccc) INTO da VALUES(33,ddd) SELECT * FROM da;修改表数据
update xiaoxiong set name xx where age 20;追加新的列
alter table xiaoxiong add(shengri varchar(12));修改列名
alter table xiaoxiong rename column shengri to data;删除列
alter table xiaoxiong drop column data;删除表内容
delete from xiaoxiong where age 20;将表移动到指定表空间
alter table table_name move tablespace_name;查询数据库字符集
SELECT PARAMETER ,VALUE FROM V$NLS_PARAMETERS WHERE PARAMETERNLS_CHARACTERSET;角色管理
数据库预定义角色
CONNECTCREATE SESSIONRESOURCECREATE SEQUENCE、CREATE TRIGGER、CREATE CLUSTER、CREATE PROCEDURE、CREATE TYPE、CREATE OPERATOR、CREATE TABLE、CREATE INDEXTYPEDBA基本全有查看所有角色
select * from dba_users;创建角色
CREATE ROLE role1;赋予角色权限
grant connect, resource to role1;撤销角色权限
revoke connect, resource from role1;查看系统同目前所有的角色
SELECT role FROM dba_roles;查询用户USER1所有用的角色和默认角色
SELECT granted_role,default_role FROM dba_role_privs WHERE granteeXIAOXIONG;查询角色role1中包含的系统权限
SELECT privilege,admin_option FROM role_sys_privs WHERE rolerole1;查看其它用户未提交的事务
SELECT START_DATE,START_TIME,START_SCN,START_SCNB,XID FROM v$TRANSACTION order by start_date , start_time;索引
CREATE [UNIQUE∣BITMAP] INDEX /*索引类型*/
[用户方案名.]索引名
ON 表名(列名 | 列名表达式 [ASC∣DESC] [,…n])
[LOGGING | NOLOGGING] /*指定是否创建相应的日志记录*/
[COMPUTE STATISTICS] /*生成统计信息*/
[COMPAESS | NOCOMPRESS] /*对复合索引进行压缩*/
[TABLESPACE 表空间名] /*索引所属表空间*/
[SORT | NOSORT] /*指定是否对表进行排序*/
[REVERSE]UNIQUE指定索引所 基于的列或多列值 必须唯一。默认的索引 是非唯一索引。Oracle 建议不要在表上显式定 义UNIQUE索引。BITMAP指定创建位图索引而不是 B*索引。位图索引保存的行标识符 与作为位映射的键值有关。位映射 中的每一位都对应于一个可能的行 标识符位设置意味着具有对应行 标识符的行包含该键值。用户方案名表示包含索 引的方案。若忽略则Oracle 在自己的方案中创建索引。ON子句在指定表的列中 创建索引ASC和DESC分别 表示升序索引和降序索引。列名表达式用指定表的列、 常数、SQL函数和自定义函数创 建的表达式用于创建基于函数 的索引。指定列名表达式以后用 基于函数的索引查询时必须保 证查询该列名表达式不为空。LOLOGGING | NOLOGGINGLOGGING 选项规定在创建索引时 创建相应的日志记录 NOGGING选项则表示创建 索引时不产生重做日志信 息默认为LOGGING。COMPUTE STATISTICSCOMPUTE STATISTICS选项表示在创建索引时 直接生成索引的统计信息这样可 以避免以后对索引进行分析操作。COMPAESS | NOCOMPRESS对 于复合索引如果指定了 COMPRESS选项则可以在创建 索引时对重复的索引值进行压缩 以节省索引的存储空间但对索 引进行压缩后将会影响索引的使 用效率默认为NOCOMPRESS。SORT | NOSORT默认情况下Oracle在 创建索引时会对表中的记录进行排序如 果表中的记录已经按照顺序排序可以指 定NOSORT选项这样可以省略创建索引 时对表进行的排序操作加快索引的创建 速度。但若索引列或多列的行不按顺序保 存Oracle就会返回错误默认为SORT。REVERSE指定以反序索引块的 字节不包含行标识符。 NOSORT不能与该选项一起指定。查看表中的索引SELECT * FROM ALL_INDEXES WHERE TABLE_NAMEDADA;查询指定表的索引列是哪个select * from all_IND_COLUMNS where table_name DADA;查询当前登录用户表索引select * from USER_INDEXES where table_name DADA;查询当前用户创建的表的索引列select * from USER_IND_COLUMNS where table_name DADA;B树索引为xiaoxiong的age列创建索引指定索引段在daxiong的表空间create index age_index on xiaoxiong(age) tablespace daxiong;普通索引create index index_text_txt on dada(age);索引的使用age列上的索引来查询age1的信息
SELECT * FROM dada WHERE age1;age上的索引来对年纪按照age进行排序
SELECT * FROM dada ORDER BY age;age上的索引来查询age大于22的信息
SELECT * FROM dada WHERE age22;唯一索引create unique index index_name on table_name(coiumn_name);位图索引create bitmap index index_name on table_name(column_name);组合索引create index index_name on table_name(column_name1column_name2);基于函数索引create index index_name on table_name(column_name) reverse;
create index index_name on table_name(upper(column_name));反向键索引create index index_name on table_name(column_name) reverse;重置索引alter index age_index rebuild;删除索引drop index age_index;将索引移动到指定的表空间alter index index_name rebuild tablespace tablespace_name;手动为索引分配空间alter index age_index allocate extent(size 256k);回收索引未使用的存储空间alter index age_index deallocate unused;分区
范围分区create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(partition p1 values less than (to_date(2023-02-01, yyyy-mm-dd)),partition p2 values less than (to_date(2023-03-01, yyyy-mm-dd)),partition pmax values less than (maxvalue)
);上面的例子中定义了3个分区所有create_time小于2023-02-01的数据不包含被分配在分区p1中。
所有create_time小于2023-03-01的数据不包含被分配在p2中。
所有create_time大于等于2023-03-01的数据被分配在pmax中如果没有这个分区那么插入大于等于2023-03-01的数据时会因为没有合适的存储分区而报错。查看指定分区select * from worker_202301 partition (WORKER20230129);添加分区ALTER TABLE worker_202301
ADD PARTITION WORKER20230131
VALUES LESS THAN
(TO_DATE( 2023-02-01 00:00:00,
SYYYY-MM-DD HH24:MI:SS,
NLS_CALENDARGREGORIAN));删除分区ALTER TABLE worker_202301(表名) DROP PARTITION WORKER20230131(分区名);列表分区列表分区适用于一个字段只有固定的几个值创建列表create table WORKER_202302
(id VARCHAR2(100) not null,name VARCHAR2(200),technology VARCHAR2(100),save_date DATE
)
partition by list (technology)
(partition technology_java values (java),partition technology_python values (python),partition technology_c values (c)
);create index IDX_WORKER_ID202301 on WORKER_202301 (ID)local;
create index IDX_WORKER_ID_NAME202301 on WORKER_202301 (ID, NAME)local;插入数据insert into worker_202302 (id,name,technology,save_date) values (1,哪吒,java,to_date(2023/2/1 22:45:19,yyyy-MM-dd hh24:mi:ss));
insert into worker_202302 (id,name,technology,save_date) values (2,云韵,java,to_date(2023/2/1 22:46:19,yyyy-MM-dd hh24:mi:ss));
insert into worker_202302 (id,name,technology,save_date) values (3,美杜莎,python,to_date(2023/2/2 00:45:19,yyyy-MM-dd hh24:mi:ss));查询分区数据SELECT * FROM worker_202302 PARTITION (technology_python);exp命令
导出scott用户的dept表和emp表exp scott/tiger FILE/home/oracle/scott1.exp TABLES(dept,emp)导出xiaoxiong用户的所有数据exp xiaoxiong/123456 file/home/oracle/test.exp fully导出scott用户的dept表但不导出表中的数据和索引exp scott/tiger FILE/home/oracle/soctt2.exp TABLESDEPT ROWSN INDEXESN导出分区emp表上的两个分区m和nexp scott/tiger FILE/home/oracle/scott3.exp TABLES(emp:m,emp:n)用户user1导出其他用户的表需要EXP_FULL_DATABASE系统权限exp user1/1234 FILE/home/oracle/scott4.exp TABLES scott.deptexpdp 命令
创建逻辑目录目录必须存在
create directory dpdata as /home/oracle/dump;按用户导出
expdp system/123456orcl schemasscott dumpfileexpdp.dmp DIRECTORYdpdata;导出整个数据库
expdp system/123456 DIRECTORYdpdata DUMPFILEfull.dmp FULLy;按表名导出
expdp system/123456orcl TABLESemp,dept dumpfileexpdp.dmp DIRECTORYdpdata;按查询条件导出
expdp system/123456orcl directorydpdata dumpfileexpdp.dmp Tablesemp queryWHERE deptno20;imp命令
system是登陆用户123456是用户密码orcl是数据库名称file是存放要导入文件的目录imp system/123456127.0.0.1:1521/orcl file/home/oracle/imp_test.dmp fullyimpdp 命令
导入到指定用户impdp scott/tiger DIRECTORYdpdata DUMPFILEexpdp.dmp SCHEMASscott;导入表空间impdp system/123456 DIRECTORYdpdata DUMPFILEtablespace.dmp TABLESPACESexample;导入数据库impdp system/123456 DIRECTORYdpdata DUMPFILEfull.dmp FULLy;RMAN备份和恢复
RMAN备份
非一致性备份整个数据库打开状态下全库备份数据库需处于归档模式
全量备份
RMAN backup database format /home/oracle/dump/full_%d_%s_%T_%p.bak;对于非一致性备份必须在备份完毕后手工对当前的redo-log进行归归档。 因为在使用备份恢复数据库时需要使用当前重做日志中的重做记录。
RMAN sql alter system archive log current;查看建立的备份集与备份片段的信息
RMAN list backup of database;表空间备份RMAN backup tablespace daxiong format /home/oracle/dump/full_%d_%s_%T_%p.bak;查看建立的表空间备份信息RMAN list backup of tablespace daxiong;查看某个表对应的序号及表空间select file_name, file_id, tablespace_name from dba_data_files where file_id in (select distinct file_id from dba_extents where segment_nameDA);查询表空间对应的数据文件及其序号select file_name, file_id, tablespace_name from dba_data_files;备份对应的表RMAN backup datafile 4 format /home/oracle/dump/full_%d_%s_%T_%p.bak;查看建立的表备份信息RMAN list backup of datafile 4;备份控制文件自动备份开启该参数后当在RMAN中执行backup或copy命令时RMAN都会对控制文件进行一次自动备份。可以通过format命令指定其存储路径RMAN CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO /home/oracle/dump/full_%d_%s_%T_%p.bak;单独手动备份RMAN backup current controlfile format /home/oracle/dump/full_%d_%s_%T_%p.bak;在执行其他备份的同时备份控制文件RMAN backup tablespace tb1 include current controlfile;备份归档重做日志单独备份重做日志RMAN backup archivelog all format /home/oracle/dump/full_%d_%s_%T_%p.bak;执行其他备份的同时备份归档日志RMAN backup database current controlfile **plus archivelog** format /home/oracle/dump/full_%d_%s_%T_%p.bak一致性状态下的全库备份脱机全库备份RMAN shutdown immediate
RMAN startup mount
RMAN backup database format /home/oracle/dump/full_%d_%s_%T_%p.bak;
RMAN alter database open;RMAN恢复
完全恢复和不完全恢复restore和recoverrestore 是还原文件级的恢复。就是物理文件还原。recover 是恢复数据级的恢复。逻辑上恢复比如应用归档日志、重做日志全部同步保持一致。常用命令restore 命令用于还原已经备份的数据文件。
1restore database 还原所有的数据文件。
2restore tablespace 还原特定表空间的数据文件。
3restore datafile 还原特定的数据文件。
4restore controlfile 还原控制文件。
5restore archivelog 还原归档日志文件。recover 命令当数据库需要应用归档日志文件恢复数据文件时使用recover命令。使用该命令数据库系统会自动应用归档的日志文件。
1recover database 恢复所有的数据文件。
2recover tablespace 恢复特定表空间的数据文件。
3recover datafile 恢复特定的数据文件。归档模式下的完全恢复模拟介质故障通过系统删除daxiong表空间的物理文件mv daxiong.dbf daxiong.dbf.bak恢复daxiong表空间RMAN restore tablespace daxiong;
RMAN recover tablespace daxiong;完全恢复启动数据库到加载状态RMAN shutdown immediate;
RMAN startup mount;执行恢复
RMAN restore database;
RMAN recover database;
