Oracle数据库用户角色、表空间创建、删除命令

Oracle常⽤命令⼤全（很有⽤，做笔记）⼀、ORACLE的启动和关闭1、在单机环境下要想启动或关闭ORACLE系统必须⾸先切换到ORACLE⽤户，如下su - oraclea、启动ORACLE系统oracle>svrmgrlSVRMGR>connect internalSVRMGR>startupSVRMGR>quitb、关闭ORACLE系统oracle>svrmgrlSVRMGR>connect internalSVRMGR>shutdownSVRMGR>quit启动oracle9i数据库命令：$ sqlplus /nologSQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 31 13:53:53 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect / as sysdbaConnected to an idle instance.SQL> startup^CSQL> startupORACLE instance started.2、在双机环境下要想启动或关闭ORACLE系统必须⾸先切换到root⽤户，如下su － roota、启动ORACLE系统hareg －y oracleb、关闭ORACLE系统hareg －n oracleOracle数据库有哪⼏种启动⽅式说明：有以下⼏种启动⽅式：1、startup nomount⾮安装启动，这种⽅式启动下可执⾏：重建控制⽂件、重建数据库读取init.ora⽂件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora⽂件。

2、startup mount dbname安装启动，这种⽅式启动下可执⾏：数据库⽇志归档、数据库介质恢复、使数据⽂件联机或脱机，重新定位数据⽂件、重做⽇志⽂件。

oracle19c常用命令数据库启动、关闭和状态检查•启动数据库：SqlSQL> STARTUP; # 启动数据库，默认为读写模式SQL> STARTUP MOUNT; # 只装载数据库但不打开SQL> STARTUP OPEN READ ONLY; # 以只读模式打开数据库•关闭数据库：SqlSQL> SHUTDOWN; # 正常关闭数据库，等待所有会话结束SQL> SHUTDOWN IMMEDIATE; # 立即关闭数据库，断开所有会话SQL> SHUTDOWN TRANSACTIONAL; # 在事务结束后关闭数据库SQL> SHUTDOWN ABORT; # 强制立即关闭数据库，可能导致数据丢失•查看数据库状态：SqlSQL> SELECT DATABASE_STATUS FROM V$INSTANCE;SQL> SELECT INSTANCE_NAME, STATUS FROM V$INSTANCE;用户管理•创建用户并分配表空间：SqlCREATE USER username IDENTIFIED BY password DEFAULTTABLESPACE users QUOTA UNLIMITED ON users;•给用户授权：SqlGRANT connect, resource TO username;GRANTdba TO username; # 授予数据库管理员权限表空间操作•创建表空间：SqlCREATE TABLE SPACE new_tbs DATAFILE'/path/to/datafile.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;•删除表空间（需先离线）：SqlALTER TABLE SPACE tbs_name OFFLINE;DROP TABLESPACE tbs_name INCLUDING CONTENTS ANDDATAFILES;PDB(可插拔数据库)管理•创建PDB：SqlCREATE PLUGGABLE DATABASE pdb_nameADMIN USER admin_user identified by passwordFILENAME '/path/to/pdb.xml';•开启/关闭PDB：SqlALTER PLUGGABLE DATABASE pdb_name OPEN;ALTER PLUGGABLE DATABASE pdb_name CLOSE;其他常用操作•查询当前登录用户：SqlSELECT USER FROM DUAL;•备份数据库：Bashrman target / # 进入RMAN恢复管理器BACKUP DATABASE; # 执行全库备份•查询数据库版本信息：SqlSELECT * FROM v$version;数据查询与操作•查询表数据：SqlSELECT * FROM your_table;-- 或者特定列SELECT column1, column2 FROM your_table;•插入数据：SqlINSERT INTO your_table (column1, column2) VALUES('value1', 'value2');•更新数据：SqlUPDATE your_table SET column1 = 'new_value'WHEREcondition;•删除数据：SqlDELETE FROM your_table WHERE condition;索引管理•创建索引：SqlCREATE INDEX idx_your_table_column1 ONyour_table(column1);•删除索引：SqlDROP INDEX idx_your_table_column1;触发器和序列•创建序列：SqlCREATE SEQUENCE seq_your_sequenceSTART WITH1INCREMENT BY1NOMAXVALUE;•使用序列：SqlINSERT INTO your_table (id, column1) VALUES(seq_your_sequence.NEXTVAL, 'value1');•创建触发器：SqlCREATE OR REPLACE TRIGGER trg_your_triggerBEFORE INSERT ON your_tableFOR EACH ROWBEGIN:NEW.column1 := seq_your_sequence.NEXTVAL;END;事务控制•开始事务：SqlSET TRANSACTION;•提交事务：SqlCOMMIT;•回滚事务：SqlROLLBACK;•保存点设置与恢复：SqlSAVEPOINT your_savepoint;ROLLBACK TO your_savepoint;SQL性能分析•查看执行计划：SqlEXPLAIN PLAN FOR SELECT * FROM your_table;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 数据库审计•开启审计：SqlAUDIT ALL BY user_name BY ACCESS;•查看审计记录：SqlSELECT * FROM SYS.AUD$;。

1.查询数据库状态 select status from v$instance;查看数据库版本 select * from v$version;more /home/oracle/.bash_profile 查看数据库的运行环境listener.ora和tnsnames.ora：放在“/ORACLE_HOME/network/admin”子目录下，是SQL*Net V2正常工作所需的配置文件及整个网络的配置文件。

listener.ora是服务器监听进程网络配置文件，tnsnames.ora 是客户机与服务器联络所需的网络配置文件。

2、创建一个表空间，数据文件创建在/export/home/自己名字的目录，数据文件100MCREATE TABLESPACE test LOGGING DATAFILE ‘d:\dev\test.dbf' SIZE 100M;CREATE TEMPORARY TABLESPACE test_tmp tempfile '/export/home/test_tmp.dbf' SIZE 100M;3、创建用户，表空间使用自己创建的表空间。

create user test identified by test default tablespace test profile default;grant create session,create table to test;grant create view to test;grant create sequence to test;grant UNLIMITED TABLESPACE to test;commit;4、连接到自己创建的用户上Sql>Connect user/password5、创建表CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2));5、在表中插入数据INSERT INTO EMP VALUES(7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-12-1980', 'DD-MM-YYYY'), 800, NULL, 20);INSERT INTO EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1600, 300, 30);INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-02-1981', 'DD-MM-YYYY'), 1250, 500, 30);INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('02-04-1981', 'DD-MM-YYYY'), 2975, NULL, 20);INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1250, 1400, 30);6、修改表中数据update7、删除表中一条数据delete from8、查找表中一条数据 select9、删除表 drop table10、删除用户 drop user test cascade;11、删除表空间 drop tablespace test including contents and datafiles;1)创建表空间drop tablespace smsc_center including contents;create tablespace smsc_center datafile '/dev/rlv_dbsmsc1' size 2000M reuse;alter tablespace smsc_center add datafile '/dev/rlv_dbsmsc2' size 2000M reuse;(动态扩充表空间)2、建立用户drop user smsc cascade;create user smsc identified by oracle default tablespace smsc_center profile default; grant dba to smsc;grant unlimited tablespace to smsc;commit;3、连接刚建立的用户上，创建表（sm_histable0101、ms_usertable）SQL>connect smsc/oracleSQL>create table ms_usertable(MSISDN varchar2(21) NOT NULL ,SubName varchar2(21) NULL ,UserSex number(3) NULL ,UserAddr varchar2(41) NULL ,UserPass varchar2(9) NULL ,MSType number(3) NULL ,TON number(3) NULL ,NPI number(3) NULL ,Service number(3) NULL ,SMFlag number(3) NULL ,OCOS number(10) NULL ,TCOS number(10) NULL ,ExpireTime number(10) NULL,RegDateTime varchar2(21) NULL,Schedulemode number(3) NULL)TABLESPACE SMSC_CENTER;4、建立存储过程 process5、建立任务 job补充：1、错误码解释 ora-15241 $oerr ora 15421tns-12542 $oerr tns 12542imp-00013 $oerr imp 000132、倒表命令exp smsc/oracle tables=sm_histable0426 file=sm_histable0426 log=sm_histable0426imp smsc/oracle file=sm_histable0426 log=sm_histable0426 tables=sm_histable0426 ignore=y3、保存查询结果SQL> spool /home/oracle/text.txtSQL>select * from V$tablespace;SQL> spool off4、建立、删除裸设备mklv -y 'lv92_system' -t 'raw' datavg 256 [256*8(pp size)=2048]rmlv -f 'lv92_system'5、命令select orgaddr,destaddr from sm_histable0920 where error_code='48';查询0920从源地址到目的地址错误码为48的数据select * from sm_histable1228 where destaddr like'8613%' and orgaddr like'8613'%;count(*) 查询1228从源地址8613发往目的地址8613的所有数据analyze table sm_histable1228 compute statistics for all indexes (优化表索引)truncate table sm_histable1228; 清除表中所有数据，保留表结构，不可恢复数据delete from sm_histable1228; 没有commit前可以，用rollback命令回滚。

输入sqlplus system/orcl@orcl，回车(system是用户名，orcl是密码，@后面的orcl 是库的名字)（数据库安装完成后，有两个系统级的用户:1）system 默认密码为:manager2）sys 默认密码为:change_on_install）创建用户前必须要先建好临时表空间和数据表空间两个表空间，否则用系统默认的表空间不好。

//创建临时表空间create temporary tablespace zfmi_temp tempfile'D:/oracle/oradata/zfmi/zfmi_temp.dbf' size 100m autoextend on next 32m maxsize2048m extent management local;说明：1、zfmi_temp 表空间的名字2、D:/oracle/oradata/zfmi/ 存放数据库文件的地方，一般是安装数据库后有控制文件，数据文件和日志文件的文件夹,再加上要创建表空间的名字+dbf(数据文件)3、100M 表空间的初始大小4、32M 表空间自动增长的大小5、2048M 表空间最大的大小//创建数据表空间create tablespace zfmi logging datafile 'D:/oracle/oradata/zfmi/zfmi.dbf' size 100m autoextend on next 32m maxsize 2048m extent management local;//创建用户并指定表空间create user zfmi identified by zfmi default tablespace zfmi temporary tablespace zfmi_temp;//给用户授予权限grant connect,resource dba to zfmi; (dba：指定所有权限)到这一步新建一个用户的工作就完成了。

oracle中文使用手册Oracle是一款功能强大的关系型数据库管理系统，广泛应用于企业数据管理和应用开发领域。

本手册将详细介绍Oracle数据库的基本概念、常用命令和操作方法，以帮助初学者快速上手和熟练使用Oracle。

1. Oracle简介Oracle是美国Oracle公司开发的一种关系型数据库管理系统。

它是目前企业级应用系统首选的数据库产品之一，被广泛应用于各个行业的数据管理和应用开发中。

Oracle具有可靠性高、性能优异、安全性强等特点，成为大型企业数据处理的首选。

2. 安装与配置在开始使用Oracle之前，首先需要进行安装和配置。

可以从Oracle官方网站下载安装程序，根据提示进行安装。

安装完成后，需要进行一些基本的配置，如创建数据库实例、设置监听器等。

详细的安装和配置过程可以参考Oracle官方提供的文档或手册。

3. 数据库连接与登录使用Oracle数据库前，需要先进行数据库连接和登录。

可以使用SQL*Plus命令行工具或Oracle SQL Developer等图形化界面工具来进行连接和登录。

在连接时需要提供数据库的主机名、端口号和SID等信息，以及合法的用户名和密码。

连接成功后，即可开始对数据库进行操作。

4. SQL基本操作SQL是结构化查询语言，用于在关系型数据库中进行数据的增删改查等操作。

下面介绍一些常用的SQL命令：- 创建表: 使用CREATE TABLE语句可以创建数据表，并指定表的字段、数据类型、约束等信息。

- 插入数据: 使用INSERT INTO语句可以向表中插入数据。

- 更新数据: 使用UPDATE语句可以更新表中的数据。

- 删除数据: 使用DELETE FROM语句可以删除表中的数据。

- 查询数据: 使用SELECT语句可以查询表中的数据。

5. 数据库事务和锁机制数据库事务是指对数据库进行的一系列操作，要么全部执行成功，要么全部不执行。

Oracle提供了事务管理机制，可以确保数据库的一致性和完整性。

Oracle中建表和指定表空间--建⼀个表create table HH2(tid number primary key ,--主键设定tname varchar2(20));--删除表drop table HH;--表空间（相当于⼀个数据库）(DBA权限)create tablespace testdatafile 'D:test.dbf'size 10Mautoextend onnext 10Mmaxsize 100M--指定表在那个表空间⾥⾯(默认在USERS表空间⾥)create table HH(tid number primary key)tablespace test;select * from tabs;--删除表空间drop tablespace test including contents and datafiles --连带物理⽂件和表空间中的数据也⼀起删除--建表建约束create table student1(sid number primary key ,sname varchar2(20) not null,sage number,ssex char(2),saddress varchar2(100),cid number references tclass(cid)--建⽴外键关系);create table tclass(cid number primary key,cname varchar2(20));--唯⼀unique 检查 check 默认值 modify 添加外键关系添加列alter table student1 add constraint UQ_student1_sname unique(sname);alter table student1 add constraint CK_student1_agae check(sage between 19 and 70);alter table student1 modify ssex default '男';alter table student1 add constraint FK_student1_cid foreign key(cid) references tclass(cid);alter table student1 add dt date;--删除约束alter table student1 drop constraint UQ_student1_sname ;。

操作oracle 临时表空间的增删改查1、查看临时表空间dba_temp_files视图v_$tempfile视图select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;select status,enabled, name, bytes/1024/1024 file_size from v_$tempfile;--sys用户查看2、缩小临时表空间大小alter database tempfile 'D:\ORACLE\PRODUCT\' resize 100M;3、扩展临时表空间：方法一、增大临时文件大小：SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/’ resize 100m;方法二、将临时数据文件设为自动扩展：SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/’ autoextend on next 5m maxsize unlimited;方法三、向临时表空间中添加数据文件：SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/’ size100m;4、创建临时表空间：SQL> create temporary tablespace temp1 tempfile ‘/u01/app/oracle/oradata/orcl/’ size 10M;5、更改系统的默认临时表空间：--查询默认临时表空间select from database_properties whereproperty_name='DEFAULT_TEMP_TABLESPACE';--修改默认临时表空间alter database default temporary tablespace temp1;所有用户的默认临时表空间都将切换为新的临时表空间：select username,temporary_tablespace,default_ from dba_users;--更改某一用户的临时表空间：alter user scott temporary tablespace temp;6、删除临时表空间删除临时表空间的一个数据文件：SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/’ drop;删除临时表空间彻底删除：SQL> drop tablespace temp1 including contents and datafiles cascade constraints;7、查看临时表空间的使用情况GV_$TEMP_SPACE_HEADER视图必须在sys用户下才能查询GV_$TEMP_SPACE_HEADER视图记录了临时表空间的使用大小与未使用的大小dba_temp_files视图的bytes字段记录的是临时表空间的总大小SELECT ,total - used as "Free",total as "Total",roundnvltotal - used, 0 100 / total, 3 "Free percent"FROM SELECT tablespace_name, SUMbytes_used / 1024 / 1024 usedFROM GV_$TEMP_SPACE_HEADERGROUP BY tablespace_name temp_used,SELECT tablespace_name, SUMbytes / 1024 / 1024 totalFROM dba_temp_filesGROUP BY tablespace_name temp_totalWHERE =8、查找消耗资源比较的sql语句Select ,,,to_numberrtrim as Space,tablespace,segtype,sql_textfrom v$sort_usage su, v$parameter p, v$session se, v$sql swhere = 'db_block_size'and =and =and =order by ,9、查看当前临时表空间使用大小与正在占用临时表空间的sql语句select , segtype, blocks 8 / 1000 "MB", sql_textfrom v$sort_usage sort, v$session sess, v$sql sqlwhere =and =order by blocks desc;10、临时表空间组介绍1创建临时表空间组：create temporary tablespace tempts1 tempfile '/home/oracle/' size 2M tablespace group group1;create temporary tablespace tempts2 tempfile '/home/oracle/' size 2M tablespace group group2;2查询临时表空间组：dba_tablespace_groups视图select from dba_tablespace_groups;GROUP_NAME TABLESPACE_NAME------------------------------ ------------------------------GROUP1 TEMPTS1GROUP2 TEMPTS23将表空间从一个临时表空间组移动到另外一个临时表空间组：alter tablespace tempts1 tablespace group GROUP2 ;select from dba_tablespace_groups;GROUP_NAME TABLESPACE_NAME------------------------------ ------------------------------GROUP2 TEMPTS1GROUP2 TEMPTS24把临时表空间组指定给用户alter user scott temporary tablespace GROUP2;5在数据库级设置临时表空间alter database <db_name> default temporary tablespace GROUP2;6删除临时表空间组删除组成临时表空间组的所有临时表空间drop tablespace tempts1 including contents and datafiles;select from dba_tablespace_groups;GROUP_NAME TABLESPACE_NAME------------------------------ ------------------------------GROUP2 TEMPTS2drop tablespace tempts2 including contents and datafiles;select from dba_tablespace_groups;GROUP_NAME TABLESPACE_NAME11、对临时表空间进行shrink11g新增的功能--将temp表空间收缩为20Malter tablespace temp shrink space keep 20M;--自动将表空间的临时文件缩小到最小可能的大小ALTER TABLESPACE temp SHRINK TEMPFILE ’/u02/oracle/data/’;临时表空间作用Oracle临时表空间主要用来做查询和存放一些缓冲区数据;临时表空间消耗的主要原因是需要对查询的中间结果进行排序;重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,temp表空间会一直增长;直到耗尽硬盘空间;网上有人猜测在磁盘空间的分配上,oracle使用的是贪心算法,如果上次磁盘空间消耗达到1GB,那么临时表空间就是1GB;也就是说当前临时表空间文件的大小是历史上使用临时表空间最大的大小;临时表空间的主要作用：索引create或rebuild；Order by 或group by；Distinct 操作；Union 或intersect 或minus；Sort-merge joins；analyze.。

oracle 创建create user 及授权grant 查看登陆的用户：以下都可以：show user;select sys_context('userenv','session_user') from dual;select user from dual;查看所有登录的用户必须为DBA 用户：select username from v$session;sys、system等DBA 用户查看其他用户(test)中的对象(表)：SQL> select * from test.student;创建一个普通用户都把该用户用起来的流程：1、创建用户SQL>create user test indentified by test;这样就创建了一个用户名密码都为test的用户但这个时候test还是不能登陆成功的，我们需要赋予相应的权限2、赋予create session的权限SQL>grant create session to test;这样test用户就能成功登陆进去但是此时用户还是不能创建表我们需要赋予用户创建表的权限：SQL>grant create table to test;但是用户此时还不能创建表因为需要有使用表空间的权限（相当于用户有了进房间的钥匙但是没有进大门的钥匙。

）所以也应该赋予相应的权限SQL>grant unlimited tablespace to test;这个时候用户就拥有了创建表的权限由于表是用户test的相应的他就拥有了对创建的表的增删查改的权限了3、查看用户拥有什么权限可以通过查询一个系统的视图(数字字典)SQL>select * from user_sys_privs;这样就可以知道当前用户的权限4、撤销权限SQL> revoke create table from test;-----------------------------一些常用视图的区分dba_tables dba_all_tables user_tables user_all_tables all_tables all_all_tables当前用户所属的所有表(注意大写)SQL> select tablespace_name,table_name from user_all_tables where table_name='STUDENT'; SQL> select table_name,tablespace_name from user_tables where table_name='STUDENT'; TABLE_NAME TABLESPACE_NAME------------------------------ ------------------------------STUDENT USERSsys 要查看dba_all_tables,ALL_ALL_TABLES才能查看到test 用户的表。

oracle中快速删除某个⽤户下的所有表数据,删除Oracle某数据库
下的所有表
批量删除数据库中的表
select 'drop table '||table_name||';' as sqlscript from user_tables;
SQLSCRIPT-
drop table LO_CASEINFO;
drop table LO_HARMONIZECASE;
drop table LO_LAWCHECK;
drop table LO_LEGISLATIONITEM;
drop table LO_TRAINBATCH;
drop table OA_IMPRESS;
drop table OA_SYSGROUP;
就可以复制所有表的drop语句了，⼀次批量执⾏完。

还有另外⼀个⽅法imp时使⽤ignore参数，可以忽略已经存在的对象，免除⼿动删除表的操作：
参数说明：
Oracle在恢复数据的过程中，当恢复某个表时，该表已经存在，就要根据ignore参数的设置来决定如何操作。

若ignore=y，Oracle不执⾏CREATE TABLE语句，直接将数据插⼊到表中，假如插⼊的记录违反了约束条件，⽐如主键约束，则出错的记录不会插⼊，但合法的记录会添加到表中。

若ignore=n，Oracle不执⾏CREATE TABLE语句，同时也不会将数据插⼊到表中，⽽是忽略该表的错误，继续恢复下⼀个表。