Oracle相关命令

oracle创建表空间和数据库及用户权限问题

在plsql工具中执行以下语句，可建立Oracle表空间。创建用户的规范步骤 其中 file的路径必须存在,且不能是中文和空格

分为四步

1. 创建临时表空间

• create temporary tablespace datatemp

• tempfile 'E:\oracle\product\10.2.0\datatemp.dbf'

• size 50m

• autoextend on

• next 50m maxsize 20480m

• extent management local;

• create tablespace occdata

• datafile 'E:\oracle\product\10.2.0\occdata.dbf'

• size 50m

• autoextend on

• next 50m maxsize 20480m

• extent management local;

• drop user occ cascade;

• create user occ identified by "123"

• default tablespace occ_data

• temporary tablespace occdatatemp;

• --grant dba to occ;

• grant connect,resource,create view,impfulldatabase to occ;

• grant connect to occ;

• grant resource to occ;

• grant create view to occ;

• -- Grant/Revoke system privileges

• grant unlimited tablespace to occ;

一些删除操作

• 删除用户 drop user hqjt cascade;

• 删除表空间。DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

• 删除空的表空间，不包含物理文件。DROP TABLESPACE tablespace_name;

• 删除空表空间，包含物理文件。DROP TABLESPACE tablespace_name INCLUDING DATAFILES;

• 删除非空表空间，不包含物理文件。DROP TABLESPACE tablespace_name INCLUDING DATAFILES;

• 删除非空表空间，包含物理文件。DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

• 查看所有用户：select * from all_users;

• 查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：select * from dbasysprivs; select * from usersysprivs;

• 查看角色(只能查看登陆用户拥有的角色)所包含的权限 select * from rolesysprivs;

• 查看用户对象权限：select * from dbatabprivs; select * from alltabprivs; select * from usertabprivs;

• 查看所有角色：select * from dba_roles;

• 查看用户或角色所拥有的角色：select * from dbaroleprivs; select * from userroleprivs;

• 查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限) select * from V$PWFILE_USERS;

• 查询表名、表空间 SELECT TABLENAME,TABLESPACENAME FROM USERTABLES WHERE TABLESPACENAME = 'TABLE_DATA'

• 修改表空间SQL，查询并执行 SELECT 'alter table '||TABLENAME||' move tablespace TABLEDATA;' FROM USERTABLES WHERE TABLESPACENAME = 'CCZL_DATA'

• 查询oracle目录 select * from dba_directories;

• 修改directory_path create or replace directory dumpdir as '/data/oraclebak/dump'

oracle数据库导入导出dmp文件

1. 全表导出

• expdp user/password@localhost:1521/orcl directory=dumpdir dumpfile=20201231_all.dmp

• expdp user/password@localhost:1521/orcl directory=dumpdir dumpfile=20201231_all.dmp tables=()

• skip 如果已存在表，则跳过并处理下一个对象；

• append 为表增加数据;

• truncate 截断表，然后为其增加新数据；

• replace 删除已存在表，重新建表并追加数据;

• impdp user/password@localhost:1521/orcl directory=dumpdir dumpfile=20201231all.dmp tableexists_action=replace

• tableexistsaction参数说明 *

• 导入时，若报Permission denied，权限不足，需要给对应的dmp授予权限 chown -R oracle

数据库连接和事务问题处理

• 当前连接数

• select count() from v$process;

• select count() from v$session;

• 查看连接的进程

• select sid, serial#, username, osuser from v$session;

• 查看oracle的并发连接数

• select count(*) from v$session where status='ACTIVE';

• 查看不同用户的连接数

• select username,count(username) from v$session where username is not null group by username;

• 数据库允许的最大连接数

• select value from v$parameter where name = 'processes'

• 查看信息

• show parameter processes;

• 修改连接数，需要重启

• alter system set processes = 500 scope = spfile;

• 查询所有用户

• select * from all_users;

• 查询不同用户的链接数

• select username,count(username) from v$session where username is not null group by username;

• 迅速杀掉进程

• select 'alter system disconnect session ''' || b.sid || ',' || b.serial# || '''immediate;' from v$lockedobject a, v$session b, dbaobjects c where b.sid = a.session_id;

• select 'alter system disconnect session ''' || a.sid || ',' || a.serial# || '''immediate;' from v$session a where machine = 'LOCALHOST.LOCALDOMAIN' and program = 'JDBC THIN CLIENT' and username = 'username' and status = 'INACTIVE';

• 查看数据库连接的消耗情况

• 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

• 查看指定主机的连接

• select sid,serial#,username,program,machine,status from v$session WHERE machine = 'localhost.localdomain' AND program = 'JDBC Thin Client' AND username = 'username';

• 查看被锁的表

• select p.spid,a.serial#, c.objectname,b.sessionid,b.oracleusername,b.osusername from v$process p,v$session a, v$lockedobject b,allobjects c where p.addr=a.paddr and a.process=b.process and c.objectid=b.object_id

• select 'alter system kill session ''' ||SID ||',' ||serial#||''';' from v$session t1, v$lockedobject t2 where t1.sid = t2.SESSIONID

• 解决锁表问题

• select * from v$session t1, v$lockedobject t2 where t1.sid = t2.SESSIONID

• alter system kill session 'sid,serial#';

linux下关闭数据库服务和重启

1. 关闭数据库

• su - oracle -- 切换至oracle模式

• sqlplus /nolog

• connect /as sysdba 以sysdba身份连接数据库

• select status from v$instance; -- 查看数据库状态 OPEN - 开启的

• shutdown immediate; -- 关闭数据库

• start up; -- 启动数据库

• exit -- 退出

• [oracle@linux4 ~]$ cd $ORACLEHOME

• [oracle@linux4 db1]$ cd bin

• [oracle@linux4 bin]$ ./sqlplus "/ as sysdba"

• SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 27 14:06:13 2011

• Copyright (c) 1982, 2005, Oracle. All rights reserved.

• Connected to an idle instance.

• SQL> startup

• ORACLE instance started.

• Total System Global Area 285212672 bytes

• Fixed Size 1218992 bytes

• Variable Size 100664912 bytes

• Database Buffers 180355072 bytes

• Redo Buffers 2973696 bytes

• Database mounted.

• Database opened.

• SQL>

linux下设置oracle数据库定时备份

1. 新建脚本文件 dbbackup.sh

• #!/bin/bash

• source /home/oracle/.bashprofile

• cd /data/dump

• datestr= date"+%Y%m%d"
  

• chmod 777 dbbackup.sh

• ./dbbackup.sh

• crontab -l

• service crond restart

• 0～59 表示分

• 1～23 表示小时

• 1～31 表示日

• 1～12 表示月份

• 0～6 表示星期（其中0表示星期日）

• i:插入操作

• shifi+: : 输入命令

• :wq 退出编辑页面

• 00 17 * * * /home/oracle/dbback/shell/dbback.sh

• crontab -e

• 出现一个类似vi编辑的页面，输入设置的定时任务

• crontab 前面5个*参数的意义：

• 设置保存之后，输入命令，让其生效

• 查看定时任务