Oracle PL SQL :TIPS

28. sql server 查看用户权限
//z 2013-03-15 17:26:40 IS2120@BG57IV3.T2133125329.K[T313,L3697,R122,V4808]
    select DbRole = g.name, MemberName = u.name, MemberSID = u.sid  
    from sys.database_principals u, sys.database_principals g, sys.database_role_members m  
    where g.principal_id = m.role_principal_id  
    and u.principal_id = m.member_principal_id  
    order by 1, 2  
    go 

use master  
go  
select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid  
from sys.server_principals u, sys.server_principals g, sys.server_role_members m  
where g.principal_id = m.role_principal_id  
and u.principal_id = m.member_principal_id  
order by 1, 2  
go 

exec sp_helprotect @username = 'user name' 
go  

//z 2013-03-14 16:38:09 IS2120@BG57IV3.T2964525815.K[T298,L2884,R84,V4599]
27. 查看 sql server login 信息
EXEC sp_helplogins 'user'
输出
LoginName    SID    DefDBName    DefLangName    AUser    ARemote
user    ox1 user us_english    yes      no    

LoginName    DBName    UserName    UserOrAlias
user    master user    User    
....

ALTER LOGIN Victoria WITH 
     PASSWORD = 'V1cteAmanti55imE' 
     OLD_PASSWORD = 'B3r1000d#2-36';
GO

GO
ALTER LOGIN [LOGIN_NAME] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [LOGIN_NAME] WITH PASSWORD=N’NewPassword’ MUST_CHANGE
GO


//z 2012-08-16 16:08:33 IS2120@csdn.T1365311050[T5,L248,R11,V328]
26. ORA-00059错误解决
今天在执行ALTER TABLESPACE **  ADD DATAFILE **

命令时出现了ORA-00059:超出DB_FILES的最大值的错误信息，之前已经成功添加了几个datafile数据文件。

解决方法：
（1）SQL> show parameter db_files
NAME TYPE VALUE
------------------------------------ -----------
db_files integer 200

（2）SQL> select count(*) from v$datafile;
显示200，说明已经达到了最大值。

（3）SQL> alter database backup controlfile to trace ;在udump下面查看控制文件发现MAXDATAFILES的值是1024，可以直接修改db_files
参数解决。

（4）alter system set db_files=800 scope=spfile;
然后重启数据库OK。

//z 2012-08-16 15:48:00 IS2120@CSDN.T2515987399[T4,L68,R2,V25]
25. 一条SQL语句实现查看Oracle表记录的生成时间
oracle的建每张表时都会建一个隐含的字段ora_rowscn，看到这个字段的名字，有写人会想到scn？对了，oracle在向表中插入记录时会记录插入记录时当前数据库的scn，那么聪明的你很快就会想到，既然有每条记录的scn，那么通过scn不就能查到每条Oracle表记录的创建时间啦？全对！

只要一句话，就可以轻松查看Oracle表记录的生成时间，相信这个应该会有帮助的！

举个例子:

select to_char(scn_to_timestamp(ORA_ROWSCN),'yyyy-mm-dd hh24:mi:ss') insert_time from scott.emp;

结果将会看到emp表中每条记录创建时的时间。

默认情况下，每行记录的ORA_ROWSCN是基于Block的，这样是不准确的，除非在建表的时候执行开启行级跟踪(create table … rowdependencies)，这样就会是在行级记录scn。

所以要想查看准确的Oracle表记录创建时间则在建表时要加rowdependencies选项！



//z 2012-08-16 15:44:11 IS2120@csdn.T3789687845[T3,L68,R2,V25]
24. 查看 表 创建 修改时间 等相关信息
select   *   from   dba_objects where   OBJECT_TYPE= 'TABLE' AND OWNER='SYS' OR OBJECT_NAME='TABLE_NAME'

select   CREATED,LAST_DDL_TIME
from       ALL_OBJECTS
where     OBJECT_NAME   =   '表名 '
and         OBJECT_TPE     =   'TABLE ';

//z 2012-08-08 17:54:31 IS2120@Z.T3587414571[T62,L459,R18,V606]
23. alter table 是否需要 commit
So to clarify for those who don't know the difference between DDL and DML....

You only need a commit after:

INSERT ...
UPDATE ...
DELETE ...

statements

Merge also requires commit...
ALTER TABLE is a DDL command, and all DDL command is "auto-committing", so you don't need to commit.

22. 删除用户 所有表

用该用户登录数据库，然后可以用   select   'drop   table   '||tname|| '; '   from   tab;   生成删除所有表的脚本
然后执行之即可删掉所有的表。已试验过

drop   user   username   cascade
cascade   参数表示级联删除这个用户的所有对象

21. oracle 用户查询
select   *   from   all_users;
select   *   from   dba_users;

20. 详解Oracle创建用户权限全过程 //z 2012-07-23 15:02:20 IS2120@CSDN.T1805885382

本文将介绍的是通过创建一张表，进而实现Oracle创建用户权限的过程。以下这些代码主要也就是为实现Oracle创建用户权限而编写，希望能对大家有所帮助。

注意：每条语语分开执行，结尾必须用分号;

1.  //创建空间 

2.  create tablespace test  

3.  datafile 'c:\oracle\oradata\orcl9\test.dbf' size 50M  

4.  default storage (initial 500K  

5.  Next 500K  

6.  minextents 1 

7.  maxextents unlimited 

8.  pctincrease 0); 

1.  //Oracle创建用户权限

2.  //创建用户 

3.  create user lxg identified by lxg default tablespace test; 

4.  //授权　 

5.  grant resource,connect,dba to test; 

1.  //删除表空间  

2.  drop tablespace "空间名" including contents and datafiles 

删除用户

1.  drop user "lxg" cascade 

增加表空间

1.  alter tablespace chinawater add datafile 'c:\oracle\oradata\orcl9\ADDCHINAWATER.dbf' size 200M 

创建用户

1.  create user userName identified by password; 

创建用户 userName,密码为 password

2

给用户授权

1.  grant dba to lxg;--授予DBA权限 

2.  grant unlimited tablespace to lxg;--授予不限制的表空间 

3.  grant select any table to lxg;--授予查询任何表 

4.  grant select any dictionary to lxg;--授予 查询 任何字典 

1.  grant dba to lxg;  

2.  grant unlimited tablespace to lxg;  

3.  grant select any table to lxg;  

4.  grant select any dictionary to lxg; 

 

19. 在Oracle里，表的别名不用as,列的别名要用as?
ORA-00905:MISSING KEYWORD

列的别名也可以不用as，如：select t.a xxx from table t

在Oracle数据库中，数据表别名是不能加as的，例如：

  select a.appname from appinfo a;-- 正确

  select a.appname from appinfo as a;-- 错误

  注释：这可能是为了防止和Oracle数据库中的存储过程中的关键字as冲突的问题。

//z 2012-6-6 15:19:24 PM IS2120@CSDN
18. sql server 查看存储过程

EXECsp_helptext N'AdventureWorks.dbo.uspLogError';

17.利用pl/sql执行本地的sql文件中的sql语句

功能：pl/sql执行本地的sql文件中的sql语句
说明：比如：e:\zhaozhenlong下有create_table.sql文件，则按如下方法执行：
步骤：
1、在pl/sql的command window下，
   或在windows的开始/'运行'下，sqlplus /nolog; connect cs@orademo;
2、执行：
@@e:\zhaozhenlong\drop_table.sql
@@e:\zhaozhenlong\create_table.sql//z 2012-3-9 16:36:56 is2120@csdn

16. client ip (//z 2012-2-22 11:49:00 AM IS2120@CSDN)

CREATE FUNCTION [dbo].[GetCurrentIP] ()

RETURNS varchar(255)

AS

BEGIN

    DECLARE @IP_Address varchar(255);

 

    SELECT @IP_Address = client_net_address

    FROM sys.dm_exec_connections

    WHERE Session_id = @@SPID;

 

    Return @IP_Address;

END

15 trace (sql server)//z 2012-2-21 14:00:14 PM @TC,B

net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS /T4032

dbcc traceon(3605, -1)

查看 errorlog

取消 DBCC TRACEOFF(3605)

14 query database name,filename (//z 2012-2-17 17:01:44 PM IS2120@CSDN)

SELECT * FROM master.sys.master_files

select c.name,a.name as logicalname,a.physical_name from
master.sys.master_files a
inner join master.sys.sysdatabases c on a.database_id=c.dbid
order by dbid

select db_name(dbid),filename from sys.sysaltfiles
order by 1


DROP TABLE #DBFiles

CREATE TABLE #DBFiles

    (

      [DBName] [nvarchar](100),

      [Name] [nvarchar](100),

      [Filename] [nvarchar](100)

      )

ON  [PRIMARY]   

      

INSERT INTO

    #DBFiles

    EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, [?]..sysfiles.name,

[?]..sysfiles.filename From [?]..sysfiles'

SELECT

    *

FROM

    #DBFiles

13Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

就是sql server的认证方式只采用了windows认证的方式，改成混合模式就可以了。


1. Login to the MSSQL Server Management Studio with Windows Authentication.

2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

3. Under the Server Properties, select a page of "Security".

4. Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.
5. Restart the SQL Services and then try to login with 'sa' details.

12 修改sql server 密码

12.1
ALTER LOGIN 登录名
WITH PASSWORD = N'新密码'
    OLD_PASSWORD = N'旧密码'

//z 2012-2-15 14:51:16 PM is2120@csdn

12

12.2
企业管理器-》安全-sa ->属性

12//z 11/9/2011 :49 PM @is21

12.3
exec sp_password 旧密码,新密码

20@csdn

11. 使用 sqlplus 执行 sql 脚本

//z 2011-11-30 6:15 PM IS2120@CSDN
Oracle/PLSQL: Execute an SQL script file in SQLPlus

---

Question:  How do I execute an SQL script file in SQLPlus?

Answer:  To execute a script file in SQLPlus, type @ and then the file name.

SQL >  @{file}

For example, if your file was called script.sql, you'd type the following command at the SQL prompt:

SQL >  @script.sql

The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in before you launched SQLPlus.)

If you need to execute a script file that is not in the current directory, you would type:

SQL >  @{path}{file}

For example:

SQL >  @/oracle/scripts/script.sql

This command would run a script file called script.sql that was located in the /oracle/scripts directory.

10. 在Oracle中查看所有表select   *   from   tab/dba_tables/dba_objects/cat
用system登陆，
select   *   from   dba_tables
select   table_name   from   user_tables;

select   table_name   from   user_tables;     //当前用户的表
select   table_name   from   all_tables;     //所有用户的表
select   table_name   from   dba_tables;       //包括系统表
select   table_name   from   dba_tables   where   owner= '用户名 '

有时候需要导出某用户下的所有table、view、sequence、trigger等信息，下面的SQL可以将这些信息select出来：

select * from user_tables;

select * from user_views;

select * from user_sequences;

select * from user_triggers;

//z 2011-11-29 6:54 PM IS2120@CSDN
9. NLS_LANG 设置
NLS_LANG 参数组成
LANGUAGE指定:

　　-Oracle消息使用的语言
　　-日期中月份和日显示
　　TERRITORY指定
　　-货币和数字格式
　　-地区和计算星期及日期的习惯
　　CHARACTERSET:
　　-控制客户端应用程序使用的字符集
　　通常设置或者等于客户端(如Windows)代码页
　　或者对于unicode应用设置为UTF8

    　查看当前oracle使用的CHARACHERSET：
　　select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

　　查看instance字符集Language与Territory：
　　select * from nls_instance_parameters;

8. 查看 oracle 版本
select * from v$version
输出的版本信息
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0    Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

7. tnsnames.ora 例子
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.47)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

使用sqlplus 连接 oracle 语句
sqlplus user/pass@192.168.0.47/orcl

6. Oracle Data Dump //z 2011-11-29 1:39 PM IS2120@CSDN

--- 创建目录,这是必须的
CREATE DIRECTORY datadir1 AS '/opt/oracle/wkdir';

--- 不过还得需要这个目录的真实存在,
SQL> ! mkdir -p /opt/oracle/wkdir

--- 授权

GRANT READ,WRITE ON DIRECTORY datadir1 TO db_user;

--- 导出

expdp db_user/db_passwd@dbName SCHEMAS=db_user  parallel=4 DUMPFILE=datadir1:schema1%U.dmp LOGFILE=datadir1:expschema.log

--- 导入

impdp db_user/db_passwd@dbName SCHEMAS=db_user  parallel=4 DUMPFILE=datadir1:schema1%U.dmp EXCLUDE=constraint, ref_constraint, index,materialized_view TABLE_EXISTS_ACTION=replace logfile=datadir1:impschema.log

--- 导出时指定不导出某表
expdp clearspace/XSJpa4TdQn2RJBL@bocc SCHEMAS=clearspace DUMPFILE=datadir1:schema1%U.dmp  parallel=4  LOGFILE=datadir1:expschema.log  EXCLUDE=TABLE:\"IN\(\'table1\',\'table2\'\)\"

      多用户，将一个用户的数据导入给另一个用户
      imp u/p@orcl file=tank log=tank fromuser=seapark touser=seapark_copy full=y ignore=y
     
      使用impdp 导入 dump data
      impdp oracle/oracle@orcl schemas=user1 remap_schema="user1:user2" parallel=4 DUMPFILE=DMPDIR:1.DMP LOGFILE=DMPDIR:1.LOG exclude=constraint,ref_constraint,index TABLE_EXISTS_ACTION=REPLACE
     impdp USER/PASS  schemas="USER1"   remap_schema="USER1:USER2"    logfile=dir1:user1.log dumpfile=dir2:user1.dmp
    
     oracle不同用户下的表复制   

   1、现有同一oracle下的两用户user1和user2
    现在想把user1的表tb的结构复制到user2下，应该如何实现？  
    首先，使用更高一级权限的用户，如system登录，执行类似如下语句即可：
    create table   user1.tb as select * from   user2.tb;
    2、就有全表导出一个数据文件如：exp test/test file='d:\abc.dmp' log='d:\abc.log'现在将这个数据文件导入到用户test2
    答案 imp test/test file='d:\abc.dmp' log='d:\abc.log' fromuser=test touser=test2
    如果你是要把test导入到test2的话，否则改变fromuser

5. STATISTICS
--1.STATISTICS是一个表中某几个列的统计信息，如一个表是全校学生某次考试的分数，score列的类型是int，取值范围是0到100的整数，那么statistc就是每个分数分别有多少人。在一个特定的查询中，使用索引可能加快速度，也可能减慢速度，所以SQL server要事先对使用索引的效果做一个预测，预测的依据就是STATISTICS。
--2.默认情况下，表或索引更改了，统计会相应地自动更新，以保持统计是最新的。但是可以在数据库选项中关闭这个自动更新的功能，提高数据表的更新速度。但需要定期手动更新统计。因为过期的统计信息会对是否使用索引的判断带来误判。没发现SQL2000和SQL2005的统计有什么区别。
--3.index要依赖正确的STATISTICS才能发挥作用。而你的SQL2005优化工具对缺乏STATISTIC的列自动产生了创建语句。


4. 在表上添加 comment
对于oracle而言比较方便：
COMMENT ON TABLE Employee IS 'This is a table for Employee.';
comment on column PROJECT.ID is '251';

sql server则要通过一下这些存储过程来进行
sp_addextendedproperty adds a new extended property to a database object
sp_dropextendedproperty removes an extended property from a database object
sp_updateextendedproperty updates the value of an existing extended property
fn_listextendedproperty retrieves the value of an extended property or the list of all extended properties from a database object

在 oracle 中查看 comment
--查看表的comment
select   *   from   all_tab_comments   where   table_name= '大写表名 ';

--查看列的comment
select   *   from   all_col_comments   where   table_name= '大写表名 ';

也可以从下面两个系统表中查出：
--查看表的comment
select   *   from   user_tab_comments   where   table_name= '大写表名 ';

--查看列的comment
select   *   from   user_col_comments   where   table_name= '大写表名 ';

4.1 实用语句
oracle
select   *   from   user_tab_comments where table_name='PROJECT'
select   *   from   user_col_comments where table_name='PROJECT' and column_name='ID'

sql server
EXEC sp_addextendedproperty 'Version', '1.0.71', 'user', dbo, 'table', TABLE_NAME
EXEC sp_updateextendedproperty 'Version', '1.0.71', 'user', dbo, 'table', TABLE_NAME
exec sp_dropextendedproperty 'Version', 'user',  dbo,  'table',  TABLE_NAME

SELECT objtype, objname, name, value    FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', default, NULL, NULL);

MSDN上的例子

A. Updating an extended property on a column

The following example updates the value of property Caption on columnID in tableT1.

USE AdventureWorks2008R2;
GO
CREATE TABLE T1 (id int , name char (20));
GO
EXEC sp_addextendedproperty 
    @name = N'Caption'
    ,@value = N'Employee ID'
    ,@level0type = N'Schema', @level0name = dbo
    ,@level1type = N'Table',  @level1name = T1
    ,@level2type = N'Column', @level2name = id;
GO
--Update the extended property.
EXEC sp_updateextendedproperty 
    @name = N'Caption'
    ,@value = 'Employee ID must be unique.'
    ,@level0type = N'Schema', @level0name = dbo
    ,@level1type = N'Table',  @level1name = T1
    ,@level2type = N'Column', @level2name = id;
GO

B. Updating an extended property on a database

The following example first creates an extended property on the AdventureWorks2008R2 sample database and then updates the value of that property.

USE AdventureWorks2008R2;
GO
EXEC sp_addextendedproperty 
@name = N'NewCaption', @value = 'AdventureWorks2008R2 Sample OLTP Database';
GO
USE AdventureWorks2008R2;
GO
EXEC sp_updateextendedproperty 
@name = N'NewCaption', @value = 'AdventureWorks2008R2 Sample Database';
GO

3. ORA-00972: identifier is too long
当IDENTIFIER长度超过30个字符时会出现此错误

2. ORACLE 中查看和修改存储过程
2.1
select text from user_source where name='PROCEDURE' order by line;
2.2
select text from all_source where owner='USERNAME' and type='PROCEDURE' and name='PROCEDURENAME';
2.3
set   heading   off  
set   echo   off  
set   feedback   off  
set   pages   off  
set   long   90000  
SEELCT   DBMS_METADATA.GET_DDL( 'PROCEDURE ', 'YOURPROCEDURENAME ')   FROM   USER_OBJECTS   WHERE   OJBECT_TYPE= 'PROCEDURE '   AND   OBJECT_NAME= 'YOURPROCEDURENAME ';


1. 空字符串和null value的区别 zz
在面向sql server和oracle的代码中因为加了Column<>''导致返回的结果不一致，经查询才知道oracle对空字符串("")做了特殊处理；应该是常识，
记录一下。

Oracle/PLSQL:Difference between an empty string and a null value

---

Question:  What is the differencebetween an "empty" value and a "null" value?  When Iselect those fields that are "empty" versus "null", I gettwo different result sets.

 

Answer:  An empty string is treatedas a null value in Oracle. Let's demonstrate.

We've created a table calledsuppliers with the following table definition:

create table suppliers
( supplier_id	number,
supplier_name	varchar2(100));

Next, we'll insert two records intothis table.

insert into suppliers (supplier_id,supplier_name )
 values ( 10565, null );

insert into suppliers (supplier_id,supplier_name )
values ( 10567, '' );

The first statement inserts a recordwith a supplier_name that is null, while the second statement inserts arecord with an empty string as asupplier_name.

Now, let's retrieve all rows with asupplier_name that is an empty string value as follows:

select * from suppliers
where supplier_name = '';

When you run this statement, you'dexpect to retrieve the row that you inserted above. But instead, this statementwill not retrieve any records at all.

Now, try retrieving all supplier_idsthat contain a null value:

select * from suppliers
where supplier_name is null;

When you run this statement, youwill retrieve both rows. This is because Oracle has now changed its rules sothat empty strings behave as null values.

It is also important to note thatthe null value is unique in that you can not use the usual operands (=, <, >, etc) on a null value. Instead, you must use theIS NULL andIS NOT NULLconditions.

Acknowledgements: We'd like to thank Charles Rothfor contributing to this solution!

c# sql server oracle 空字符串 String.Empty "" '' <> null