在Oracle中查看各个表、表空间占用空间的大小
-------------------------------------------------------------------------------------------------------------
查询Oralce中某个用户有多少个存储过程
select distinct name from user_source where type='PROCEDURE';
select distinct name from dba_source where type='PROCEDURE' and owner='XG_TEST';
-------------------------------------------------------------------------------------------------------------
oracle的使用心得



1、DDL(Data Definition Language) Command
   create,alter,drop objects;
   grant,revoke privileges and roles;
   establishing auditing options;
   add comments to the data dictionary;
   Before and after each DDL statement,Oracle implicitly commit the current transactions.

2、DML(Data Manipulation Language) Command
   Query and Modify data within existing schema objects;
   DML statements consist of DELETE,INSERT,SELECT and UPDATE statements;
                        EXPLAIN PLAN statements;
                        LOCK TABLE statements;
   Unlike DDL Command , a commit is not implicit , after execute DDL Command ,must execute commit command    to commit a transaction;

3、Dynamic Performance Tables
   These tables are created at the instance startup and used to store information about the performance    of the instance. This information includes connection informatioion,I/OS, initialization parameter    values and so on..

4、Procedure and Function are identical except that Founction are always return a value(Procedure do not).

5、Schema is a collection of Objects that associated with the DataBase.

6、SGA is made up of :
   DataBase Buffers;
   Redo Log Buffers;
   The Shared Pool;

7、Transaction is a logical unit of work consisting of one or more SQL statements,ending in a commit or rollback.

8、 The DataBase
The Physical Layer
（1）One or more datafiles;
（2）Two or more redo log files;
（3）One or more control files;
The Logical Layer
（1）One or more tablespaces;
（2）The database schema;

9、The database is devided into one or more logical pieces known as tablespace;

10、Recommend that every one need DBA roles should have a different account , thus , if auditing is enabled , there is a record who made these system changes.

11、The Instance is the logical term that refers to the components necessary to access the data in a database.

12、数据库实例（也称为服务器Server），是用来访问一个数据库文件集的一个存储结构及后台进程的集合。Oralce并行服务器是指一个单独的数据库可以被多个实例访问。

13、查询实例名：
   select instance_name from v$instance；

14、查询动态视图v$waitstat、v$system_event、v$session_event、v$session_wait和v$ buffer_pool_statistics（在Oracle8中通过catperf.sql脚本创建）以获取下面所的统计信息，
目的是为了检查服务器进程是否正等待DBWR（对单个会话而言，也对整个数据库而言）。

15、
(1)SMON：系统监控程序
(2)PMON：进程监控程序
(3)DBWR：数据库写入程序
(4)LGWR：日志写入程序
(5)CKPT：检查点进程
(6)ARCH：归档日志
(7)RECO：恢复进程
(8)SNPn：快照进程
(9)LCKn：锁定进程
(10)Dnnn：调度程序进程
(11)Snnn：服务器进程
(12)Pnnn：并行查询服务器进程

16、数据库备份之前，若使用了shutdown abort命令，则需要进行如下操作，然后才能进行数据库备份：
1)    执行一个shutdown abort命令；
2)    启动数据库实例；
3)    执行shutdown命令；

17、使用OPS数据库时，如何解决两个服务器同时对同一记录的更新？
更新同一个表的数据的用户使用同一个实例来访问数据库。

18、通过ORACLE数据库对非ORACLE数据库进行访问，首先需要在运行非ORACLE数据库的服务器端安装ORACLE透明网关产品，每种被访问的数据引擎需要一个独立的网关；然后需要在本地ORACLE数据库中建立一个数据库连接（DATABASE LINK）。

19、外部文件访问：
1)    用作脚本文件的源代码写入SQL*PLUS、SQL、PL/SQL中；
2)    用作SQL*PLUS脚本文件的输出，用SPOOL命令生成；
3)    用作PL/SQL程序的输入或输出，通过UTL_FILE软件包访问；
4)    用作PL/SQL程序的脚本文件的输出，通过DBMS_OUTPUT软件包生成；
5)    用作通过BFILE数据类型在数据库中引用的外部数据，BFILE数据类型含有一个指向外部二进制数据文件的指针，用户必须通过CREATE DIRECTORY命令，在ORACLE中创建一个目录指针，指向存储文件的目录。
6)    用作通过DBMS_PIPE访问的外部程序，该程序必须以ORACLE支持的3GL来编写。

20、取消用户在SYSTEM表空间上创建对象的定额：
ALTER USER USER_NAME QUOTA 0 ON SYSTEM;
注：
如果一个用户被授权UNLIMITED_TABLESPACE系统权限或RESOURCE角色(Resouce角色拥有使用数据库中所有表空间的权限)，则这个授权将覆盖用户的任何定额设置。
21、创建一个用户，并且指定缺省表空间：
CREATE USER USER_NAME IDENTIFIED BY USER_PASSWORD DEFAULT TABLESPACE TABLESPACE_NAME;

22、重新指定用户的缺省表空间：
ALTER USER USER_NAME DEFAULT TABLESPACE TABLESPACE_NAME;

23、从数据表中分离出已有的索引：
ALTER INDEX INDEX_NAME REBUILD
   TABLESPACE INDEX_TABLESPACE
   STORAGE(INITIAL 2M NEXT 2M PCTINCREASE 0);

24、创建表时指定数据表空间和索引表空间：
CREATE TABLE TAB_NAME(
COLUMN_A TYPE,
COLUMN_B TYPE，
…
COLUM_N TYPE,
CONSTRAINT TAB_NAME_PK PRIMARY KEY (COLUMN_A)
USING INDEX TABLESPACE TABLESPACE_INDEXES
STORAGE(INITIAL 2M NEXT 2M PCTINCREASE 0))
TABLESPACE TABLESPACE_DATA
STORAGE(INITIAL 5M NEXT 5M PCTINCREASE 0);

25、TEMP表空间只有在大型排序操作时才使用；

26、STORAGE子句的意义：
Storage（initial 盘区大小 next 盘区大小 pctincrease 每个顺序盘区几何增长的系数）；
使用非零pctincrease参数的结果：
storage（initial 10M next 10M pctincrease 50）;
盘区号        大小        总块数        盘区容量注释
1            10            10            INITIAL
2            10            20            NEXT
3            15            35            NEXT×1.5
4            22.5            57.5            NEXT×1.5×1.5
5            33.75        91.25        NEXT×1.5×1.5×1.5
.. .. .. .. .. ..
理想的情况：
一个段只具有一个大小合适的盘区，并且next值较小，设表的pctincrease值为零；
实际上：
在表空间级设置pctincrease的值为零，会影响ORACLE自动合并表空间中自由空间的能力，把表空间缺省pctincrese设置为一个非常低的值，例如1；

27、通常称作TEMP的临时表空间，由于其自身的特点会有很多碎片，临时段总是在不断的创建、扩展和撤销，对于临时表，将INITIAL和NEXT盘区大小设为表空间大小的1/20到1/50，对于这个表空间，INITIAL和NEXT缺省设置应该相等，PCTINCREASE的值为0，这样，段将有同样大小的盘区构成，当撤消这些段时，下一个临时段将能够重新利用这些已撤消的盘区。

28、
1）将一个表空间改为临时表空间：
alter tablespace tablespace_name temporary;
2）将一个表空间转换为能存储永久对象：
alter tablespace tablespace_name permenent;
3）强制表空间合并其自由空间(只能合并位置相邻的自由盘区)：
alter tablespace talbespace_name coalesce;

29、
1)手工缩放数据文件(只能增大不能减小)：
alter database datafile '$path/datafile01.dat' resize nnnM;
2)创建一个在需要时自动扩展的文件：
CREATE TABLESPACE DATA
DATAFILE '$PATH/DATAFILE01.DAT' SIZE 200M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 250M;
3)通过ALTER TABLESPACE增加一个新的数据文件：
alter tablespace tablespace_name
add datafile '$path/datafile02.dat' size 200M
autoextend on
maxsize 300M;

40、移动数据文件：
1）    关闭实例；
2）    使用操作系统命令来移动数据文件；
3）    安装数据文件并使用ALTER DATABASE命令改变数据库中的文件名；
4）    启动实例；
具体步骤如下：
1)>svrmgrl
>connect internal
>shutdown
>exit
2)mv /db01/oracle/cc1/data01.dbf /db02/oracle/cc1/
3)>svrmgrl
>connect internal
>startup mount cc1
>alter database rename file '/db01/oracle/cc1/data01.dbf' to '/db02/oracle/cc1/data01.dbf';
4)    startup

31、
查看回滚段名称:v$rollname
查看表空间：dba_tablespace;
查看用户表空间：user_tablespaces;
查看回滚段状态信息：dba_rollback_segs;
查看数据库回滚段的当前分配情况：dba_segments;

32、
若系统中有多个表空间，就需要在system表空间中创建"第二回滚段"来支持多个表空间，有了"第二回滚段"，system表空间就只用于管理数据库级的事务。

33、
ipcs | grep oracle
ipcrm [-m|-s] ipcid(数字)

34、连接字符串：||   
select 'drop talbe '||table_name from user_tables;

35、视图中不能使用order by，但可以用group by 代替来达到排序目的：
create view as select b1,b2 from table_b group by b1,b2;

36、用户间复制数据:
copy from user1@databasex to user2@databasey create table2 using select * from talbe1;

37、察看数据库的大小，和空间使用情况
select b.file_id FileID,b.tablespace_name TableSpace,b.file_name PhysicalFileName,b.bytes TotalBytes,(b.bytes-sum(nvl(a.bytes,0))) UsedSpace,sum(nvl(a.bytes,0)) FreeSpace,sum(nvl(a.bytes,0))/(b.bytes)*100 FreePecent from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name;

38、Oracle提供了几个包，它们可以用来完成很多任务，从内部进程通信到文件I/O，到在PL/SQL块中动态创建和执行SQL语句。所有这些包由SYS用户所拥有-当Oracle最初安装时两个用户中的一个，这些包中最重要的包括：
DBMS_ALERT          不用轮询就允许应用命名并发出警告条件信号的过程与函数
DBMS_DDL          允许获取PL/SQL程序内部一定数量的DDL语句的过程
DBMS_DESCRIBE    为存储过程与函数描述API的过程
DBMS_JOB           管理BLOBs、CLOBs、NCLOBs与BFILEs的过程与函数
DBMS_OUTPUT        允许PL/SQL程序生成终端输出的过程与函数
DBMS_PIPE           允许数据库会话使用管道通信(通信频道)的过程与函数
DBMS_SQL           在PL/SQL程序内部执行动态SQL的过程与函数
DBMS_ULTILITY        DBMS_ULTILITY
ULT_FILE    允许PL/SQL程序读写服务器文件系统上的文本文件的过程与函数

39、如何解决单机监听不启动的问题：
你给IP固定一个值，然后配置NET8时最好用机器名，把listener.ora,tnsname.ora里的IP改成机器名。

40、查看日志文件的路径和数量：
select * from v$logfile;

41、oracle中的配置文件：
init.ora
tnsname.ora
listener.ora
sqlnet.ora

42、如何利用rownum 检索纪录：（在oracle中，只能通过rownum检索比rownum值小的所有的列）利用如下方法，可以检索表中rownum等于固定值的列：
select * from ( select rownum rn,column1,column2,… from table_name) where rn=要查询的值；

43、利用translate（char,from,to）函数判断一个字符串是否可以转换成 number 型：
translate(str,'x1234567890','x') is null，则str为纯字符串。
利用from to 参数，把str字段中所有的0-9的字符替换为空，然后判断函数返回值，返回值为空，则str一定可以转换成number型。

44、如何修改internal 用户的密码？
用法：orapwd file= password= entries=
参数解释：
　　　　file - name of password file (mand),
　　　　password - password for SYS and INTERNAL (mand),
　　　　entries - maximum number of distinct DBA and OPERs (opt),
　　　　There are no spaces around the equal-to (=) character.
1)进入DOS下
2)默认internal密码文件在c:\orant\database下，是隐藏属性，文件名称与数据库实例名有关
　 如默认ORACLE实例名为ORCL，则internal密码文件名为pwdorcl.ora
3)建立新的internal密码文件，起个新名字为pwdora8.ora
　 orapwd80 file=pwdora8.ora password=B entries=5　　　　　
--注：password项一定要用大写，并且不要用单引号
4)拷贝pwdora8.ora文件到c:\orant\database目录下
5)运行regedit，修改口令文件指向
6)找到HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE项
　 定位ORA_ORCL_PWFILE子项，改变其值为c:\orant\database\pwdora8.ora
7)关闭ORACLE数据库，重新启动
进入svrmgrl服务程序，测试internal密码是否更改成功

45、只有对象的拥有者才有对对象的操作权。如，把用户user1下的表tab_1赋给用户user2 查看的权限。此时，必须以uer1用户登陆，让后执行如下语句：
grant select on tab_1 to user2;

46、Oracle的本地进程    ps -aef | grep LOCAL=YES   的父进程ID不能为 1 ，若为 1 ，则该进程被掉死，需要用命令杀死改进程！！

47、在Windows 2000 server 操作系统下，用 net start 命令启动oracle的服务,如下：
net start OracleServiceORCL
net start OracleStartORCL
net start OracleTNSListener80
net start OracleWWWListener80
或者，利用 net stop 命令来终止服务：
net stop oraclestartorcl
net stop oracleserviceorcl
net stop oracletnslistener80
…
启动数据库的另外一个方法：
oradim -startup -sid SID
关闭数据库的另外一个方法：
oradim -shutdown -sid SID

48、多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。

49、保持Oracle数据库优良性能的若干诀窍：
1）    分区：
根据实际经验所得，在一个大数据库中，数据库空间的绝大多数是被少量的表所占有。如何简化大数据库和管理，如何改善应用的查询性能，一般可以使用分区这种手段。所谓分区就是动态地将表中记录分离到若干不同的表空间上，使数据在物理上被分割开来，便于维护、备份、恢复、事务及查询性能。当使用的时候可建立一个连接所有分区的视图，使其在逻辑上仍以一个整体出现。
(1)建立分区表
Create table Employee (
EmpNo varchar2(10) primary key,
Name varchar2(30),
DeptNo Number(2)
)
Partition by range(DeptNo)
(
partition PART1 values less than (11)
tablespace PART1_TS,
partition PART2 values less than (21)
tablespace PART2_TS,
partition PART3 values less than (31)
tablespace PART3_TS
partition PART4 values less than (MAXVALUE)
tablespace PART4_TS
);
表Employee依据DeptNo列进行分区。
(2)分区索引
Create index Employee_DeptNo on Employee(DeptNo)
local (
partition PART1 tablespace PART1_NDX_TS,
partition PART2 tablespace PART2_NDX_TS,
partition PART3 tablespace PART3_NDX_TS,
partition PART4 tablespace PART4_NDX_TS,
);
当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引，如：
Create index Employee_DeptNo on Employee(DeptNo)
global partition by range (DeptNo)
(
partition PART1 values less than (11)
tablespace PART1_NDX_TS,
partition PART2 values less than (21)
tablespace PART2_NDX_TS,
partition PART3 values less than (31)
tablespace PART3_NDX_TS
partition PART4 values less than (MAXVALUE)
tablespace PART4_NDX_TS
);
在建立全局索引时，global子句允许指定索引的范围值，这个范围值可以不同于表分区的范围值。只有建立局部索引才会使索引索引分区与表分区间建立起一一对应关系。因此，在大多数情况下，应该使用局部索引分区。若使用了此索引，分区就能够很容易地将索引分区与表分区建立关联，局部索引比全局索引更易于管理。
(3)分区管理
根据实际需要，还可以使用 alter table 命令来增加、丢弃、交换、移动、修改、重命名、划分、截短一个已存在分区的结构。

2)Rebuild Indexes（重建索引不会影响存储过程）
如果表中记录频繁的被删除或插入，尽管表中的记录总量保持不变，索引空间的使用量会不断增加。虽然记录从索引中被删除，但是该记录索引项的使用空间不能被重新使用。因此，如果表变化不定，索引空间量会不断增加，不管表中记录数量是否增加--只仅仅是因为索引中无效空间量的增加。
要回收那些曾被删除记录使用的空间，需要使用alter index rebuild 命令。可以做一个定期运行的批处理程序，来重建最活动表的索引。这个批处理程序可以在空闲时运行，以避免程序与用户冲突。若能坚持索引的这一程序规划，便可以及时回收那些未使用空间，提高空间利用率。

3)段的碎片整理
当生成一个数据库对象时（一个表或一个索引），通过用户缺省值或指定值来为它指定表空间。一个在表空间中所生成的段，用于存储对象的相关数据。在段被关闭、收缩、截断之前，段所分配的空间将不被释放。
一个段是由范围组成，而范围是由相邻的Oracle块组成。一旦存在的范围不能再存储新的数据，那这个段就会去获得新的范围，且并不要求这些范围是彼此相邻的。这样的扩展会一直继续下去，直到表空间中的数据文件不能提供更多的自由空间，或者范围数量已达到极限。
因此，一个碎片太多的数据段，不仅会影响运行，也会引发表空间中的空间管理问题。所以，每个数据段只含有一个范围是十分有益的。借助监控系统，可以通过检查DBA_SEGMENTS数据字典视图来了解哪些数据库对象含有10个或更多范围的段，确定其数据段碎片。
若一个段的碎片过多，可用两种方法解决这个问题：
(1)用正确的存储参数建立一个新表，将旧表中的数据插入到新表中，再删除旧表；
(2)利用Export/Import工具。
如：exp system/manager file=exp.dmp compress=Y grants=Y indexes=Y tables=(T1,T2)
若输出成功，进入Oracle，删除上述表。
注：compress=Y决定将在输出过程中修改它们的存储参数。
imp system/manager file=exp.dmp commit=Y buffer=64000 full=Y
注：在输入时重新配置新的存储参数。

自由范围的碎片整理
表空间中的一个自由范围是表空间中相连自由（空间）块的集合。当一个段关闭时，它的范围将被释放，并被标记为自由范围。然而，这些自由范围再也不能与相邻的自由范围合并，它们之间的界线始终存在。但是当表空间的缺省值pctincrease设置不为0时，SMON后台进会定期的将这些相邻的自由范围合并。若pctincrease设置为0，那相邻自由范围不会被数据库自动合并。但可以使用 alter tablespace 命令coalesce选项，来强迫进行相邻自由范围的合并。
不进行自由范围合并，在日后的空间请求中，会影响到表空间中的空间分配。当需要一个足够大的范围时，数据库并不会合并相邻的自由范围，除非没有其他选择。这样，当表空间中前面较小自由范围已被相关使用时，将使用表空间中后面部分最大的一个自由范围。结果，会因为它们没有足够多的使用空间，从而导致表空间中速度上的矛盾。由于这样的进程出现，使数据库的空间分配距理想越来越远。自由空间碎片常会出现在那些经常关闭又重新生成的数据库表和索引中。
在理想的ORACLE表空间中，每一个数据库对象存储在一个单独的范围中，并且所有有效自由空间集中在一个巨大而连续的范围中。这样，在一个对象需要附加存储空间时，可以在增加获取足够大自由空间的可能性同时，最小化空间中的循环调用，提高自由空间使用率

50、查看和修改Oracle服务器端字符集：
方法一：
1)    查看服务器端字符集：
select * from v$nls_parameters;
select * from nls_database_parameters;
select * from sys.props$;
2)    修改服务器端字符集(用sys用户)：
首先执行：update props$ set value$ = 'WE8ISO8859P1' where name ='NLS_CHARACTERSET';
update props$ set value$ = 'china' where name ='NLS_LANGUAGE';
提交（commit），然后，重新启动数据库；
3）用客户端工具（PL/SQL DEVELOP or PB etc.）查询数据库，若显示乱码，先查询出数据库端的字符集，然后，从注册表中修改NLS_LANG字段的值，可能为AMERICAN_AMERICA.WE8ISO8859P1或者SIMPLIFIED CHINESE_CHINA.ZHS16GBK或者NA等。
方法二：
alter system enable restricted session;
alter database ORCL character set ZHS16GBK;
alter database ORCL national character set ZHS16GBK;

51、查看系统中的角色：
select * from dba_roles;
-------------------------------------------------------------------------------------------------------------
Oracle中查看所有的表
select   table_name   from   user_tables;     //当前用户的表  
   
  select   table_name   from   all_tables;     //所有用户的表  
   
  select   table_name   from   dba_tables;       //包括系统表
-------------------------------------------------------------------------------------------------------------