1.如何限定特定IP访问数据库
可以利用登录触发器、cmgw或者是在$OREACLE_HOME/network/admin下新增一个protocol.ora文件（有些os可能是. protocol.ora），9i可以直接修改sqlnet.ora：
增加如下内容：
tcp.validnode_checking=yes
#允许访问的ip
tcp.inited_nodes=(ip1,ip2,...)
#不允许访问的ip
tcp.excluded_nodes=(ip1,ip2,...)
2.如何穿过防火墙连接数据库
这个问题只会在WIN平台出现，UNIX平台会自动解决。
解决方法：
在服务器端的SQLNET.ORA应类似
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
TRACE_LEVEL_CLIENT = 16
注册表的HOME0加[HKEY_LOCAL_MACHINE]
USE_SHARED_SOCKET=TRUE
3.如何利用hostname方式连接数据库
host name方式只支持tcp/ip协议的小局域网
修改listener.ora中的如下信息
(SID_DESC =
(GLOBAL_DBNAME = ur_hostname) --你的机器名
(ORACLE_HOME = E:\oracle\ora92) --oracle home
(SID_NAME = orcl) --sid name
)
然后在客户端
的sqlnet.ora中，确保有
NAMES.DIRECTORY_PATH= (HOSTNAME)
你就可以利用数据库服务器的名称访问数据库了
4.dbms_repcat_admin能带来什么安全隐患
如果一个用户能执行dbms_repcat_admin包，将获得极大的系统权限。
以下情况可能获得该包的执行权限：
1、在sys下grant execute on dbms_repcat_admin to public[|user_name]
2、用户拥有execute any procedure特权（仅限于9i以下，9i必须显示授权）
如果用户通过执行如下语句：
exec sys.dbms_repcat_admin.grant_admin_any_schema('user_name');
该用户将获得极大的系统特权
可以从user_sys_privs中获得详细信息
5.在不知道用户密码的时候，怎么样跳转到另外一个用户执行操作后并不影响该用户?
我们通过如下的方法，可以安全使用该用户，然后再跳转回来，在某些时候比较有用
需要Alter user权限或DBA权限：
SQL> select password from dba_users where username='SCOTT'; PASSWORD
-----------------------------
F894844C34402B67
SQL> alter user scott identified by lion;
User altered.
SQL> connect scott/lion
Connected.
REM Do whatever you like...
SQL> connect system/manager
Connected.
SQL> alter user scott identified by values 'F894844C34402B67';
User altered.
SQL> connect scott/tiger
Connected.
6.如何加固你的数据库
要注意以下方面
1. 修改sys, system的口令。
2. Lock，修改，删除默认用户：dbsnmp,ctxsys等。
3. 把REMOTE_OS_AUTHENT改成False，防止远程机器直接登陆。
4. 把O7_DICTIONARY_ACCESSIBILITY改成False。
5. 把一些权限从PUBLIC Role取消掉。
6. 检查数据库的数据文件的安全性。不要设置成666之类的。检查其他dba 用户。
7. 把一些不需要的服务（比如ftp, nfs等关闭掉）
8. 限制数据库主机上面的用户数量。
9. 定期检查Metalink/OTN上面的security Alert。比如：
https://www.doczj.com/doc/1415428954.html,/deploy/security/alerts.htm
10. 把你的数据库与应用放在一个单独的子网中，要不然你的用户密码很容易被sniffer去。或者采用advance security，对用户登录加密。
11. 限止只有某些ip才能访问你的数据库。
12. lsnrctl 要加密码，要不然别人很容易从外面关掉你的listener。
13. 如果可能，不要使用默认1521端口
7.如何检查用户是否用了默认密码
如果使用默认密码，很可能就对你的数据库造成一定的安全隐患，那么可以使用如下的查询获得那些用户使用默认密码
select username "User(s) with Default Password!"
from dba_users
where password in
('E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C183431F00', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'4A3BA55E08595C81', -- outln
'F894844C34402B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DA8977092B7B81', -- tracesvr
'9300C0977D7DC75E', -- oas_public
'A97282CE3D94E29E', -- websys
'AC9700FD3F1410EB', -- lbacsys
'E7B5D92911C831E1', -- rman
'AC98877DE1297365', -- perfstat
'66F4EF5650C20355', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4C5016086B2DC6A', -- sys
'D4DF7931AB130E37') -- system
/
8.如何修改默认的XDB监听端口
Oracle9i默认的XML DB把HTTP的默认端口设为8080，这是一个太常用的端口了，很多别的WebServer都会使用这个端口，
如果我们安装了它，最好修改一下，避免冲突，如果不使用呢，就最好不要安装
提供三种修改的方法
1.dbca，选择你的数据库，然后Standard Database Features->Customize->Oracle XML DB option，进入这个画面你应该就知道怎么改了。
2.OEM console，在XML Database 的配置里面修改
3.用oracle提供的包：
-- 把HTTP/WEBDAV端口从8080改到8081
SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/httpconfig/http-port/text()',8081))
/
-- 把FTP端口从2100改到2111
SQL> call dbms_xdb.cfg_update(updateXML(dbms_xdb.cfg_get(),
'/xdbconfig/sysconfig/protocolconfig/ftpconfig/ftp-port/text()',2111))
/
SQL> commit;
SQL> exec dbms_xdb.cfg_refresh;
-- 检查修改是否已经成功
SQL> select dbms_xdb.cfg_get from dual;
9.怎么捕获用户登录信息，如SID，IP地址等
可以利用登录触发器，如
CREATE OR REPLACE TRIGGER tr_login_record
AFTER logon ON DATABASE
DECLARE
miUserSid NUMBER;
mtSession v$session%ROWTYPE;
CURSOR cSession(iiUserSid IN NUMBER) IS
SELECT * FROM v$session
WHERE sid=iiUserSid;
BEGIN
SELECT sid INTO miUserSid FROM v$mystat WHERE rownum
10.程序报错
ORA-01555: snapshot too old: rollback segment number 148 with name
"_SYSSMU148$" too small
可以查询错误原因
oerr ora 01555
得到如下信息
01555, 00000, "snapshot too old: rollback segment number %s with name \"%s\" too small"
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments
说明一个事务的执行时长超过了系统设定的最大值.
SQL> show parameters undo
比如
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 16200
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2
则说明是16200秒.
11.如果你要用ultraEdit-32编辑oracle脚本，可以设置对关键字不同亮度的显示。点"advanced"-->"configuration"-->"Syntax Highlighting"
-->"open"，然后把下面的代码追加到最后保存,再重新打开ultraEdit，编辑.sql文件就可以看到了。这样颜色上容易分辨。方便一些。
/L8"sql" Nocase Line Comment = -- Block Comment On = /* Block Comment Off = */ String Chars = ' File Extensions = SQL
/Delimiters = ~!%@^&*()-+=|\/{}[]:;"'<> , .?
/Function String = "%[A-Z _]*[~\s]+(*)"
/C1 "Keywords"
ABORT ACCEPT ACCESS ADD ALL ALTER AND ANY ARRAY ARRAYLEN AS ASC ASSERT ASSIGN AT AUDIT AUTHORIZATION AVG
BASE_TABLE BEGIN BETWEEN BINARY_INTEGER BODY BOOLEAN BY
CASE CHAR CHAR_BASE CHECK CLOSE CLUSTER CLUSTERS COLAUTH COLUMN COMMENT COMMIT COMPRESS CONNECT CONSTANT CRASH CREATE CURRENT CURRVAL CURSOR
DATABASE DATA_BASE DATE DBA DEBUGOFF DEBUGON DECLARE DECIMAL DEFAULT DEFINITION DELAY DELETE DESC DIGITS DISPOSE DISTINCT DO DROP ELSE ELSIF END ENTRY EXCEPTION EXCEPTION_INIT EXCLUSIVE EXISTS EXIT FALSE FETCH FILE FLOAT FOR FORM FROM FUNCTION
GENERIC GOTO GRANT GROUP
HAVING
IDENTIFIED IF IMMEDIATE IN INCREMENT INDEX INDEXES INDICATOR INITIAL INSERT INTEGER INTERFACE INTERSECT INTO IS
LEVEL LIKE LIMITED LOCK LONG LOOP
MAX MAXEXTENTS MIN MINEXTENTS MINUS MISLABEL MOD MODE
NATURAL NATURALN NEW NEXTVAL NOAUDIT NOCOMPRESS NOLOGGING NOT NOWAIT NULL NUMBER NUMBER_BASE
OF OFFLINE ON ONLINE OPEN OPTION OR ORDER OTHERS OUT
PACKAGE PARTITION PCTFREE PCTUSED PLS_INTEGER POSITIVE POSITIVEN PRAGMA PRIOR PRIVATE PRIVILEGES PROCEDURE PUBLIC
RAISE RANGE RAW REAL RECORD REF RELEASE REMR RENAME RESOURCE RETURN REVERSE REVOKE ROLLBACK ROW ROWID ROWLABEL ROWNUM ROWS ROWTYPE RUN
SAVEPOINT SCHEMA SELECT SEPARATE SESSION SET SHARE SMALLINT SPACE SQL SQLCODE SQLERRM START STATEMENT STDDEV SUBTYPE SUCCESSFUL SUM SYNONYM SYSDATE
TABAUTH TABLE TABLES TASK TERMINATE THEN TO TRIGGER TRUE TYPE
UID UNION UNIQUE UPDATE USE USER
VALIDATE VALUES VARCHAR VARCHAR2 VARIANCE VIEW VIEWS
WHEN WHENEVER WHERE WHILE WITH WORK WRITE
XOR
/C2 "Packages"
DBMS_OUTPUT
DBMS_JOB
DBMS_SQL
/C3 "Package Methods"
BIND_VARIABLE BIND_VARIABLE_CHAR BIND_VARIABLE_RAW
BIND_VARIABLE_ROWID BROKEN
CHANGE CLOSE_CURSOR COLUMN_VALUE COLUMN_VALUE_CHAR COLUMN_VALUE_RAW COLUMN_VALUE_ROWID
DEFINE_COLUMN DEFINE_COLUMN_CHAR DEFINE_COLUMN_RAW DEFINE_COLUMN_ROWID DISABLE
ENABLE EXECUTE EXECUTE_AND_FETCH
FETCH_ROWS
GET_LINE GET_LINES
INTERVAL IS_OPEN
LAST_ERROR_POSITION LAST_ROW_COUNT LAST_ROW_ID
LAST_SQL_FUNCTION_CODE
NEW_LINE NEXT_DATE
OPEN_CURSOR
PARSE PUT PUT_LINE
REMOVE
SUBMIT
WHAT
/C4 "SQL*Plus Commands"
EXECUTE
GRANT
REPLACE
RUN
START
/C5 "SQL*Plus Functions"
ABS ADD_MONTHS ASCII
CEIL CHR CONCAT CONVERT COS COSH
DECODE
EXP
FLOOR
GREATEST
HEXTORAW
INITCAP INSTR
LAST_DAY LENGTH LN LOG LOWER LPAD LTRIM
MOD MONTHS_BETWEEN
NEXT_DAY NEW_TIME NLS_INITCAP NLS_LOWER NLS_UPPER NVL POWER
RAWTOHEX REPLACE ROUND ROWIDTOCHAR RPAD RTRIM
SIGN SIN SINH SOUNDEX SQRT SUBSTR SUBSTRB
TAN TANH TO_CHAR TO_DATE TO_LABEL TO_MULTI_BYTE TO_NUMBER TO_SINGLE_TYPE TRANSLATE TRUNC
UPPER USERENV
VSIZE
/C6 "Should Be Reserved"
BEFORE
EACH
FOR
KEY
PRIMARY
WHEN WHERE WHILE
/C7 "CONST Var"
DUP_VAL_INDEX TIMEOUT_ON_RESOURCE TRANSACTION_BACKED_OUT INVALID_CURSOR NOT_LOGED_ON LOGIN_DENIED
NO_DATA_FOUND SYS_INVALID_ROWID TOO_MANY_ROWS ZERO_DIVIDE INVALID_NUMBER STORAGE_ERROR PROGRAM_ERROR VALUE_ERROR NOTFOUND FOUND
许多软件公司都理解开发不依赖于特定数据库类型(例如Oracle、SQL Server、DB2)的应用程序的重要性，它可以让客户们选择自己习惯的平台。一般来说，软件开发人员都能够识别出他们的负责数据库维护的客户，和必须使用现有平台和个性化的客户。 关于Oracle和SQL Server之间的区别，已经有很多篇文章从企业的角度和数据库管理员的角度出发描写了两者之间的一般差别。在这篇文章里面，我将会从应用程序的角度向你描述SQL erver 和oracle平台之间的区别，并且将会讨论开发不依赖于数据库环境的应用程序的几种可能的方法。与此同时，我不会再论讨对于应用程序来说，两个平台之间显而易见的区别，例如表的分割和索引。 定义通用接口和语言有很少的几种通用语言和接口可以让应用程序不依赖于数据库，想来也可以以同样的方式应用在关系型数据库上面: ANSI是美国国家标准局定义的，这是一家志愿者成员的组织(用私人基金运转)，他们在有关设备和程序等广泛领域内开发了国家承认的标准。在数据库领域， ANSI定义了编写SQL命令的标准，假设命令可以运行在任何的数据库上，而不需要更改命令的语法。 ODBC是开放数据库连接(ODBC)接口，微软定义的，它可以让应用程序访问数据库管理系统(DBMS)中的数据，使用SQL作为访问数据的标准。 ODBC允许最大的互联性，这意味着一个单个的应用程序可以访问不同的数据库管理系统。然后，应用程序终端用户可以添加开放数据库连接(ODBC)数据库驱动来将应用程序与他们所选择的数据库管理系统链接上。 OLE DB 是ODBC的继承者，是一组允许例如基于VB、C++、Access等任何可以连接到类似SQL Server、Oracle、DB2、MySQL等后台的“前台”的软件组件。在许多种情况下，OLE DB组件都比原有的ODBC提供了好得多的性能。 JDBC(Java数据库连接)应用程序接口是Java编程语言和广泛范围的数据库，SQL数据库和其他表列数据源(例如电子表格或者普通文本文件)之间，进行不依赖于数据库的连接的行业标准。JDBD应用程序接口为基于SQL的数据库访问提供了调用级别的应用程序接口。真实世界中的通用接口不幸的是，并不是所有数据库级别的命令都是ANSI，每个数据库平台都有自己的扩展功能。对于ANSI或者通用接口，一般来说都代表着几本功能，因此也可能意味着丧失了性能方面的竞争力。对于小型数据库和小型应用程序来说，要维护对数据库的通用访问是简单的，但是当数据库和/或应用程序变得越来越大，越来越复杂，你就不得不向代码中添加功能。 SQL Server和Oracle的常用函数对比 ---------数学函数 1.绝对值 S:select abs(-1) value O:select abs(-1) value from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual
（O管理）玩转实战教程(韩顺平)学习笔记
韩顺平—玩转oracle视频教程笔记 一：Oracle认证，与其它数据库比较，安装 oracle的卸载 1.停止所有与ORACLE相关的服务。 2. 使用OUI（Oracle Universal Installer）卸载Oracle软件。 “开始”->“程序”->“Oracle-OraDb110g_home1|Oracle installation product|Universal installer. 3.删除注册表内容。运行regedit命令，删除下面内容：HKEY_LOCAL_MACHINE|SOFTWARE|ORACLE注册表键，删除此键。 HKEY_LOCAL_MACHINE|SYSTEM|CurrentControlSet|Services，删除Services键下所有以oracle为首的键。 HKEY_LOCAL_MACHINE|SYSTEM|CurrentControlSet|Services|Eventlog|Application，删除此键下所有以oracle为首的键。 HKEY_CLASSES_ROOT，删除此键下所有以Ora，Oracle，Orcl，EnumOra 为前缀的键。 HKEY_CURRENT_USER|Software| Microsoft|Windows|CurrentVersion|Explorer|MenuOrder|Start Menu|Programs，删除此键下所有以oracle为首的键。 HKEY_LOCAL_MACHINE|SOFTWARE|ODBC|ODBCINST.INI注册表键，删除了
Oracle常见数值函数 ----***特别说明***： x 可以是纯的数值，也可以是数值型表达式/* ABS(x)返回x绝对值 eg. */ selectabs(100),abs(-100) from dual; /* sign(x)判断x的正负，正数返回1，负数返回-1,0返回0； eg. */ selectsign(100),sign(-100),sign(0) from dual;
/* round(x[,n])对x进行四舍五入，保留n位小数，其中n采用其整数部分； 没有n时默认四舍五入到整数位，n为负数时，四舍五入保留小数点左边n位(补零)， eg. */ selectround(5555.6666, 2.1), round(5555.6666, -2.6), round(5555.6666) from dual; /* trunc(x)对x进行直接截取，保留n位小数，其中n采用其整数部分； 没有n时默认截取到整数位，n为负数时，截取保留小数点左边n位(补零)， eg. */ selecttrunc(5555.66666,2.1), trunc(5555.66666,-2.6), trunc(5555.033333) from dual; /* ceil(x)对x进行向上取整，返回不小于x的最小整数(可以是整数x本身)。
eg. */ selectceil(3.1), ceil(2.8+1.3), ceil(0) from dual; /* floor(x)对x进行向下取整，返回不大于x的最大整数(可以是整数x本身)。eg. */ selectfloor(3.1), floor(2.8+1.3), floor(0) from dual; /* mod(x,y)求x除以y的余数，x，y为数字型表达式。 eg. */ selectmod(23,8),mod(24,8) from dual;
1.用insert语句进行行数据的迁移 Insert into 表名(列，列) select 列，列from emp where 条件 2.用update更新数据(使用子查询)：希望员工scott的岗位，工资，补助与smith员工一样？ update emp set (job,sal,comm)=(select job,sal,comm. From emp where ename=?SMITH?) where ename=?SCOTT?; 3.事务 (1)锁 (2)提交事务 (3)回退事务（一开始就做保存点(savepoint)） (4)事务的几个重要操作 1)设置保存点:savepoint a 2)取消部分事务:rollback to a 3)取消全部事务:rollback (5)只读事务:set transaction read only（当前用户能看到之前的操作，若其他用户进行更 新，该用户之后的操作都不能看到更新） (6)字符函数 (1)lower(char):将字符串转化为小写的格式 (2)upper(char): 将字符串转化为大写的格式 (3)length(char):返回字符串的长度 (4)substr(char,m,n):取字符串的子串，从m开始取，取n个 (5)例：以首字符大写的方式显示所有的员工的姓名: (1)完成首字母大写：select upper(substr(ename,1,1)) from emp; (2)完成后面字母小写：select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并:select upper(substr(ename,1,1)) || lower(substr(ename,2,length(ename)-1)) from emp; (6)replace(char1,search_string,replace_string) (7)instr(char1,char2,[,n[,m]])—取子串在字符串的位置 (8)例：显示所有员工的姓名，用”我是A”替换所有”A”:select replace(ename,?A?,? 我是A?) from emp; (7)数学函数 (1)round(n,[m]):用于四舍五入 (2)trunc(n,[m]):用于截取数字 (3)mod(m,n)：取模 (4)floor(n)：返回<=n的最大整数 (5)ceil(n):返回>=n的最小整数 (6)例：显示在一个月为30天的情况所有员工的日薪金，忽略余数:select floor(sal/30),ename from emp; (8)日期函数 (1)sysdate (2)add_months(d,n) (3)last_day(d):返回指定日期所在月份的最后一天 例：返回在八个月前雇佣的员工(已经入职8个月多的员工):select * from emp where sysdate>add_months(hiredate,300); 例：对于每个员工，显示其加入公司的天数:select trunk(sysdate-hiredate) “入职天
1.Oracle 工具:sqlplus Sqlplus / as sysdba Shutdown immediate(关闭数据库) Startup(启动数据库) 注意：数据库开启才可以进行操作 Select username,account_status from dba_users;(查询数据库中所有的用户名称与用户状态) Alter user scott identified by tiger account unlock;(scott用户解锁) Sqlplus scott/tiger(使用SCOTT 密码为tiger登陆ORACLE数据库) Connect scott/tiger (在SQL>中直接使用SCOTT用户连接数据库) Show all;(看所有变量) Set sqlprompt “_user>”(设置sqlplus环境下面的提示符用自身用户显示) 注意：如要变量下次重启生效必须把变量写入 /u01/app/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql中 Host(回到系统) Help index（查看所有命令） Help+命令(类似于LINUX中的MAN,查看命令的格式与用法) Disconnect(关闭SQLPLUS工具但是不离开数据库) Describe emp(查看EMP表) Define(定义变量,常用与写脚本用) 如：DEFINE _EDITOR =”vi” (CHAR) (定义VI编辑器用于保存最后一条执行的SQL语句) Save /u01/app/oracle/aa.sql (保存SQL语句) Get /u01/app/oracle/aa.sql (调用保存的SQL语句) 2.select（select 查询语句是ORACLE中最常用的语句） DML语句包括（insert 写入，update改变，delect删除，merge两张表同步） 注意：merge常用在ETL（数据仓库）底下 DDL数据定义语句包括（create创建对象，alter修改，drop删除对象，rename改名，truncate 删除整个表） TCL事务处理语句（commit提交立刻生效，rollback回滚，savepoint保存点，savepoint(保存点) DCL权限语句（grant赋予权限，revoke(收回权限) ORACLE的表称之为堆表（keep table） SELECT (1) writing basic SQL select statemanes 1. selecting all columns SCOTT>select * from dept; SCOTT>select dname,deptno,loc from dept; Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;
Oracle常用函数/过程说明主要介绍Oracle的系统函数、过程和包。 SQL常用函数： 数值函数： ABS Purpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 CEIL Purpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) "Ceiling" FROM DUAL;
Ceiling ---------- 16 * MOD Syntax MOD(m,n) Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) "Modulus" FROM DUAL; Modulus ---------- 3 * ROUND Syntax ROUND(n[,m]) Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.
1、在开发环境中连接到数据库，进行基本的select查询操作； 2、熟悉plsql的使用； 3、熟悉sqlplus相关命令（登录、查询、导入导出等） 登录sqlplus： 第一：使用dos窗口登录sqlplus Sqlplus 用户名/密码@数据库实例名 Sqlplus system/密码@数据库实例名as sysdba 第二：使用oracle自带的一个sqlplus登录，提供界面，显得更简单一些。 显示当前用户名：show user; 创建一个用户：create user 用户名identified by 密码； 给用户赋予登录的权限：grant connect to 用户名；（此时才可以使用这个用户来登录这个数据库。）给其赋予dba的权限。 修改用户的密码：alter user 用户名identified by 新密码； 查询： Select * from t_user; Select id from t_user; Select name from t_user; Select birthday from t_user; Select id,name from t_user; 导入导出： 导出表: （注意，导出表的exp命令不是在sqlplus下使用的，是在dos窗口下使用的命令。） exp userid=test/sa@test tables=(qx) file=d:\e1223.dmp exp userid=test/sa@test tables=(t_user,qx) file=f:\test.dmp 导出方案： Exp userid=test/sa@test owner=test file=f:\test2.dmp 导出数据库： Exp userid=test/sa@test full=y inctype=complete file=f:\all.dmp 导入表： 下面以一个例子来说明： 看下面的图，我的用户名test，密码sa，数据库实例名test，所有的表都在这里 现在我执行导出表JD的操作：exp userid=test/sa@test tables=(jd) file=f:\jd.dmp 在我的f盘下就出现了这么一个.dmp文件
Oracle笔记 实现中英文转换 Alter session set nls_language=‘ADMIN’ 从已知表中复制数据和结构 Create table test as select * from dept； 从已知表中复制数据和结构，但是不包括数据 Create table test as select * from dept where 1=2 插入数据 Insert into test select * from dept 二、运算符 +——*/可以在select 语句中使用 ||连接两个字段，select deptno ||dname from dept 比较运算付：> >= = !=< <= 逻辑运算符：not ，or 集合运算符：intersect，union，union all，minus， 使用ord er by 的时候必须使用位置序号，不能使用列名 Select *from emp intersect select * from emp where deptno=10； Select *from emp minus select * from emp where deptno=10； ---不包括重复行 Select *from emp where deptno=10 union select * from emp where deptno in（10，20）；----包括重复行 Select *from emp where deptno=10 union all select * from emp where deptno in（10，20）； 三、常用ORACLE函数
●PLSQL控制台输出语句 SET serveroutput ON; --打开控制台输出服务 dbms_output.put_line('values2='||var_val); --输出语句 ●PLSQL动态变量 var_str := '&input'; ●创建表空间和用户 --创建表空间 CREATE TABLESPACE "BCPBS" LOGGING DATAFILE 'D:\app\E430\oradata\orcl\BCPBS_01.ora' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED, 'D:\app\E430\oradata\orcl\BCPBS_02.ora' SIZE 2048M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; --建立用户 CREATE USER "BCPBS" PROFILE "DEFAULT" IDENTIFIED BY "bcpbs123" DEFAULT TABLESPACE "BCPBS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK; GRANT "CONNECT" TO "BCPBS"; GRANT "DBA" TO "BCPBS"; GRANT "RESOURCE" TO "BCPBS"; ●删除表空间和用户 drop user bcpbs cascade ; drop tablespace BCPBS including contents and datafiles cascade constraints ; ●自定义函数 CREATE OR REPLACE FUNCTION fun_level_value(level_value number) RETURN number IS return_value number:=null; BEGIN CASE level_value WHEN 0 THEN return_value:='0'; WHEN 1 THEN return_value:='1'; WHEN 2 THEN return_value:='2'; WHEN 3 THEN return_value:='3'; WHEN 6 THEN return_value:='8'; ELSE
oracle学习心得 一、sqlserver的理解 sqlserver服务器就像一栋大楼，大楼里的机房就像服务器的数据库，机房里的电脑如同数据库里的表 1、登录用户可以登录服务器——可以进大楼 2、登录用户成为数据库用户才能进指定的数据库——进入大楼的人给了某个机房的钥匙 才能进入机房 3、登录用户有权限使用表——进入机房的人有电脑的密码才能使用电脑 二、oracle的理解 oracle服务器（全局数据库）就像一个商场，商场的每一家公司是表空间，公司的业务是表 1、数据库由多个表空间组成——商场里有多家公司组成 2、表空间由段组成——公司要有自己的经营业务，可以只有一个业务，就是一个表空 间中只有一个段，可以有多个业务，就是一个表空间有多个段 3、段由区组成——单个业务的细分类别。例如有家公司经营三个业务，卖书，卖家电，
卖衣服，则每个业务就是一个段。而每个业务又有细分，比如卖书的话要进行分类了。计算机区，人文区，小说区等，每一区都要放上书架存放书籍，则书架就是oracle块，存放数据的 三、数据库，表空间，用户（指定默认表空间），表统统由管理员管理 四、在oem中管理数据库的步骤 1、创建 1)存储——表空间——创建表空间(tomspace)（类似于在sql中创建数据库， 通常可以省略，使用默认表空间为users,,临时表空间为temp） 2)首先展开安全性——创建新用户（tom，指定表空间）（类似于在sql中指定 数据库用户） 3)创建表——指定方案（用户）和表空间（列名不要带<>） 4)设置约束 5)输入信息：方案——用户名——表——右击——查看/编辑目录… 2、修改 1）方案——用户名——表 2）修改表结构，添加约束
oracle中常用函数大全 1、数值型常用函数 函数返回值样例显示 ceil(n) 大于或等于数值n的最小整数select ceil(10.6) from dual; 11 floor(n) 小于等于数值n的最大整数select ceil(10.6) from dual; 10 mod(m,n) m除以n的余数,若n=0,则返回m select mod(7,5) from dual; 2 power(m,n) m的n次方select power(3,2) from dual; 9 round(n,m) 将n四舍五入,保留小数点后m位select round(1234.5678,2) from dual; 1234.57 sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 select sign(12) from dual; 1 sqrt(n) n的平方根select sqrt(25) from dual ; 5 2、常用字符函数 initcap(char) 把每个字符串的第一个字符换成大写select initicap('mr.ecop') from dual; Mr.Ecop lower(char) 整个字符串换成小写select lower('MR.ecop') from dual; mr.ecop replace(char,str1,str2) 字符串中所有str1换成str2 select replace('Scott','s','Boy') from dual; Boycott substr(char,m,n) 取出从m字符开始的n个字符的子串select substr('ABCDEF',2,2) from dual; CD length(char) 求字符串的长度select length('ACD') from dual; 3 || 并置运算符select 'ABCD'||'EFGH' from dual; ABCDEFGH 3、日期型函数 sysdate当前日期和时间select sysdate from dual;
Oracle的四个主要用户 1.超级管理员sys/change_on_install 2.普通管理员system/manager 3.普通用户scott/tiger 4.大数据用户sh/sh 简单查询 1.查询表结构 DESC 表名 2.简单查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] … FROM 表名 [别名] （1）查询时也可以使用四则运算，如：SELECT (字段*x)+y FROM 表名 （2）可以使用“||”连接字段与字符串，如： select '工作与工资：' || job || sal 工作工资 from emp 执行结果： 3.限定查询SELECT [DISTINCT] *|[字段 [别名]] [,字段 [别名]] … FROM 表名 [别名] [WHERE 条件(s)] （1）Where字句后可以增加多个条件，最常见的条件就是最基本关系运算：>、<、>=、<=、!=(<>)、BE TWEEN…AND、LIKE、IN(指定范围)、IS NULL(为空)、AND(且)、OR(或)、NOT(非)。 （2）多个条件可以使用AND与OR连接。 例如：select * from emp where sal>1300 and sal<4000 （3）范围判断BE TWEEN…AND：BETWEEN 最小值 AND 最大值（大于等于最小值，小于等于最大值），BE TWEEN…AND 对数字、日期都可以使用！！！ 例如：select * from emp where sal between 1300 and 4000 例如：select * from emp where hiredate between '01-1月-1981' and '31-12月-1981' （4）判断为空IS (NOT) NULL 例如：select * from emp where comm is null 例如：select * from emp where comm is not null 例如：select * from emp where not comm is null （5）指定范围的判断(NOT) IN 例如：select * from emp where empno in(7369,7566,7799)
Oracle常用函数及使用案例（珍藏版） 一：sql函数： lower（char）:将字符串转化为小写的格式。 upper（char）:将字符串转化为大写的格式。 length（char）：返回字符串的长度。 substr（char，m，n）：取字符串的字串。 案例1.将所有员工的名字按小写的方式显示 select lower(ename),sal from emp; 案例2.将所有员工的名字按大写的方式显示。 select upper(ename),sal from emp; 案例3.显示正好为五个字符的的员工的姓名。 select * from emp where length(ename)=5; 案例4.显示所有员工姓名的前三个字符。 select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。 案例5.以首字母为大写的方式显示所有员工的姓名。 （1）首字母大写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母小写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp; 案例6.以首字母为小写的方式显示所有员工的姓名。（需要有较高的灵活度，细心分析和清晰思路） （1）首字母小写：select upper(substr(ename,1,1)) from emp; （2）完成后面字母大写。select lower(substr(ename,2,length(ename)-1)) from emp; (3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp; 案例7.函数（替换）：replace(char1,search_string,replace_string); 显示所有员工的姓名，用“我要替换A”替代所有“A”。 select replace(ename,'A','我是老鼠')from emp; 案例8.以首字母为小写的方式显示所有员工的姓名。 select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp; 案例9.以首字母为大写的方式显示所有员工的姓名。 Select replace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp; 二：数学函数：(在财务中用的比较多) ronud（sal）用于四舍五默认取整； ronud（sal，1）用于四舍五留一位小数。 trunc（sal）取整，忽略小数。截去小数部分。 trunc（sal，1）截取；小数点留一位，之后的右边的省去。 trunc（sal，-1）截取；只留整数，个位数取零。 floor（sal）向下最接近取整；比如1.1值为1.
基础知识 表3.2 Oracle数据类型
表3.3 XSB的表结构
操作表 创建表 CREATE TABLE [schema.] table_name ( column_namedatatype [DEFAULT expression] [column_constraint][,…n]
Oracle常用函数列表速查 PL/SQL单行函数和组函数详解 函数是一种有零个或多个参数并且有一个返回值的程序。在SQL中Oracle内建了一系列函数，这些函数都可被称为SQL或PL/SQL语句，函数主要分为两大类： 单行函数 组函数 本文将讨论如何利用单行函数以及使用规则。 SQL中的单行函数 SQL和PL/SQL中自带很多类型的函数，有字符、数字、日期、转换、和混合型等多种函数用于处理单行数据，因此这些都可被统称为单行函数。这些函数均可用于SELECT,WHERE、ORDER BY 等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等单行函数。 SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename) 单行函数也可以在其他语句中使用，如update的SET子句，INSERT的V ALUES子句，DELET 的WHERE子句,认证考试特别注意在SELECT语句中使用这些函数，所以我们的注意力也集中在SELECT语句中。 NULL和单行函数 在如何理解NULL上开始是很困难的，就算是一个很有经验的人依然对此感到困惑。NULL值表示一个未知数据或者一个空值，算术操作符的任何一个操作数为NULL值，结果均为提个NULL值,这个规则也适合很多函数，只有CONCAT,DECODE,DUMP,NVL,REPLACE在调用了NULL参数时能够返回非NULL值。在这些中NVL函数时最重要的，因为他能直接处理NULL值，NVL有两个参数：NVL(x1,x2),x1和x2都式表达式，当x1为null时返回X2,否则返回x1。 下面我们看看emp数据表它包含了薪水、奖金两项，需要计算总的补偿 column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2 不是简单的将薪水和奖金加起来就可以了，如果某一行是null值那么结果就将是null，比如下面的例子： update empset salary=(salary+bonus)*1.1 这个语句中，雇员的工资和奖金都将更新为一个新的值，但是如果没有奖金，即salary + null,那么就会得出错误的结论，这个时候就要使用nvl函数来排除null值的影响。 所以正确的语句是： update empset salary=(salary+nvl(bonus,0)*1.1
select* |字段表名 from 表名 where 布尔表达式【条件】 externalcandidate 职员相关信息 contractrecruiter 猎头公司 查看表里所有字段 describe[描述] contractrecruiter select cname,nperecentagecharge from contractrecruiter 不显示原来名字，显示现在名字【用AS】 select cname as "Recruiter Name",nperecentagecharge as "Hire Fees" from contractrecruiter 不显示原来名字，显示现在名字【可不用AS】 select cname "Recruiter Name",nperecentagecharge "Hire Fees" from contractrecruiter 【双引号是否写】 双引号是为了区分大小写 职位表 position select *from position 缺额运算:[想减] select VDESCRIPTION , nbudgetedstrength, NBUDGETEDSTRENGTH -NCURRENTSTRENGTH , NYEAR from position
select VDESCRIPTION "Potion", nbudgetedstrength "Budgeted Strength" , NBUDGETEDSTRENGTH -NCURRENTSTRENGTH "Vacancies", NYEAR "Year" from position 显示非重复运行 查询来源地的人数 describe externalcandidate 查看职员城市名字 select ccity from externalcandidate 查看职员城市名字【名字不重复，插入一个关键字：distinct】select distinct ccity,cstate from externalcandidate 运算符： 两列 select vfirstname,vlastname from externalcandidate 字段拼接 select vfirstname||vlastname from externalcandidate select vfirstname||' '||vlastname from externalcandidate 学校 describe college 查看所有学校 select *from college 查看只是加利福尼亚的学校 select * from college where cstate='California' 一个条件 select vfirstname,vlastname,dbirthdate,ntestscore from externalcandidate where dbirthdate>='01-1月-70'
Oracle知识点总结 根据阎赫老师讲义整理Zealjiang 1、Oracle数据库的安装和配置 ⑴Oracle Internet级数据库 SQLServer 中小企业级数据库 Access 桌面级数据库 ⑵Oracle的安装 注意：来源和目标的目录不允许有中文或空格 ⑶Oracle数据库启动时必需开启的后台服务 ①OracleOrahome90TNSListener 使第三方的软件或语言访问 ②OracleServiceETC Oracle的实例 CRUD 增删改查 注意：②中的ETC是你起的实例的名字 ⑷Oracle的开发工具 ①DOS界面的开发平台 -> 运行->sqlplus ②Oracle本身基于DOS的平台 ->运行-> sqlplusw ③Oracle Enterprise Manager Console ④PL/SQL Developer7.1.5 ⑸创建一个表的完整流程 ①创建一个数数库 例子：创建一个数据库 ETC , 物理文件放在F:\ ，初始化1m ,自增长开启 create tablespace etc
datafile 'f:\etc.dbf' size 1m autoextend on; 删除表空间 drop tablespace 数据库名称; ②为该数据库创建一个帐号和密码 语法： create user 用户名称 identified by 密码 default tablespace 数据库名称 注意：1、在Oracle中账号和数据库one2one绑定 2、Oracle的密码不允许为纯数字或空 3、需要在system账号完成动作 修改Oracle的账号密码 语法：alter user 用户 identified by 新密码 ③用grant 权限 to 用户为该帐户授权 语法： grant 权限名称 to 用户; 撤销权限 语法：revoke 权限名称 from 用户名; ④在该帐号下创建表
数据库基本概念： 数据库：Database ，DB （数据） 数据库管理系统：（Database Management System，DBMS）oracle ,DB2,Sql_server 等 数据库系统：（Database System ，DBS） 关系型数据库：（Relationship Database ,RDB） SQL语言:(Structured Query Language,结构化查询语言) 数据建模： 要将现实世界中客观存在的事物以数据的形式存储到计算机中并进行处理，就需要对其进行分析，抽象，进而确定数据的结构以及数据间的内在联系，这一过程称为数据建模。 数据模型应满足三个方面的要求： 1，能够比较真实地模拟现实世界 2，容易为人所理解； 3，便于计算机实现。 数据模型三要素： 数据结构---描述事物的静态特性； 数据操作---描述事物的动态特性； 完整性约束----描述事物内部和事物间的约束性关系。 现实世界-------认识抽象-------->概念世界-------转换--------->机器世界 概念数据模型（Conceptual Database Model，CDM） CDM从用户的观点出发对信息进行建模，并不依赖于具体的计算机系统或某个DBMS系统，主要用于数据库的概念设计。 CDM以实体--关系（E-R）模型为基础，将现实世界中的客观对象抽象为实体和关系。 到机器世界中，CDM将被转化为特定DBMS所支持的物理数据模型（Physical Database Model ，PDM）。 CDM相关术语： 实体（Entity）:客观存在并且可以相互区分开来的事物。 实体集（Entity Set）：同一类实体的集合。 属性（Attribute）：描述实体的特性。 关系（Relationship）:实体集之间的对应关系（现实世界事物之间的相互关联）。 E-R图三要素： 实体：用矩形框表示
AD1. 执行一个SQL脚本文件 SQL>start file_name SQL>@ file_name 我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。 4. 将显示的内容输出到指定文件 SQL> SPOOL file_name 在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。 5. 关闭spool输出 SQL> SPOOL OFF 只有关闭spool输出，才会在输出文件中看到输出的内容。 7. COL命令： 主要格式化列的显示形式。 1). 改变缺省的列标题 COLUMN column_name HEADING column_heading For example: Sql>select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- --------- 10 ACCOUNTING NEW YORK sql>col LOC heading location sql>select * from dept; DEPTNO DNAME location --------- ---------------------------- ----------- 10 ACCOUNTING NEW YORK 2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上： Sql>select * from emp Department name Salary ---------- ---------- ---------- 10 aaa 11 SQL> COLUMN ENAME HEADING ’Employee|Name’ Sql>select * from emp Employee Department name Salary ---------- ---------- ---------- 10 aaa 11 note: the col heading turn into two lines from one line. 3). 改变列的显示长度： FOR[MAT] format Sql>select empno,ename,job from emp; EMPNO ENAME JOB ---------- ---------- --------- 7369 SMITH CLERK