本文共 10850 字，大约阅读时间需要 36 分钟。

1.用户操作

-- Oracle自带用户sys; -- 系统管理员，拥有最高权限system; -- 本地管理员，次高权限scott; -- 普通用户，密码默认为tiger,默认未解锁--二、登陆sqlplus / as sysdba; -- 登陆sys帐户sqlplus sys as sysdba; --- 同上sqlplus scott/tiger; -- 登陆普通用户scott--三、管理用户create user username identified by password;--在管理员帐户下，创建用户usernamealert user scott identified by tiger;--修改密码-- 解锁用户ALTER USER username ACCOUNT UNLOCK;--修改用户默认表空间alter user username default tablespace tablespace_name;-- 级联删除用户drop user username cascade;--四，授予权限-- 授予用户管理员权限：grant dba to username;--1、默认的普通用户scott默认未解锁，不能进行那个使用，新建的用户也没有任何权限，必须授予权限grant create session to username;--授予username用户创建session的权限，即登陆权限grant unlimited tablespace to username;--授予username用户使用表空间的权限grant create table to username;--授予创建表的权限grant drop table to username;--授予删除表的权限grant insert table to username;--插入表的权限grant update table to username;--修改表的权限grant all to public;--这条比较重要，授予所有权限(all)给所有用户(public)--2、oralce对权限管理比较严谨，普通用户之间也是默认不能互相访问的，需要互相授权grant select on tablename to username;--授予username用户查看指定表的权限grant drop on tablename to username;--授予删除表的权限grant insert on tablename to username;--授予插入的权限grant update on tablename to username;--授予修改表的权限grant insert(id) on tablename to username;grant update(id) on tablename to username;--授予对指定表特定字段的插入和修改权限，注意，只能是insert和updategrant alert all table to username;--授予username用户alert任意表的权限--五、撤销权限--基本语法同grant,关键字为revoke--六、查看权限select * from user_sys_privs;--查看当前用户所有权限select * from user_tab_privs;--查看所用用户对表的权限--七、操作表的用户的表select * from username.tablename--八、权限传递--即用户A将权限授予B，B可以将操作的权限再授予C，命令如下：grant alert table on tablename to username with admin option;--关键字 with admin optiongrant alert table on tablename to username with grant option;--关键字 with grant option效果和admin类似--九、角色--角色即权限的集合，可以把一个角色授予给用户create role myrole;--创建角色grant create session to myrole;--将创建session的权限授予myrolegrant myrole to username;--授予username用户myrole的角色drop role myrole;删除角色--赋予某个用户操作此用户的权限GRANT SELECT/update/insert/delete on PMLF_TCMA_GD_XC.Lf_Ctrl_Trade TO PMLF_GD;--十、查看所有用户select * from dba_users;   select * from all_users;   select * from user_users;

2.表空间操作

-- 创建表空间/* 第1步：创建临时表空间  */create temporary tablespace username_temptempfile 'E:\app\ACGkaka\oradata\orcl\username_temp.dbf'size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步：创建数据表空间  */create tablespace username_data logging datafile 'E:\app\ACGkaka\oradata\orcl\username_data.dbf'size 50m autoextend on next 50m maxsize 20480m extent management local; /* 在Linux下创建表空间 */CREATE TABLESPACE PMLF_TCMA_GD_BIG        DATAFILE '/opt/oracledb/PMLF_TCMA_GD_BIG.dbf'          SIZE 500 M          AUTOEXTEND ON NEXT 32 M MAXSIZE UNLIMITEDLOGGING        DEFAULT NOCOMPRESS        ONLINE        PERMANENT        EXTENT MANAGEMENT LOCAL AUTOALLOCATE/*第3步：创建用户并指定表空间  */create user username identified by username default tablespace username_data temporary tablespace username_temp; /*第4步：给用户授予权限  */grant connect,resource,dba to username;-- 删除表空间drop tablespace tablespace_name-- 重命名表空间alter tablespaces tablespace_name rename to new_tablespace_name;  -- 查看表空间的名称及大小SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_sizeFROM dba_tablespaces t, dba_data_files dWHERE t.tablespace_name = d.tablespace_nameGROUP BY t.tablespace_name;-- 查看表空间物理文件的名称及大小SELECT tablespace_name,file_id,file_name,round(bytes / (1024 * 1024), 0) total_spaceFROM dba_data_filesORDER BY tablespace_name;--查看表空间的使用情况SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_nameFROM dba_free_spaceGROUP BY tablespace_name;SELECT a.tablespace_name,a.bytes total,b.bytes used,c.bytes free,(b.bytes * 100) / a.bytes "% USED ",(c.bytes * 100) / a.bytes "% FREE "FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free cWHERE a.tablespace_name = b.tablespace_nameAND a.tablespace_name = c.tablespace_name;

3.数据库信息查看

-- 查看oracle数据库的versionselect * from v$version;-- 查看所有表SELECT * FROM user_tables;-- 查看回滚段名称及大小SELECT segment_name,tablespace_name,r.status,(initial_extent / 1024) initialextent,(next_extent / 1024) nextextent,max_extents,v.curext curextentFROM dba_rollback_segs r, v$rollstat vWHERE r.segment_id = v.usn(+)ORDER BY segment_name;-- 查看控制文件SELECT NAME FROM v$controlfile;-- 查看日志文件SELECT MEMBER FROM v$logfile;--7、查看数据库库对象SELECT owner, object_type, status, COUNT(*) count#FROM all_objectsGROUP BY owner, object_type, status;--8、查看数据库的版本　SELECT versionFROM product_component_versionWHERE substr(product, 1, 6) = 'Oracle';--9、查看数据库的创建日期和归档方式SELECT created, log_mode, log_mode FROM v$database;--1G=1024MB--1M=1024KB--1K=1024Bytes--1M=11048576Bytes--1G=1024*11048576Bytes=11313741824BytesSELECT a.tablespace_name "表空间名",total "表空间大小",free "表空间剩余大小",(total - free) "表空间使用大小",total / (1024 * 1024 * 1024) "表空间大小(G)",free / (1024 * 1024 * 1024) "表空间剩余大小(G)",(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",round((total - free) / total, 4) * 100 "使用率 %"FROM (SELECT tablespace_name, SUM(bytes) freeFROM dba_free_spaceGROUP BY tablespace_name) a,(SELECT tablespace_name, SUM(bytes) totalFROM dba_data_filesGROUP BY tablespace_name) bWHERE a.tablespace_name = b.tablespace_name;--十六、查询某个表的字段都被哪些表引用为外键select c.TABLE_NAME tablename,c.constraint_name foreignname,u.column_name columnnamefrom all_constraints p, all_constraints c,user_cons_columns uwhere p.table_name = 'TABLE_NAME'and p.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')and c.OWNER=SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')and c.constraint_type = 'R'and c.CONSTRAINT_NAME=u.constraint_nameand p.CONSTRAINT_NAME = c.R_CONSTRAINT_NAME

4.表结构操作

--更换数据库某张表的表名(备份时常用)--直接更换ALTER TABLE 当前表名字 RENAME TO 新表的名字--或者rename 当前表名字 to 新表的名字 ;--建新表删旧表create new_table as select * from old_table;drop table old_table;--修改数据表名 ALTER TABLE OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME; --修改列名 ALTER TABLE TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME; --修改列的数据类型 ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME NEW_DATATYPE; --修改VARCHAR2列的字段长度ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME VARCHAR2(你需要的值); --删除列 ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;--添加字段 ALTER TABLE 表名 ADD 字段 NUMBER(15,4);--删除当前用户下所有表select 'drop table '||table_name||';' from user_tables;  --查询表主键及主键名称SELECT A.CONSTRAINT_NAME,  A.COLUMN_NAME  FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B  WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME  AND B.CONSTRAINT_TYPE = 'P' AND A.TABLE_NAME = 'GH_BAS_TEMP_STATION'--删除表主键ALTER TABLE GH_BAS_TEMP_STATION DROP CONSTRAINT PK_GH_BAS_TEMP_STATION;--添加表主键ALTER TABLE GH_BAS_TEMP_STATION ADD CONSTRAINT PK_GH_BAS_TEMP_STATION PRIMARY KEY(PROJECTID, BPAID, STATIONID);--删除所有表select 'drop table '||table_name||';' from cat where table_type='TABLE' 

4.SQL标准格式

-- 插入：insert into 表名 (字段1, 字段2, 字段3, ...) values (值1, 值2, 值3, ...);--更新：update 表名  set 字段名 = 插入值  where 条件-- 存在即更新，不存在则插入：begin	--写 update 语句，记住，分号";"结尾	if sql%rowcount = 0 then	--写 insert 语句，记住，分号";"结尾	end if;end;-- MERGE 针对同一张表MERGE INTO mn aUSING (select count(*) co from mn where mn.ID=4) bON (b.co<>0)--这里使用了count和<>，注意下，想下为什么！WHEN MATCHED THENUPDATESET a.NAME = 'E'where a.ID=4WHEN NOT MATCHED THENINSERTVALUES (4, 'E');

5.特殊场景

-- 1.复制一张表中的输入到另一张表中/* 1)两张表字段完全相同的情况下 */insert into tablename1 select * from tablename2/* 2)复制部分数据 */insert into 表A(字段1,字段2,...) select 字段1,字段2,... from 表B -- 2.取某一行数据的最大、最小值/* 1)取得col1, col2, col3...字段中最大的值 */greatest(col1, col2, col3...)/* 2)同理，取得col1,col2,col3...字段中最小的值 */least(col1, col2, col3....)/* 3)计算某一行数据的平均值 */(nvl(col1,0)+nvl(col2,0)+nvl(col3,0)+...+nvl(coln,0))/n-- 3.判断一个列的值，例子如下，加入COL大于95，则为0，否则不变CASE WHEN COL > 95 THEN 0 ELSE COL END

7.Oracle 递归查询

--找子集select * from Lf_Ctrl_Module start with MODULEID = '01' connect by prior MODULEID=UPMODULEID--找父集select * from Lf_Ctrl_Module start with MODULEID = '0101' connect by prior UPMODULEID=MODULEID

8.Oracle回收站

--1、查询回收站SELECT t.object_name,t.type ,t.original_name FROM user_recyclebin t;SELECT count(*) FROM user_recyclebin t;--2、回收站清理purge recyclebin ;--3、禁用回收站：ALTER SESSION SET recyclebin = OFF; -- 作用于会话状态ALTER SYSTEM SET recyclebin = OFF; -- 作用于系统状态--4、启用回收站：ALTER SESSION SET recyclebin = ON; -- 作用于会话状态ALTER SYSTEM SET recyclebin = ON;-- 作用于系统状态--5、单独删除PURGE TABLE BIN$5l669dZGTlGbDXj0fB80Gw==$0;--6、恢复回收站中的表flashback table BIN$5l669dZGTlGbDXj0fB80Gw==$0 to before drop; 

8.导出导入

--导出多张表：exp username/password@orcl tables=(table1,table2,table3,table4,...) file=e:\table.dmp--导出单张表：exp username/password@orcl tables=table1 file=e:\table.dmp--导出整个数据库：--1、本地导出exp username/password@orcl file=e:\table.dmp owner=username--2、远程导出exp username/password@192.168.0.1/orcl file=e:\table.dmp owner=username--3、导出所有表结构不要数据：exp username/password@orcl file=e:\table.dmp owner=username rows = n--导入数据库imp username/password@orcl file=e:\table.dmp full=y commit = y

9.Oracle 11g空表不导出

--Oracle 11g的新特性，数据条数是0时不分配segment，所以就不能被导出。--解决方案：--1、先执行此语句生成分析表的SQL，copy出来，然后打开新的SQL窗口执行select 'analyze table '||table_name||' compute statistics;' from user_tables;--2、执行sql，查看有哪些是空表select table_name from user_tables where NUM_ROWS=0; --3、通过select 来生成修改语句select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0; --4、将上面生成的修改语句，copy出来，然后打开新的SQL窗口执行即可 

10.用户过期

-- 修改用户过期时间为永久select * from Dba_Profiles A WHERE A.profile = 'DEFAULT'  AND A.resource_name = 'PASSWORD_LIFE_TIME';ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

11.修改最大连接数

--查看连接线程数SELECT COUNT(*) FROM V$PROCESS;--查询连接线程数配置表select * from v$parameter where name ='processes';--统计数据库连接的消耗情况select  b.MACHINE, b.PROGRAM, b.USERNAME, count(*) from v$process a, v$session b where a.ADDR = b.PADDR and  b.USERNAME is not null  group by  b.MACHINE, b.PROGRAM, b.USERNAME order by count(*) desc--修改最大连接数，重启监听和服务后生效alter system set processes = 1000 scope = spfile;

其他数据库问题处理

--1、金仓数据库select SYSTIMESTAMP from dual;--获取系统时间到年月日时分秒select sysdate from dual;--获取系统时间到年月日--2、MySQL (Linux下安装)--1)登录mysqlmysql -u root -p--然后输入密码即可--2)授权可以远程登陆grant all privileges on *.* to 'root' @ '%' identified by 'root';--刷新权限,刷新后才可生效flush privileges;--通过设置Linux的防火墙，开启3306端口，这个实在Linux命令下执行/sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT--重新刷新防火墙/etc/rc.d/init.d/iptables save-- 删除当前用户下数据库中所有表SELECT 'DROP TABLE '|| table_name || ';' FROM USER_TABLES ORDER BY TABLE_NAME;-- 查看是否开启分区select * from v$option where parameter = 'Partitioning'; -- 全库查询DECLARE  CURSOR cur_query IS    SELECT table_name, column_name, data_type FROM user_tab_columns;    a NUMBER;    sql_hard VARCHAR2(2000);    vv NUMBER;  BEGIN    DBMS_OUTPUT.ENABLE(buffer_size => null);  FOR rec1 IN cur_query LOOP    a:=0;    IF rec1.data_type ='VARCHAR2' THEN    a := 1;    END IF;    IF a>0 THEN    sql_hard := '';    sql_hard := 'SELECT COUNT(*) FROM  '|| rec1.table_name ||' WHERE '  ||rec1.column_name  || '=''待查询内容''';  dbms_output.put_line(sql_hard);     EXECUTE IMMEDIATE sql_hard INTO vv;    IF vv > 0 THEN     dbms_output.put_line('[字段值所在的表.字段]:['||rec1.table_name||'].['||rec1.column_name||']');    END IF;     END IF;    END LOOP;  END;   

​
​
​
​
​