查看表結(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

查看表大小

有兩種含義的表大泄敌鳌：一種是分配給一個表的物理空間數(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';

說明：

表名稱要大寫，紅色加粗部分隶糕。

查看每個表空間的大小

Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group ByTablespace_Name

快速做表備份

createtable table_name as select *? from table;

這個是創(chuàng)建和table表一樣的表tablke_name瓷产，包含原table表中的數(shù)據(jù)信息；

createtable table_name as select * from table where 1 = 2;

這個是創(chuàng)建和table表一樣的表tablke_name枚驻，包不包含原table表中的數(shù)據(jù)信息濒旦，即為一張空表。

計(jì)算一個表占用的空間的大小? ???????????????????????????????????

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ù)庫有多少表

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 = 外鍵引用表的鍵名

停止外鍵語句

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;

啟用外鍵語句

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

如何獲得某張表對應(yīng)的表空間信息

oracle@mmsg:~>sqlplus mmsg/mmsg@mmsgdb?? //應(yīng)用級用戶登錄oracle數(shù)據(jù)庫

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>

分辨某個用戶是從哪臺機(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>

查看最大會話數(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á)的最大會話數(shù)

session數(shù)锉矢，session=processe*1.1 + 5

查看系統(tǒng)被鎖的事務(wù)時(shí)間

SQL>select * from v$locked_object;

未選定行

SQL>

查得數(shù)據(jù)庫的SID

SQL>select name from v$database;

NAME

------------------

MMSGDB

SQL>

獲取SQL語句執(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)入到一個文件中

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語句使查詢結(jié)果自動生成序號

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)前用戶下所有存儲過程

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'

?