查看表結(jié)構(gòu)
desc tablename
行數(shù)
rownum
查詢用戶執(zhí)行過哪些sql操作
select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('WYZ') order byt.LAST_ACTIVE_TIME desc
鎖表
LOCK TABLE table1,table2,table3 IN ROW EXCLUSIVE MODE;
十進(jìn)制十六進(jìn)制轉(zhuǎn)換
to_char(1212,'xxxx'),to_number('4bc','xxx') from dual
查看表大小
有兩種含義的表大小：一種是分配給一個(gè)表的物理空間數(shù)量，而不管空間是否被使用?？梢赃@樣查詢獲得字節(jié)數(shù)：
select segment_name, bytes
from user_segments
where segment_type = 'TABLE';
或者
Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group BySegment_Name
另一種表實(shí)際使用的空間。這樣查詢：
analyze table AREAINFOcompute statistics;
select??TABLE_NAME,TABLESPACE_NAME, NUM_ROWS ,AVG_ROW_LEN,? NUM_ROWS*AVG_ROW_LEN??
from user_tables
where table_name = 'AREAINFO';
說明：
表名稱要大寫，紅色加粗部分。
查看每個(gè)表空間的大小
Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group ByTablespace_Name
快速做表備份
createtable table_name as select *? from table;
這個(gè)是創(chuàng)建和table表一樣的表tablke_name，包含原table表中的數(shù)據(jù)信息；
createtable table_name as select * from table where 1 = 2;
這個(gè)是創(chuàng)建和table表一樣的表tablke_name，包不包含原table表中的數(shù)據(jù)信息，即為一張空表。
計(jì)算一個(gè)表占用的空間的大小? ???????????????????????????????????
selectowner,table_name,NUM_FREELIST_BLOCKS,LAST_ANALYZED,BLOCKS*AAA/1024/1024"Size M" from dba_tables?? wheretable_name='XXX';???????????????????????????
Here：AAA is the value of db_block_size;
XXX is the table name you want to check
?
或者
selectsum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
查詢數(shù)據(jù)庫(kù)有多少表
SQL>select * from all_tables；
SQL>select count(0) from all_tables;
? COUNT(0)
----------
????? 1331
SQL>
查詢表中主鍵信息
selectcu.* from user_cons_columns cu, user_constraints au
where
??? cu.constraint_name =au.constraint_name?
and
?? au.constraint_type = 'P' andau.table_name ='RPT_DELAYTIME_20100828';
查詢表的所有索引
selectt.*,i.index_type
from user_ind_columns t,user_indexes i
where
????? t.index_name = i.index_name
and??
????? t.table_name = i.table_name
and
????? t.table_name='RPT_DELAYTIME_20100828';
查詢表的唯一性約束
selectcolumn_name from user_cons_columns cu, user_constraints au
where
?cu.constraint_name = au.constraint_name
and
au.constraint_type = 'U'
and
au.table_name = 'RPT_DELAYTIME_20100828';
查找表的外鍵
selectc.* from user_constraints c
where
???? c.constraint_type = 'R'
and
c.table_name= 'RPT_DELAYTIME_20100828';
外鍵約束的列名：
select cl.* from user_cons_columns cl where
cl.constraint_name = 外鍵名稱
引用表的鍵的列名：
select
cl.* from user_cons_columns cl where cl.constraint_name = 外鍵引用表的鍵名
停止外鍵語(yǔ)句
altertable T_BME_TASK disable constraints FK_TASKDEFINITION_TASKDEFID;
altertable T_BME_TASKRUNRESULT disable constraints FK_TASKRUNRESULT_TASKID;
altertable T_BME_TASKNOTIFYINFO disable constraints FK_TASKNOTIFYINFO_TASKID;
啟用外鍵語(yǔ)句
altertable T_BME_TASK enable constraints FK_TASKDEFINITION_TASKDEFID;
altertable T_BME_TASKRUNRESULT enable constraints FK_TASKRUNRESULT_TASKID;
altertable T_BME_TASKNOTIFYINFO enable constraints FK_TASKNOTIFYINFO_TASKID;
查詢表的所有列及其屬性
selectt.*,c.COMMENTS from user_tab_columns t,user_col_comments c
?where
t.table_name= c.table_name
and
t.column_name= c.column_name
and
t.table_name=? 'RPT_DELAYTIME_20100828';
修改表名
SQL>alter table old_table_name rename to new_table_name;
查詢/搜索出前N條記錄
select * fromtable_name where rownum
select* from systemparameter where rownum <30
如何獲得某張表對(duì)應(yīng)的表空間信息
oracle@mmsg:~>sqlplus mmsg/mmsg@mmsgdb?? //應(yīng)用級(jí)用戶登錄oracle數(shù)據(jù)庫(kù)
SQL*Plus:Release11.1.0.7.0 - Production on星期四7月1 17:34:15 2010
Copyright(c) 1982, 2008, Oracle.? All rightsreserved.
連接到:
OracleDatabase11gEnterprise Edition Release11.1.0.7.0 - 64bitProduction
Withthe Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>select tablespace_name from user_tables where
table_name like 'VPNCORP_30%';
TABLESPACE_NAME
------------------------------------------------------------
MMSG
SQL>
oracle如何區(qū)分 64-bit/32bit 版本？
oracle@linux:~>sqlplus / as sysdba
SQL*Plus:Release11.1.0.7.0 - Production on星期四7月1 17:48:20 2010
Copyright(c) 1982, 2008, Oracle.? All rightsreserved.
連接到:
OracleDatabase11gEnterprise Edition Release11.1.0.7.0 -64bit Production
Withthe Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from
v$version;
BANNER
--------------------------------------------------------------------------------
OracleDatabase11gEnterprise Edition Release11.1.0.7.0 -64bit Production
PL/SQLRelease11.1.0.7.0 - Production
CORE??? 11.1.0.7.0????? Production
TNSfor Linux: Version11.1.0.7.0 -Production
NLSRTLVersion11.1.0.7.0 - Production
SQL>
分辨某個(gè)用戶是從哪臺(tái)機(jī)器登陸ORACLE的
SQL>SELECT machine,terminal FROM V$SESSION;
MACHINE?????????????????????????????????????????????????????????TERMINAL
----------------------------------------------------------------------------------------------
linux????????????? ??????????????????????????????????????????????pts/2
linux???????????????????????????????????????????????????????????pts/2
linux???????????????????????????????????????????????????????????pts/1
linux???????????????????????????????????????????????????????????pts/1
linux???????????????????????????????????????????????????????????pts/1
linux???????????????????????????????????????????????????????????pts/2
linux???????????????????????????????????????????????????????????pts/1
linux???????????????????????????? ???????????????????????????????pts/1
linux???????????????????????????????????????????????????????????pts/1
已選擇9行。
SQL>
查看最大會(huì)話數(shù)
SQL>select * from v$parameter where name like 'proc%';
SQL>show parameter processes
NAME???????????????????????????????? TYPE??????? VALUE
----------------------------------------------- ------------------------------
aq_tm_processes????????????????????? integer???? 0
db_writer_processes????????????????? integer???? 1
gcs_server_processes???????????????? integer???? 0
global_txn_processes???????????????? integer???? 1
job_queue_processes????????????????? integer???? 1000
log_archive_max_processes??????????? integer???? 4
processes??????????????????????????? integer???? 1000
SQL>
SQL>select * from v$license;?
SESSIONS_MAXSESSIONS_WARNING SESSIONS_CURRENTSESSIONS_HIGHWATER? USERS_MAX CPU_COUNT_CURRENTCPU_CORE_COUNT_CURRENT CPU_SOCKET_COUNT_CURRENT
---------------------------- ---------------- ------------------ --------------------------- ---------------------- ------------------------
CPU_COUNT_HIGHWATERCPU_CORE_COUNT_HIGHWATER CPU_SOCKET_COUNT_HIGHWATER
------------------------------------------- --------------------------
?????????? 0??????????????? 0?????????????? 83??????????????? 482????????? 0???????????????? 8????????????????????? 8?????????????????? 2
????????????????? 8??????????????????????? 8????????????????????????? 2
SQL>
其中sessions_highwater紀(jì)錄曾經(jīng)到達(dá)的最大會(huì)話數(shù)
session數(shù)，session=processe*1.1 + 5
查看系統(tǒng)被鎖的事務(wù)時(shí)間
SQL>select * from v$locked_object;
未選定行
SQL>
查得數(shù)據(jù)庫(kù)的SID
SQL>select name from v$database;
NAME
------------------
MMSGDB
SQL>
獲取SQL語(yǔ)句執(zhí)行耗時(shí)時(shí)間
SQL>set timing on
SQL>select instance_number,instance_name,status from v$instance;
INSTANCE_NUMBERINSTANCE_NAME??????????????????? STATUS
----------------------------------------------- ------------------------
????????????? 1 mmsgdb?????????????????????????? OPEN
已用時(shí)間:? 00: 00: 00.00
SQL>
將查詢（select）的結(jié)果導(dǎo)入到一個(gè)文件中
oracle@mmsg:~>sqlplus / as sysdba
SQL*Plus:Release11.1.0.7.0 - Production on星期五7月2 16:55:52 2010
Copyright(c) 1982, 2008, Oracle.? All rightsreserved.
連接到:
OracleDatabase11gEnterprise EditionRelease11.1.0.7.0 - 64bit Production
Withthe Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>spool test.txt
SQL>select sessions_current,sessions_highwater from v$license;
SESSIONS_CURRENTSESSIONS_HIGHWATER
----------------------------------
????????????? 38???????????????? 53
SQL>select instance_number,instance_name,status from v$instance;
INSTANCE_NUMBERINSTANCE_NAME??????????????????? STATUS
----------------------------------------------- ------------------------
????????????? 1 mmsgdb?????????????????????????? OPEN
SQL>show parameter spfile
NAME???????????????????????????????? TYPE
----------------------------------------------------------
VALUE
------------------------------
spfile?????????????????????????????? string
/opt/oracle/product/11g/dbs/sp
filemmsgdb.ora
SQL>show parameter license
NAME???????????????????????????????? TYPE
----------------------------------------------------------
VALUE
------------------------------
license_max_sessions???????????????? integer
0
license_max_users??????????????????? integer
0
license_sessions_warning???????????? integer
0
SQL>quit?
從Oracle Database11gEnterprise Edition Release11.1.0.7.0 - 64bitProduction
With
the Partitioning, OLAP, Data Mining and Real Application Testing options 斷開
oracle@mmsg:~>more test.txt
SQL>select sessions_current,sessions_highwater from v$license;
SESSIONS_CURRENTSESSIONS_HIGHWATER????????????????????????????????????????????
----------------------------------????????????????????????????????????????????
????????????? 38???????????????? 53????????????????????????????????????????????
SQL>select instance_number,instance_name,status from v$instance;
INSTANCE_NUMBERINSTANCE_NAME???????????????????STATUS????????????????????????
----------------------------------------------- ------------------------??????
????????????? 1 mmsgdb?????????????????????????? OPEN??????????????????????????
SQL>show parameter spfile
NAME???????????????????????????????? TYPE??????????????????????????????????????
----------------------------------------------------------????????????????????
VALUE??????????????????????????????????????????????????????????????????????????
------------------------------?????????????????????????????????????????????????
spfile?????? ????????????????????????string????????????????????????????????????
/opt/oracle/product/11g/dbs/sp?????????????????????????????????????????????????
filemmsgdb.ora?????????????????????????????????????????????????????????????????
SQL>show parameter license
NAME???????????????????????????????? TYPE??????????????????????????????????????
----------------------------------------------------------????????????????????
VALUE??????????????????????????????????????????????????????????????????????????
------------------------------?????????????????????????????????????????????????
license_max_sessions???????????????? integer???????????????????????????????????
0??????????????????????????????????????????????????????????????????????????????
license_max_users??????????????????? integer???????????????????????????????????
0??????????????????????????????????????????????????????????????????????????????
license_sessions_warning???????????? integer???????????????????????????????????
0???????????????????????? ??????????????????????????????????????????????????????
SQL>quit
注：
?? 相當(dāng)于邊操作邊記錄操作信息，并將信息追加到指定文件中，指定的文件路徑可設(shè)置。
查詢重復(fù)記錄
selectcount(*),ACCOUNTKEY,APPLYTIME? fromuserdb.account
groupby ACCOUNTKEY,APPLYTIME
havingcount(*)>1
刪除重復(fù)記錄
deletefrom userdb.account t1 where t1.id !=
(selectmax(id) from userdb.account t2 where t1.ACCOUNTKEY=t2.ACCOUNTKEY andt1.APPLYTIME=t2.APPLYTIME)
字符串里加回車
select'Welcome? to visit'||chr(10)||'www.CSDN.NET' from dual
使select語(yǔ)句使查詢結(jié)果自動(dòng)生成序號(hào)
select
rownum，COL from table;
插入全年日期
create
table BSYEAR (d date)；??????????????????
insert into BSYEAR????????????????????????????????????????
select to_date('20030101'，'yyyy mmdd')+rownum-1
from all_objects????????????????????????????????????????????
where rownum <=
to_char(to_date('20031231'，'yyyymmdd')，'ddd')；
觸發(fā)器
查詢當(dāng)前觸發(fā)器
SQL>set wrap off
SQL>col status format a15
SQL>col OBJECT_NAME format a20
SQL>Select object_name,status? Fromuser_objects Where object_type='TRIGGER';
禁止、恢復(fù)觸發(fā)器
禁止觸發(fā)器
altertable accounttype disable all triggers;
恢復(fù)觸發(fā)器
altertable accounttype enable all triggers;
查詢當(dāng)前用戶下所有視圖
SQL> Select object_name From user_objects Where
object_type='VIEW';
查詢當(dāng)前用戶下所有存儲(chǔ)過程
Selectobject_name? From user_objects Where object_type='PROCEDURE'
查詢job
查詢所有job
SQL>col LOG_USER format a10
SQL>col PRIV_USER format a10
SQL>col SCHEMA_USER format a10
SQL>select job, LOG_USER,SCHEMA_USER,PRIV_USER from dba_jobs;
或者從user_jobs中獲取數(shù)據(jù)。
查詢當(dāng)天跑的job
select* from all_jobs where last_date>=trunc(sysdate)
查詢某一job執(zhí)行了多少小時(shí)
select? total_time/1000/60/60? from user_jobs
查詢function
select object_name from user_objects? where object_type='FUNCTION';
查詢sequence
SELECTOBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='FUNCTION'
查詢oracle package內(nèi)容
SQL>desc all_source
Name?????????????????????????????????????Null???? Type
------------------------------------------------- ----------------------------
OWNER?????????????????????????????????????????????VARCHAR2(30)
NAME??????????????????????????????????????????????VARCHAR2(30)
TYPE??????????????????????????????????????????????VARCHAR2(12)
LINE?????????????????????????????? ????????????????NUMBER
TEXT??????????????????????????????????????????????VARCHAR2(4000)
SQL>selecttext from all_source where name='DBMS_OUTPUT' and type='PACKAGE'
?
