在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;       //包括系統表
-------------------------------------------------------------------------------------------------------------