oracle学习及常用命令

--------------------------------------------------oracle命令------------------------------------------
查看数据库锁表的sql命令如下：
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
删除锁表的命令
alter system kill session 'sid,serial#'
创建数据库成功
╔-----------------------------------------------------------------------------------------------------------------------------------------------╗
☆oracle创建库ACR完成后1、首先要对这个库创建一个ACR的用户2、给ACR这个用户赋上dba权限 ☆
╚-----------------------------------------------------------------------------------------------------------------------------------------------╝
CREATE USER ACR IDENTIFIED BY ACR ---identified by 被识别的、被鉴定的、被认同的

GRANT CONNECT,RESOURCE TO ACR ---给用户授予连接和建表的权限

GRANT dba to ACR ---grant 授予

现在使用的是默认的表空间，可以自己创建表空间进行方便管理
现在如果导入数据时出现错误可能是dba的sys密码和创建用户的密码一直造成的
可以用命令:
alter user ACR IDENTIFIED BY ACR123 来修改密码；

----创建库完成后
1、首先以sys用户密码是创建库时的密码123456登录
2、先给库创建表空间：
create tablespace DATATEST datafile 'D:\oracle\product\10.2.0\oradata\second\DATATEST.dbf' size 1G ;
（删除表空间的命令：drop tablespace TBS_CUSTRISK including contents and datafiles;
linux创建表空间的命令：
CREATE TABLESPACE SLSADMIN
LOGGING
DATAFILE '/newdisk/SLSADMIN.dbf' SIZE 5G AUTOEXTEND OFF,
EXTENT MANAGEMENT LOCAL
ONLINE
SEGMENT SPACE MANAGEMENT AUTO;
）
3、创建用户:create user jiaoqf identified by second;
4、修改用户的默认表空间（就是把表空间给创建的用户）alter user jiaoqf default tablespace DATATEST;
5、给用户授予连接和建表的权限：grant connect,resource to jiaoqf;
6、给用户授予dba权限：grant dba to jiaoqf；
7、以用户登录报用户名或密码错误，此时修改用户的密码（不要和sys的密码相同）：alter user jiaoqf identified by ora123;
8、以jiaoqf登录密码：ora123成功，此时就可以导入备份的dmp文件了

╔----------------------------------------------╗
☆oracle导出和导入单表的命令 ☆
╚----------------------------------------------╝
C:\Users\jiaoqf>exp ACR/ACR@ACR TABLES=(BIFM02) FILE = F:\BIFM02.DMP

C:\Users\jiaoqf>imp ACR/ACR@192.168.1.109:1521/orcl TABLES=(code_org_organ) FILE =/home/mq/code_org_organ.DMP


imp ACR/ACR FILE=F:\BIFM02_02.DMP FULL= Y

╔--------------------------------------------------------------------╗
☆ 必须导出空表时 整个库导出导入命令 ☆
╚--------------------------------------------------------------------╝
先执行select 'alter table '||table_name||' allocate extent;' from user_tables
把查询出的结果执行，在导出dmp文件时就会把空表导出来了
exp ACR/ACR@ACR_10 file=d:\ACR.dmp log=d:\user.log

imp user/passwd file=d:\user.dmp full=y
╔----------------------------------------------╗
☆ 远程整个库导出导入命令 ☆
╚----------------------------------------------╝
exp ACR/ACR@192.200.3.247/ACR_10 file=/home/oracle/ACR.dmp log=d:\user.log owner=orcl

imp user/passwd@192.200.3.247/ACR_10 file=d:\user.dmp full=y
╔-------------------------------------------------------------------╗
☆ 导入备份文件的一张表的命令如下 ☆
╚-------------------------------------------------------------------╝
imp ACR/ACR@ACR file=F:\ALL.dmp tables=BIFM02 ignore=y

╔-------------------------------------------------------------------╗
☆ 导出查询出来数据的命令 （在d盘生成a.txt）☆
╚-------------------------------------------------------------------╝
SQL> spool d://a.txt
Started spooling to d:\a.txt

SQL> SELECT * FROM UMG_USER where substr(organ_id,0,3) in ('210');

SQL> spool off;

╔-------------------------------------------------------------------╗
☆启动和停止linux系统上的 oracle的命令 ☆
╚-------------------------------------------------------------------╝
linux 上启动oracle的命令和启动监听的命令
su - oracle
sqlplus /nolog
connect /as sysdba
shutdown immediate
exit
lsnrctl stop
lsnrctl start
su - oracle
sqlplus /nolog
connect /as sysdba
startup
exit
windows上启动orcle的命令
首先cmd进入命令提示符中
set ORACLE_SID=orcl
sqlplus / as sysdba
shutdown immediate
startup
╔-------------------------------------------------------------------╗
☆linux系统导出表的命令如下 ☆
╚-------------------------------------------------------------------╝
1首先切换到oracle 用户 su - oracle
2输入命令：exp ACR/ACR@ACR tables=ALL_PERIDEN_INFO file=/OracleData/ALL_PERIDEN_INFO0722.dmp log=ALL_PERIDEN_INFO0722.log

╔---------------------------------╗
☆表内容的复制 ☆
╚---------------------------------╝
create table emp_bak as select * from emp;
╔-----------------------------------------------------------╗
☆查询的数据直接放到已经存在的表中 ☆
╚-----------------------------------------------------------╝
insert into emp_bak select * from emp where job='manager';
╔---------------------------------------------------------------------------------------------╗
☆oracle查询一张表的前五行内容的时候和DB2查询是有区别的 ☆
╚---------------------------------------------------------------------------------------------╝
oracle:select * from emp where rownum<=5;
db2: select * from emp fetch first 5 rows only;
╔-------------------------------------╗
☆启动oracle服务的命令 ☆
╚-------------------------------------╝
net start oracleservicesystem;
net start oracleservicerspdb;
╔-------------------------------------------╗
☆查看数据库的用户名和密码 ☆
╚-------------------------------------------╝
SQL> select username,account_status,password from dba_users where username='SLSINT';
╔---------------------------------------------╗
☆数据库的用户被锁解锁的语句☆
╚---------------------------------------------╝
SQL> alter user scott account unlock;
用户已更改。
SQL> password scott;
更改 scott 的口令
新口令:
重新键入新口令:
口令已更改
SQL> commit;
╔----------------------------------------------------------------------------------------------------------------╗
☆对于数据库中的表数据比较大，这时候不能用delete from 而是用truncate table ☆
╚----------------------------------------------------------------------------------------------------------------╝
如：truncate table menu; COMMIT;
╔-----------------------------╗
☆赋权限的方法是 ☆
╚-----------------------------╝
如：grant select on tmp to scott;
╔-------------------------------------------------------------------╗
☆赋所有权限的方法（select ,update,create）是 ☆
╚-------------------------------------------------------------------╝
如：grant all on tmp to scott;
╔----------------------------------------------------------------------------------------------------------------------------------------╗
☆sys赋权限给scott，但scott也可以把这个权限赋权给其它用户的方法（select ,update,create）是 ☆
╚----------------------------------------------------------------------------------------------------------------------------------------╝
如：connect sys/ACR@ACR as sysdba;
grant select on tmp to scott with admin option;
如果：sys把权限给scott，而scott把权限又给了acr，但如果sys把scott用户的权限给收了，那么acr还
有没有权限（通吃连坐）acr也没有了权限
╔-----------------------------╗
☆收回权限的方法是 ☆
╚-----------------------------╝
如：revoke select on tmp from scott;

====================================================================================
╔---------------------------------------------╗
☆oracle查看被锁的表和解锁 ☆
╚---------------------------------------------╝
查看被锁的表
select p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id ;
查看那个用户那个进程照成死锁，锁www.2cto.com的级别
select b.owner,b.object_name,l.session_id,l.locked_mode from v$locked_object l, dba_objects b where b.object_id=l.object_id;
查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
杀掉进程
alter system kill session 'sid,serial#';
===================================================================================
╔--------------------------------------------------------------------------------------------------------------------------------╗
☆oracle 的监听配置和远程是存在（本地是SERVICE_NAME=而远程是SID）对比如下 ☆
╚--------------------------------------------------------------------------------------------------------------------------------╝
| 本地------------------------------------------------------------------------------------------------|
|SYSTEM = |
| (DESCRIPTION = |
| (ADDRESS = (PROTOCOL = TCP)(HOST = adtec_1)(PORT = 1521)) |
| (CONNECT_DATA = |
| (SERVER = DEDICATED) |
| (SERVICE_NAME = SYSTEM) |
| ) |
| ) | | 远程----------------------------------------------------------------|
|krm_114 = |
|(DESCRIPTION = |
| (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.114)(PORT = 1521)) |
| (CONNECT_DATA = |
| (SERVER = DEDICATED) |
| (SID = ora11g) |
| ) |
|) |
|------------------ --------------------------------------------------|
---------------------------------------------------------------------------------------------
东亚银行的mds.dmp导入时先创建表空间
create tablespace SLSADMIN datafile 'D:\oracle\product\10.2.0\oradata\ACR\SLSADMIN.dbf' size 1G ;
-----------------------数据库导入的语句总结
CREATE TABLESPACE PISA
DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\PISA.dbf'
SIZE 5G
AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED;
CREATE TABLESPACE PISA_ETL
DATAFILE 'D:\oracle\product\10.2.0\oradata\orcl\PISA_ETL.dbf'
SIZE 2G
AUTOEXTEND ON NEXT 10M
MAXSIZE UNLIMITED;
commit;
create user pisa
identified by "pisa"
default tablespace PISA
temporary tablespace temp
profile default;
grant dba,connect,resource to pisa;
commit;
imp slstjm/slstjm file=f:/pisa.dmp fromuser=pisa touser=slstjm
-------------------------oracle数据库
当前的连接数
Select count(*) from v$process;
数据库的最大连接数
Select value from v$parameter where name='processes'
Select value from v$parameter where name='session'
用sysdba直接查看的命令：show parameter processes;
show parameter sessions;
修改的这些参数的命令
alter system set sessions=1024 scope=spfile;
alter system set processes=926 scope=spfile;
数据库的优化是sessiones和processes之间有一定的换算定律
sessones = (processes+5)*1.1
数据库获得最好的的调优性能

------------------------------------------------------------------------------------------------
|linux下启动视图化界面删除监听的命令：netca |
|LINUX下启动视图化界面创建数据库的命令：dbca |
|LINUX下启动视图化界面进入控制中心：netmgr |
------------------------------------------------------------------------------------------------
不小心删掉oracle数据库datafiles的dbf文件的解决办法