Oracle数据库相关命令及sql语句

IBM DB2；Microsoft SQL Server；MySQL；Oracle；Postgresql；Sybase Adaptive Server；

order by : select tablename from table order by tablename;通常是select语句中最后一条子句

 

 sqlplus dbuser/dbpassword@192.168.0.2/mydb

按多个列排序：

1按表名排列select t_n1, t_n2, t_n3 from table order by t_n1, t_n2;

              2按某列排列select t_n1, t_n2, t_n3 from table order by 2, 3;(按第2,3列排序)

              3按指定排序方向，升序或者降序，默认升序A到Z，select t_n1, t_n2, t_n3 from table order by t_n1 DESC；（DESC表示降序）

              4混合排列select t_n1, t_n2, t_n3 from table order by t_n1 DESC, t_n2;(先按n1降序，在按n2排序)

DESC关键字只应用到直接位于其前面的列名

%通配符可以表示多个字符，_表示单个字符，[]指定一个字符集，必须匹配指定位置（通配符的位置）的一个字符，[**]%表示匹配任何以方括号中字母开头的表名，此通配符可以用前缀字符^（脱字号）来否定[^**]%:

select t_n1, t_n2 from table where t_n1 like ‘%***%’ or like ‘*%*’ or ‘_**_’ or ‘[**]%’;

拼接||，将值连接到一起构成单个值，去掉空格的函数rtrim:

select rtrim(t_n1) || ’(’ || rtrim(t_n2) || ‘)’ from table order by t_n1;(rtrim去掉右边的空格 ltrim去掉左边的空格 trim去掉两边的空格)

as关键字表示一个字段或值的替换名：

select rtrim(t_n1) || ’(’ || rtrim(t_n2) || ‘)’  as t_n3 from table order by t_n1;

upper函数，将文本小写转换为大写,lower函数，将大写转换为小写：

select t_n1, upper(t_n1) as t_n2 from table order by t_n1;

soundex函数，匹配所有发音类似于***的元素：soundex(t_n1) = soundex(‘t_n1的元素’)；

datepart()函数返回日期的某一部分，oracle中没有

group by

where过滤行，having过滤分组，having支持所有where操作符：

select t_n1, count(*) as t_n2 from table group by t_n1 having count(*) >= 2;过滤count(*)>=2的那些分组。

 

将.dmp文件中的某个数据表导入导出数据库：

数据的导入
将D:\daochu.dmp 中的数据导入 TEST数据库中。
  imp system/manager@TEST  file=d:\daochu.dmp
  imp aichannel/aichannel@HUST full=y  file=file= d:\data\newsmgnt.dmp ignore=y
  上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
  在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
  imp system/manager@TEST  file=d:\daochu.dmp  tables=(table1)
基本上上面的导入导出够用了。不少情况要先是将表彻底删除，然后导入。
注意：
操作者要有足够的权限，权限不够它会提示。
数据库时可以连上的。可以用tnsping TEST 来获得数据库TEST能否连上；

imp smecc/smecc@smic file=/smecc/ficecc_20160904/ficecc_16090400.dmp tables=(fic_person_card) ignore=y

数据导出：
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
  exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
  exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
   exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp
tables=(inner_notify,notify_staff_relat)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
  exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\"
where filed1 like '00%'\"
上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。也可以在上面命令后面 加上 compress=y 来实现

 

关于.dmp版本的问题：

11g R2：V11.02.00

11g R1：V11.01.00

10g：V10.02.01

填的就是以上对应的版本号，而不是通过v$version查出的10.2.0.1.0此类。

查看数据库字符集：

select userenv('language') from dual;

修改数据库字符集：

shutdown immediate;

startup mount;

SQL> alter system enable restricted session;

 

System altered.

 

SQL> alter system set job_queue_processes=0;

 

System altered.

 

SQL> alter system set aq_tm_processes=0;

 

System altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> alter database character set internal_use AL32UTF8;

 

Database altered.

SQL> alter system disable restricted session;

 

要查看.dmp文件的字符集，可以先将文件导入数据库，再通过select nls_charset_name(to_number('0354','xxxx')) from dual;来查看

数据库两种字符集ZHS16GBK和AL32UTF8

测试导出exp及imp选项filesize

 

conn scott/system@orcl

 

create table t_exp_filesize(a int);

 

insert into t_exp_filesize select level from dual connect by level<=1e7;

commit;

 

--buffer和filesize的单位是kb,还是mb或是byte，或是BYTE,经查文档单位是：字节

exp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=102400 file=c:\exp_t_exp_filesize.dmp log=c:\exp_t_exp_filesize.log direct=y recordlength=65535

 

--小结：说明filesize不能太小

EXP-00075: 对 FILESIZE 进行舍入处理, 新值为 65535

EXP-00073: 转储文件太小

EXP-00000: 导出终止失败

 

exp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=65535 file=c:\exp_t_exp_filesize.dmp log=c:\exp_t_exp_filesize.log direct=y recordlength=65535

EXP-00073: 转储文件太小

EXP-00000: 导出终止失败

 

--filesize为20m左右

exp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971520 file=c:\exp_t_exp_filesize.dmp log=c:\exp_t_exp_filesize.log direct=y recordlength=65535

 

--未分析前此表的大小是27mb

select ug.BYTES/1024/1024 mb  from user_segments ug where ug.segment_name='T_EXP_FILESIZE';

 

--导出此表的dmp文件是370byte,说明oracle在exp时会对表的数据进行压缩处理，节省好多空间

 

 

exp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971520 file=c:\exp_t_exp_filesize_001.dmp,c:\exp_t_exp_filesize_002.dmp,c:\exp_t_exp_filesize_003.dmp log=c:\exp_t_exp_filesize.log direct=y recordlength=65535

 

Export: Release 10.2.0.3.0 - Production on 星期六 11月 10 13:05:13 2012

 

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

 

 

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

EXP-00075: 对 FILESIZE 进行舍入处理, 新值为 20971200

已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

 

即将导出指定的表通过直接路径...

. . 正在导出表                  T_EXP_FILESIZE

继续导出到文件 c:\exp_t_exp_filesize_002.dmp

 

 导出文件: EXPDAT.DMP > c:\new_other.dmp --小结：如果在命令行指定的转储文件不够用，会弹出此行的提示信息，让你指定产生新的dmp文件的存储路径

 

继续导出到文件 c:\new_other.dmp

导出了     7200000 行

导出成功终止, 但出现警告。

 

--继续测试imp加上filesize选项导入

 

--1,imp导入全部的dmp文件

imp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971200 file=c:\exp_t_exp_filesize_001.dmp,c:\exp_t_exp_filesize_002.dmp,c:\exp_t_exp_filesize_003.dmp log=c:\imp_t_exp_filesize.log recordlength=65535

 

经由直接路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

IMP-00040: FILESIZE 的值与用于导出的值 20971200 不匹配

IMP-00000: 未成功终止导入

 

--小结：说明exp和imp的filesize必须要一致

        全部导入全部的dmp可以成功

        

        

--2,仅导入自首个dmp文件开始的多个dmp文件

imp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971200 file=c:\exp_t_exp_filesize_001.dmp,c:\exp_t_exp_filesize_002.dmp log=c:\imp_t_exp_filesize.log recordlength=65535

 

经由直接路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SCOTT 的对象导入到 SCOTT

. 正在将 SCOTT 的对象导入到 SCOTT

. . 正在导入表                "T_EXP_FILESIZE"

请在导出文件集中输入下一文件名。

导入文件: EXPDAT.DMP> sss

 

IMP-00002: 无法打开 sss.DMP 进行读取

导入文件: EXPDAT.DMP> x.dmp

 

IMP-00002: 无法打开 x.dmp 进行读取

导入文件: EXPDAT.DMP>

导入了     7200000 行

成功终止导入, 但出现警告。

 

--小结；说明多个部分dmp文件可以导入imp成功

        导入最后会提示未导入的文件，你可以手工输入，也可以回车不输入

        

--3,导入首个和末尾的不连续多个dmp文件

imp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971200 file=c:\exp_t_exp_filesize_001.dmp,c:\exp_t_exp_filesize_003.dmp log=c:\imp_t_exp_filesize.log recordlength=65535

 

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

经由直接路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SCOTT 的对象导入到 SCOTT

. 正在将 SCOTT 的对象导入到 SCOTT

. . 正在导入表                "T_EXP_FILESIZE"

IMP-00047: 意外的文件序列号; 需要 2, 却出现 3

IMP-00132: 导出多个文件时, 第一个导出的文件是 c:\exp_t_exp_filesize_001.dmp

IMP-00028: 上一个表的部分导入已回退: 回退 3473302 行

IMP-00008: 导出文件中出现无法识别的语句:  

Z2V

---

IMP-00008: 导出文件中出现无法识别的语句:  

IMP-00009: 导出文件异常结束

成功终止导入, 但出现警告。

 

--小结：表结构导入了

　　　　表的记录不能导入，进行了回退rollback

        导入imp不能出现多个dmp文件的空隙性

        

  

--4,导入非首个多个连续的dmp文件        

imp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971200 file=c:\exp_t_exp_filesize_002.dmp,c:\exp_t_exp_filesize_003.dmp log=c:\imp_t_exp_filesize.log recordlength=65535        

 

Import: Release 10.2.0.3.0 - Production on 星期六 11月 10 17:49:22 2012

 

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

 

 

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

 

经由直接路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

IMP-00047: 意外的文件序列号; 需要 1, 却出现 2

IMP-00132: 导出多个文件时, 第一个导出的文件是 c:\exp_t_exp_filesize_001.dmp

IMP-00000: 未成功终止导入

 

--小结：不能导入非首个多个连续dmp文件

　　　　但会提示需要导入的dmp文件的名称

 

--5,分2次先首个文件导入，再次非首个文件导入

imp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971200 file=c:\exp_t_exp_filesize_001.dmp log=c:\imp_t_exp_filesize.log recordlength=65535  

 

 

经由直接路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

. 正在将 SCOTT 的对象导入到 SCOTT

. 正在将 SCOTT 的对象导入到 SCOTT

. . 正在导入表                "T_EXP_FILESIZE"

IMP-00047: 意外的文件序列号; 需要 2, 却出现 3

IMP-00132: 导出多个文件时, 第一个导出的文件是 c:\exp_t_exp_filesize_001.dmp

IMP-00028: 上一个表的部分导入已回退: 回退 3473302 行

IMP-00008: 导出文件中出现无法识别的语句: 

IMP-00008: 导出文件中出现无法识别的语句:  

IMP-00009: 导出文件异常结束

成功终止导入, 但出现警告。

 

 

imp userid=scott/system@orcl tables=t_exp_filesize buffer=1024000 filesize=20971200 file=c:\exp_t_exp_filesize_002.dmp,file=c:\exp_t_exp_filesize_003.dm log=c:\imp_t_exp_filesize.log recordlength=65535    

 

经由直接路径由 EXPORT:V10.02.01 创建的导出文件

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入

IMP-00047: 意外的文件序列号; 需要 1, 却出现 2

IMP-00132: 导出多个文件时, 第一个导出的文件是 c:\exp_t_exp_filesize_001.dmp

IMP-00000: 未成功终止导入

--小结：2次首个文件导入及非首个dmp文件导入，是不可以的

 

结论：imp导入和exp导出的filesize要一致

　　　imp导入可以导入自首个开始的多个dmp文件

　　　imp导入不能导入非首个多个连续或不连续的dmp文件

exp：

2、全库导出

[oracle@honey lost+found]$ exp system/systempassword@orcl full=y file=/home/lost+found/full.dmp

3、按用户导出

[oracle@honey lost+found]$ exp system/systempassword@orcl owner=olap file=/home/lost+found/olap.dmp

4、按表导出

[oracle@honey lost+found]$ exp system/systempassword@orcl tables=olap.D_TF_DATA_M4,olap.TB_TEST_1 file=/home/lost+found/olap_table_schem.dmp

5、只导出表结构不导出数据

[oracle@honey lost+found]$ exp system/systempassword@orcl owner=olap rows=n file=/home/lost+found/olap_tables_nodata.dmp

 

查看表空间大小

SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

查看某表的创建时间

 

SQL>select object_name,created from user_objects where object_name=upper('&table_name');

查看某表的大小

SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segmentswhere segment_name=upper('&table_name');

查看所有的表空间

SQL> select tablespace_name from dba_data_files order by tablespace_name;

查看后台进程

SELECT * FROM v$bgprocess WHERE paddr <> '00';

查看表空间的大小

SQL>SELECT  TABLESPACE_NAME,SUM(BYTES)/1024/1024  MB  FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

嵌入式C语言数据库语言

比如：EXEC SQL SELECT count(*) INTO :failnum:nulltmp

  FROM ECC_OFFLINE_FIELD

  WHERE TRAN_CODE = '300' AND ACC_NO=:sCardNo AND DEAL_FLAG='4';

其中nulltmp表示failnum这个变量不能为空

查看表结构

decs tablename

查看数据库版本

select * form v$version;

创建表空间

创建临时表空间：

CREATE TEMPORARY TABLESPACE DB_TEMP

         TEMPFILE 'D:appAdministratororadataNewDBDB_TEMP.DBF'

         SIZE 32M

         AUTOEXTEND ON

         NEXT 32M MASIZE UNLIMITED

         EXTENT MANAGEMENT LOCAL;

创建表空间

 CREATE TABLESPACE DB_DATA

         LOGGING

         DATAFILE 'D:appAdministratororadataNewDBDB_DATA.DBF'

         SIZE 32M

         AUTOEXTEND ON

         NEXT 32M MAXSIZE UNLIMITED

         EXTENT MANAGEMENT LOCAL;

删除表空间：drop tablespace wastbs01 including contents and datafiles;

创建新用户：

SQL>sqlplus /nolog  SQL>conn /as sysdba

CREATE USER 用户 IDENTIFIED BY 用户密码 DEFAULT TABLESPACE NOTIFYDB;

grant connect,resource to 用户;
grant unlimited tablespace to 用户;
grant create database link to 用户;
grant select any sequence,create materialized view to 用户;

grant unlimited tablespace to 用户;//授予不限制的表空间 

grant select any table to 用户;//授予查询任何表 

grant是关键字，select any table是权限,to后面是用户账号

在导入.dmp文件时可能会出现错误：只有DBA才能导入由其他DBA导出的文件

grant dba to usernanme

 

 CREATE USER NEWUSER IDENTIFIED BY BD123

         ACCOUNT UNLOCK

         DEFAULT TABLESPACE DB_DATA

         TEMPORARY TABLESPACE DB_TEMP;

 

删除用户：drop user ficecc cascade;

查看oracle表空间信息

 

1、查看表空间的名称及大小

 

    select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size

    from dba_tablespaces t, dba_data_files d

    where t.tablespace_name = d.tablespace_name

    group by t.tablespace_name;

 

2、查看表空间物理文件的名称及大小

 

    select tablespace_name, file_id, file_name,

    round(bytes/(1024*1024),0) total_space

    from dba_data_files

    order by tablespace_name;

 

3、查看回滚段名称及大小

 

    select segment_name, tablespace_name, r.status,

    (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,

    max_extents, v.curext CurExtent

    From dba_rollback_segs r, v$rollstat v

    Where r.segment_id = v.usn(+)

    order by segment_name ;

 

4、查看控制文件

 

    select name from v$controlfile;

 

5、查看日志文件

 

    select member from v$logfile;

 

6、查看表空间的使用情况

 

    select sum(bytes)/(1024*1024) as free_space,tablespace_name

    from dba_free_space

    group 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 C

    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

 

7、查看数据库库对象

 

    select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

 

8、查看数据库的版本

 

    Select version FROM Product_component_version

    Where SUBSTR(PRODUCT,1,6)='Oracle';

9、查看数据库的创建日期和归档方式

 

    Select Created, Log_Mode, Log_Mode From V$Database;四、ORACLE用户连接的管理

 

10、用系统管理员，查看当前数据库有几个用户连接：

 

SQL> select username,sid,serial# from v$session;

 

如果要停某个连接用

 

SQL> alter system kill session 'sid,serial#';

 

如果这命令不行,找它UNIX的进程数

 

SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 and ses.paddr=pro.addr;

 

说明：21是某个连接的sid数

 

然后用 kill 命令杀此进程号。

11、检查日志文件大小

select * from v$flash_recovery_area_usage;

12、删除过期的归档，delete archivelog until time 'sysdate-1' ; 删除截止到前一天的所有archivelog

13、select name from v$datafile;查看datafile的位置

 

创建实例时如果出现监听已经建立在建实例时提示再建监听可能是由于有其他实例影响