1、查看表空間的名稱及大小
????
select
?t.tablespace_name,?
round
(
sum
(bytes
/
(
1024
*
1024
)),
0
)?ts_size
????
from
?dba_tablespaces?t,?dba_data_files?d
????
where
?t.tablespace_name?
=
?d.tablespace_name
????
group
?
by
?t.tablespace_name;
2、查看表空間物理文件的名稱及大小
??
select
?tablespace_name,?
file_id
,?
file_name
,
??
round
(bytes
/
(
1024
*
1024
),
0
)?total_space
??
from
?dba_data_files
??
order
?
by
?tablespace_name;
3、查看回滾段名稱及大小
??
select
?segment_name,?tablespace_name,?r.status,
??(initial_extent
/
1024
)?InitialExtent,(next_extent
/
1024
)?NextExtent,
??max_extents,?v.curext?CurExtent
??
From
?dba_rollback_segs?r,?v$rollstat?v
??
Where
?r.segment_id?
=
?v.usn(
+
)
??
order
?
by
?segment_name?;
4、查看控制文件?
??
select
?name?
from
?v$controlfile;
?5、查看日志文件?
??
select
?member?
from
?v$logfile;
?6、查看表空間的使用情況?
??
select
?
sum
(bytes)
/
(
1024
*
1024
)?
as
?free_space,tablespace_name
??
from
?dba_free_space
??
group
?
by
?tablespace_name;
??
SELECT
?A.TABLESPACE_NAME,A.BYTES?TOTAL,B.BYTES?USED,?C.BYTES?FREE,
??(B.BYTES
*
100
)
/
A.BYTES?"
%
?USED",(C.BYTES
*
100
)
/
A.BYTES?"
%
?FREE"
??
FROM
?SYS.SM$TS_AVAIL?A,SYS.SM$TS_USED?B,SYS.SM$TS_FREE?C
??
WHERE
?A.TABLESPACE_NAME
=
B.TABLESPACE_NAME?
AND
?A.TABLESPACE_NAME
=
C.TABLESPACE_NAME;
7、查看數據庫庫對象?
select
?owner,?object_type,?status,?
count
(
*
)?
count
#?
from
?all_objects?
group
?
by
?owner,?object_type,?status;
8、查看數據庫的版本 ?
??
Select
?version?
FROM
?Product_component_version
??
Where
?SUBSTR(PRODUCT,
1
,
6
)
=
'
Oracle
'
;
9、查看數據庫的創建日期和歸檔方式?
??
Select
?Created,?Log_Mode,?Log_Mode?
From
?V$
Database
;
?10、查看當前所有對象??
?
select
?
*
?
from
?tab;
?11、建一個和a表結構一樣的空表??
?
create
?
table
?b?
as
?
select
?
*
?
from
?a?
where
?
1
=
2
;?
?
create
?
table
?b(b1,b2,b3)?
as
?
select
?a1,a2,a3?
from
?a?
where
?
1
=
2;
?12、察看數據庫的大小，和空間使用情況??
????? ?col?tablespace?format?a20
????? ?select?b.file_id --文件ID,
b.tablespace_name --表空間,
b.file_name --物理文件名,
b.bytes --總字節數,
(b.bytes-sum(nvl(a.bytes,0))) --已使用,
sum(nvl(a.bytes,0)) --剩余,
sum(nvl(a.bytes,0))/(b.bytes)*100 --剩余百分比
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
/
dba_free_space?--表空間剩余空間狀況
dba_data_files?--數據文件空間占用情況
13、查看現有回滾段及其狀態??
col?segment?format?a30
SELECT
?SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,
FILE_ID
,STATUS?
FROM
?DBA_ROLLBACK_SEGS;
?14、查看數據文件放置的路徑??
?col?
file_name
?format?a50
?
select
?tablespace_name,
file_id
,bytes
/
1024
/
1024
,
file_name
?
from
?dba_data_files?
order
?
by
?
file_id
;
?15、顯示當前連接用戶??
show?
user
?16、把SQL*Plus當計算器??
select
?
100
*
20
?
from
?dual;
?17、連接字符串??
?
select
?列1
||
列2?
from
?表1;
?
select
?concat(列1,列2)?
from
?表1;
?18、查詢當前日期??
?
select
?to_char(sysdate,
'
yyyy-mm-dd,hh24:mi:ss
'
)?
from
?dual;
?19、用戶間復制數據??
?copy?
from
?user1?
to
?user2?
create
?table2?using?
select
?
*
?
from
?table1;
?20、視圖中不能使用order by，但可用group by代替來達到排序目的??
?
create
?
view
?a?
as
?
select
?b1,b2?
from
?b?
group
?
by
?b1,b2;
?21、通過授權的方式來創建用戶??
?
grant
?connect,resource?
to
?test?identified?
by
?test;?
?conn?test
/
test;
|
|