oracle常见的sql命令

登陆数据库
[oracle@sq ~]$ sqlplus / as sysdba
[oracle@sq ~]$ sqlplus sys/123456 as sysdba

查看当前登陆用户
SQL> show user

select username from dba_users;
切换用户(第一次登陆 提示用户锁定)
SQL> conn scott/tiger
SQL> conn sys/123456 as sysdba

为用户解锁
SQL> alter user scott account unlock;

再次登陆
SQL> conn scott/tiger
需要新密码

修改密码
方法1.
SQL> alter user scott identified by abc;

方法2.
SQL> password scott
--------------------------------

sql语句
SQL> create table tt as select * from dba_object_tables; 数据字典(系统表)dba_  user_   all_
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME from tt;

查看服务器状态
SQL> select open_mode from v$database; 动态性能试图  

--------------------------------------------
X$ oracle数据库核心部分,加载数据库时即被使用,加密命名


查看数据字典
SQL> select * from dict where rownum < 2;
(列为table_name 其实现实的是数据字典对象名)


dict_columns 记录着字典视图列的说明
SQL> select column_name,comments from dict_columns
  2  where table_name='DICT';


SQL> select * from 
    (select table_name,count(*) from dict_columns
    group by table_name order by 2 desc)
    where rownum < 6;
-------------------------------------------
GV$  和 V$ 动态性能视图
GV$ 为全局v$ 主要为RAC实用
v$都会对应一个GV$


--------------------------------------------
查看后台进程
SQL> !
[oracle@sq ~]$ ps -ef |grep ora_
[oracle@sq ~]$ exit


查看当前用户数据库下面有哪些表
SQL> conn scott/123456
SQL> select table_name from user_tables;

查看实例名
select instance_name from v$instance;


显示表定义
SQL> desc emp;


创建一个相同的表
SQL> create table emp_bak as select * from emp;


查看表
SQL> select * from emp;    soctt用户可以看到


设置行长
SQL> set lines 1000;


设置页面记录条数
SQL>  set pagesize 20 ;


关闭数据库
shutdown normal 需要等待所有事务/进程全部结束 才能关数据库
shutdown transactional   需要等待,但空闲事务进程自动关闭 
shutdown immediate  关闭之前同步数据（生产关闭数据库常用）




shutdown abort  强制关闭数据库相当于断电（此动作非常危险轻易勿用）
启动数据库


startup 直接打此命令默认选项为open直接打开数据库
startup nomount 只启动实例(装载实例和打开参数文件)


startup mount  挂载数据库(装载实例和打开控制文件，激活某些功能，用户不能存取数据库可以进行实例或数据的恢复处理)
alter database mount 改变数据库从nomount状态到mount状态
alter database open 打开数据库（此时才可以正常对数据库进行读写）
alter database open read only 将数据库打开到只读状态


startup force  重启数据库 




数据的提交和回退


commit 提交数据（将数据写到日志里并同步到数据文件）（oracle默认为不提交）
rollback 回退就是取消所有操作，次命令只对还未commit的操作有效


注销
SQL> disc (用来断开与当前数据库的连接)
SQL> show user
USER is ""

脚本执行
[oracle@sq ~]$ vi a.sql
create table tt(id int,name varchar2(10));

SQL> @/home/oracle/a.sql

修改上次输入错误的命令
SQL> create talbe a
  2  (id int);        (第一行talbe错误)


SQL> l (查看上次命令)
SQL> 1(修改第一行)


SQL> c/talbe/table
SQL> l
  1  create table a
  2* (id int)
SQL> /
===========================================


参数文件pfile与spfile
参数文件的内容：控制文件的路径 要分配给sga的内存值
Pfile 文本参数文件（也叫初始化参数文件）
     Vi可以直接修改
     Sqlplus中不能通过命令直接修改此参数文件


Spfile 服务器参数文件（二进制）
      Vi不可以直接修改
      Sqlplus中可以直接修改此参数
      Oracle默认使用


Startup对参数文件的作用
   Oracle启动按以下顺序从$ORACLE_HOME/dbs读取初始化文件
     首先读取spfile.ora
     如果找不到则读取pfile.ora
     如果仍然找不到则读取initSID.ora


spfile 保存位置
/u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora


查看是否使用了spfile
SQL> show parameter spfile


通过pfile 启动数据库
SQL> startup pfile=(pfile所在的路径)


从spfile参数文件中创建pfile
SQL> create pfile='/u01/app/oracle/init01.ora' from spfile;
或SQL> create pfile from spfile;


从pfile参数文件中创建spfile 
SQL> shutdown immediate
SQL> create spfile from pfile='/u01/app/oracle/init01.ora';
或  create spfile from pfile


验证pfile
SQL> shutdown immediate
SQL> startup pfile='/u01/app/oracle/init01.ora';
SQL> show parameter spfile;(为空)
SQL> show parameter processes
processes                            integer     150 (值为150)
SQL> alter system set processes=100;
(不让修改 pfile不能通过命令修改)
(可vipfile文件 可以修改*.processes=100)




验证spfile
SQL> shutdown immediate
SQL> startup
SQL> show parameter spfile;
SQL> show parameter processes
SQL> alter system set processes=100 scope=spfile;


验证启动顺序
1.删除spfile
[root@sq /]# rm -rf /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora
2.目录下有pfile的一样可以启动
3.
SQL> shutdown immediate
SQL> startup






Oracle的日志文件存放位置：位于参数文件background_dump_dest指定的路径下
SQL> show parameter background_dump_dest
cd  /u01/app/oracle/admin/orcl/bdump/
[root@sq bdump]# ls
alert_orcl.log  orcl_lgwr_10294.trc  orcl_lgwr_10357.trc  orcl_lgwr_13449.trc




查看实例(主要看SID)
SQL> select * from v$instance;


控制文件存放位置
SQL> select name from v$controlfile;
======================================================
手工删除库
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;(静默状态，DBA能安全地执行某些操作，这些操作要求从当前非DBA用户中隔离出来操作。)


SQL> drop database;






手工建库
1。
[oracle@sq ~]$ vi .bash_profile
ORACLE_SID=vfast;
退出oracle用户 重新登陆


2。建立初始化文件
[oracle@sq ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
control_files = (/u01/app/oracle/oradata/control1.ctl,/u01/app/oracle/oradata/control2.ctl,/u01/app/oracle/oradata/control3.ctl)
undo_management = AUTO
undo_tablespace = UNDOTBS1
db_name = ORCLDB
db_unique_name = WENDING
db_block_size = 8192
sga_max_size = 320M
sga_target = 320M




3。建立密码文件
[oracle@sq ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=123456 force=y


4。建立所需目录
[oracle@sq ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
[oracle@sq ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/bdump
[oracle@sq ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/pfile
[oracle@sq ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/cdump
[oracle@sq ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/dpdump
[oracle@sq ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/udump




5。建立spfile
SQL> startup nomount
SQL> create spfile from pfile;


6。建立数据库
[oracle@sq ~]$ vi /home/oracle/createDB.sql
内容为：
create database vfast
logfile group 1 ('$ORACLE_BASE/oradata/vfast/redo01.log') size 100m,
        group 2 ('$ORACLE_BASE/oradata/vfast/redo02.log') size 100m,
        group 3 ('$ORACLE_BASE/oradata/vfast/redo03.log') size 100m
maxlogfiles 10
maxlogmembers 5
maxloghistory 5
maxdatafiles 100
maxinstances 1
character set zhs16gbk
national character set al16utf16
datafile '$ORACLE_BASE/oradata/vfast/system01.dbf' size 350m reuse autoextend on next 1m maxsize unlimited extent management local
sysaux datafile '$ORACLE_BASE/oradata/sysaux01.dbf' size 350m reuse autoextend on next 1m maxsize unlimited
default temporary tablespace temp01 tempfile '$ORACLE_BASE/oradata/vfast/temp01.dbf' size 50m reuse autoextend on next 1m maxsize 500m
undo tablespace undotbs1 datafile '$ORACLE_BASE/oradata/vfast/undotbs01.dbf' size 500m reuse autoextend on next 1m maxsize unlimited;




SQL> @/home/oracle/createDB.sql


7.
SQL> select name from v$database;


8.
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql（建数据字典视图)


SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql（建存储过程包）
===========================================
SCN的概念 
SCN是顺序递增的一个数字，在Oracle中用来标识数据库的每一次改动，及其先后顺序。SCN的最大值是0xffff.ffffffff。


查看当前scn号
SQL> select current_scn from v$database;


1、系统检查点scn
当一个检查点动作完成之后，Oracle就把系统检查点的SCN存储到控制文件中。
select checkpoint_change# from v$database
2、数据文件检查点scn
当一个检查点动作完成后，Oracle就把每个数据文件的scn单独存放在控制文件中。
select name,checkpoint_change# from v$datafile


3、启动scn
Oracle把这个检查点的scn存储在每个数据文件的文件头中，这个值称为启动scn，
因为它用于在数据库实例启动时，检查是否需要执行数据库恢复。
select name,checkpoint_change# from v$datafile_header


4、终止scn
每个数据文件的终止scn都存储在控制文件中。
select name,last_change# from v$datafile
在正常的数据库操作过程中，所有正处于联机读写模式下的数据文件的终止scn都为null.


5、在数据库运行期间的scn值
在数据库打开并运行之后，控制文件中的系统检查点、控制文件中的数据文件检查点scn和每个数据文件头中的启动scn都是相同的。控制文件中的每个数据文件的终止scn都为null.




1)系统当前SCN并不是在任何的数据库操作发生时都会改变,SCN是在事务提交或回滚时改变,
2)在控制文件,数据文件头,数据块,日志文件头,日志文件change vector中都有SCN,但其作用各不相同
create table t1 (id int);
shutdown immediate
startup
后重新启动再次查看SCN,会变化
================================================


SQL> select count(*) from dba_object_tables; 统计行数
解决中文乱码问题   WE8ISO8859P1


SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; (查看当前字符集)
SQL> shutdown immediate 


SQL> startup mount 


SQL> alter system enable restricted session; (限制会话)


SQL> show parameter job_queue_processes; 


SQL> alter system set job_queue_processes=0;(工作进程为0)


SQL> alter database open;


SQL> alter database character set ZHS16GBK; 
(报错 新的字符集必须是老的字符集 的升级版本)


SQL> alter database character set internal_use ZHS16GBK;
(使用Oracle内部命令internal_use，跳过超集检查)


SQL> select * from nls_database_parameters where parameter='NLS_CHARACTERSET'; (查看当前字符集)


SQL> shutdown immediate
SQL> startup mount
SQL> alter system set job_queue_processes=10;(改会原来值)


SQL>  alter database open;（OK）




 nls_lang是在客户端设置设置客户端字符集，也就是在环境变量(Linux:~/.bash_profile)中。
 
nls_language在服务端设置，SERVER端的lang，属于parameter，可以由alter system set nls_language='...' scope=spfile 来修改。
 
nls_characterset也是设置服务器的字符集。
==========================================================
网络连接数据库
监听配置
创建好数据库后需要建立监听与网络服务,才能使客户端或APP应用于oracle进行网络连接
1.图形建立 NETMGR工具




2.手工动态注册
[oracle@sq ~]$ vi /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
添加下面内容:
LISTENER =
 (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP) (HOST = oracle.sq.com) (PORT = 1521))
 )




[root@sq /]# vi /etc/hosts
127.0.0.1       localhost.localdomain   localhost
192.168.8.2             sq
192.168.8.2             oracle.sq.com


测试
[oracle@sq ~]$ lsnrctl start
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sq)(PORT=1521)))
The listener supports no services
The command completed successfully
(可以看到成功启动了监听程序,但目前没有支持其他服务)


[oracle@sq ~]$ sqlplus /nolog
SQL> conn / as sysdba
SQL> startup force


[oracle@sq ~]$ lsnrctl status
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
(可以看到 连接服务已经被动态注册上了)






3.手工静态注册
SQL> shutdown immediate
[oracle@sq ~]$ lsnrctl stop
[oracle@sq ~]$ vi /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora


添加下面内容:
SID_LIST_LISTENER =
  (SID_LIST =
     (SID_DESC =
        (GLOBAL_NAME = orcl)
        (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
        (SID_NAME = orcl)
      )
   )
[oracle@sq ~]$ lsnrctl start(成功)




4.自动动态注册
SQL> alter system register;
通过pmon进程 自动识别




测试监听
telnet 192.168.8.2 1521




--------------------
****如果netmgr 报java错误
切换到root用户 #xhost + (执行)
------------------------------


[oracle@sq86 admin]$ vi sqlnet.ora (配置参数文件)


TCP.INVITED_NODES= (192.168.8.6)  (允许8.6连接)


TCP.EXCLUDED_NODES= (192.168.8.5)  (拒绝8.5连接)
==============================================================
客户端连接
服务器端
cd /u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@sq admin]$ vi tnsnames.ora 
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )








客户端
安装 client软件  developer软件
配置和移植工具 -->net manager
添加
监听程序  和 服务命名


==============================================================
网络连接数据库
OEM 配置 
1.创建资料库
[oracle@oracle1 emdrep]$ emctl stop dbconsole
[oracle@sq ~]$ emca -repos recreate


STARTED EMCA at Apr 5, 2011 2:27:22 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.


Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user:  
Password for SYSMAN user:    


Do you wish to continue? [yes(Y)/no(N)]: y








SQL> alter user DBSNMP identified by abc;


[oracle@sq admin]$ emca -config dbcontrol db
Database SID: orcl
Listener port number: 1521
Password for SYS user:  
Password for DBSNMP user: 
Do you wish to continue? [yes(Y)/no(N)]: y




企业管理器控制台的状态
[oracle@sq admin]$ emctl status dbconsole


/u01/app/oracle/product/10.2.0/db_1/network/admin/samples




******************
注释:
如果重建em时间过长
分开执行,先删除
[oracle@oracle1 emdrep]$ emca -deconfig dbcontrol db -repos drop


再次创建em
[oracle@oracle1 emdrep]$ emca -repos create
===================================================================
1
mkdir test
cd test
touch {1..100000}
touch {100001..200000}
touch {200001..300000}
touch {300001..400000}
touch {400001..500000}
touch {500001..600000}
创建出 60w个文件


2.rm -rf * (出错 参数列表中不支持这么大)


3.ls|xargs rm -rf (ls查看的每一条记录  单独放到rm中执行)