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 f.tablespace_name 表空间名,curG-file_freeG 实际使用G,curG 磁盘占用G,maxG 最大空间G,ext_freeG+file_freeG 剩余空间G
from
(select tablespace_name,round(sum(bytes/(1024*1024*1024)),2) as file_freeG
from dba_free_space group by tablespace_name) f
join
(select tablespace_name,round(sum(bytes/(1024*1024*1024)),2) as curG,
round(sum(maxbytes/(1024*1024*1024)),2) as maxG,round(sum(maxbytes-bytes)/(1024*1024*1024),2) as ext_freeG
from dba_data_files group by tablespace_name) d
on f.tablespace_name=d.tablespace_name;
--显示数据文件大小、最大大小、剩余百分比
select tablespace_name,file_name,file_id,
round(bytes/(1024*1024),0) as 当前占用M,round(maxbytes/(1024*1024),0) 最大空间M,
round((maxbytes-bytes)/(1024*1024),0) as 剩余空间M,
to_char(100*(maxbytes-bytes)/DECODE(maxbytes,0,maxbytes-bytes,maxbytes), '999.99')||'%' as 剩余百分比
from dba_data_files order by tablespace_name,file_id
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';
select * from v$version;
9、查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
10、查看当前所有对象
SQL> select * from tab;
11、建一个和a表结构一样的空表
SQL> create table b as select * from a where 1=2;
SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;
12、察看数据库的大小，和空间使用情况
SQL> col tablespace format a20
SQL> 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、查看现有回滚段及其状态
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;
14、查看数据文件放置的路径
SQL> col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
15、显示当前连接用户
SQL> show user
16、把SQL*Plus当计算器
SQL> select 100*20 from dual;
17、连接字符串
SQL> select 列1||列2 from 表1;
SQL> select concat(列1,列2) from 表1;
18、查询当前日期
SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
19、用户间复制数据
SQL> copy from user1 to user2 create table2 using select * from table1;
20、视图中不能使用order by，但可用group by代替来达到排序目的
SQL> create view a as select b1,b2 from b group by b1,b2;
21、通过授权的方式来创建用户
SQL> grant connect,resource to test identified by test;
SQL> conn test/test
create user SXK identified by ncsxk default tablespace ncbdcsxk;
grant connect,create session,create table,select any table to sxk;
22、How to find the tablespace of a table?
SELECT tablespace_name
FROM all_tables
WHERE table_name = 'YOURTABLENAME';
23、How to remove duplicate rows from a table
If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:
DELETE FROM tablename
WHERE rowid not in (SELECT MIN(rowid)
FROM tablename
GROUP BY column1, column2, column3...);
Here column1, column2, column3 constitute the identifying key for each record.
If the keys cannot be identified for the table, you may create a temporary table using the query
CREATE TABLE temptablename
AS SELECT DISTINCT *
FROM tablename;
Then drop the original table and rename the temp table to original tablename.
24、检查无效的数据库对象
col owner for a20
col object_name for a30
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status= 'INVALID';
25、检查不起作用的约束
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status = 'DISABLED' AND constraint_type = 'P' ;
27、检查无效的trigger
SELECT owner, trigger_name, table_name, status
FROM dba_triggers
WHERE status = 'DISABLED';
28、密码有效期的查看、修改
--查看概要文件名
select username,profile from dba_users where username='user';
--检查概要文件（默认为default）的密码有效期设置
select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
--将密码有效期修改成无限制
alter profile default limit password_life_time unlimited;
--检查“RESOURCE_LIMIT”是否配置为“FLASE”
show parameter resource_limit;
alter system set resource_limit=false scope=both;
--修改有效期后，还没有被提示ORA-28002警告的用户账号不会再碰到同样的提示，而已经被提示的用户账号必须再改一次密码
alter user user_name identified by account unlock; --不用换密码
29、DOS链接提示"无效选项"的处理
$sqlplus /nolog
sql>conn / as sysdba 或 sql>conn user_name/psw as sysdba
30、数据库密码如果忘了怎么办
开始-->运行-->cmd
输入 ：sqlplus /nolog 回车
输入 ：connect / as sysdba 回车
用户解锁 : alter user system account unlock 回车
修改密码：alter user system identified by manager
31、资源文件
--查看RESOURCE_LIMIT值
show parameter resource_limit;
--启动当前资源限制
alter system set RESOURCE_LIMIT=true;
--创建资源配置(时间单位：分钟)
create profile [profilename] limit connect_time unlimited idle_time unlimited;
--把某种配置文件赋给某个用户
alter user [user_name] profile [profilename];
--查看用户使用了哪个资源文件
select username,profile from dba_users;
--查看资源文件设置的权限
select profile,resource_name,limit from dba_profiles;
32、断开用户连接
--查看哪些用户连接了数据库
select sid,serial#,username from v$session;
--断开用户的连接
alter system kill session '118,1394'; --'118,1394' : 'sid,serial#'
33、查看数据库服务器连接参数
--专用服务器连接(dedicated server)，共享服务器连接(shared server)
--如果VALUE_COL_PLUS_SHOW_PARAM大于0为()dedicated模式
show parameter shared_server mts_servers；
34、查看数据库名及其他属性
select name from v$database;
show parameter db
查看参数文件。
35、密码过期的处理
--查询该用户所使用的profile，如果没有特别设置，一般会是default：
SELECT username,PROFILE FROM dba_users;
--查询default的profile设置是否有密码限制，发现密码生命期限为180天：
SELECT * FROM dba_profiles WHERE resource_name='PASSWORD_LIFE_TIME';
--修改default的profile密码策略为无限制：
alter profile default limit password_life_time unlimited;
--策略已更改，但如果修改profile时候已经有该提示的用户还是需要修改密码，保持原密码即可，无提示的用户将不会再提示
alter user username identified by xxx;
--密码的历史，password_reuse_time=30,password_reuse_max=10，用户可以在30天以后重用该密码，要求密码必须被改变超过10次。
create profile p1 limit password_reuse_time 1/1440 password_reuse_max 1;
alter user scott profile p1;
--5分钟后可重用该密码，但这期间必须要被改成其他密码一次
alter profile p1 limit password_reuse_time 5/1440 password_reuse_max 1;
36、查找表中字段重复的数据
--count函数不能与列同时使用，但加group by后就可以了。分组后找出组中多余一条记录的，然后作为查询条件。
select * from bdc_fwsx where 不动产单元号 in (select 不动产单元号 from (
select count(1) as a,不动产单元号 from bdc_fwsx where 状态=1 group by 不动产单元号)
where a>1) and 状态=1 order by 不动产单元号
37、查询对象占用空间大小
--查询段占用的空间
select bytes from user_segments where segment_name='tablename';
--查询段剩余空间和已用空间
analyze table tablename compute statistics;
select empty_blocks,blocks from user_table where table_name='tablename';
38、查询系统是以pfile还是spfile启动
--如果isspecified里有true，表明用spfile进行了指定配置
--如果全为false，则表明用pfile启动
select isspecified,count(*) from v$spparameter group by isspecified;
select decode(count(*),1,'spfile','pfile') from v$spparameter
where rownum=1 and isspecified='TRUE';
39、DBLink的创建、删除、查询
--跨库查询数据时可以建立数据库连接，create后不加public，则创建的dblink只有创建者可以使用
create [public] database link link_name connect to user_name identified by user_pwd using 'server_name';
drop database link link_name;
--使用时，只要在表名后加“@link_name”即可。
--应避免使用connect by, start with, prior关键字。BLOB字段不可用。
--当本地global_names为TRUE时，link_name必须与远程的global_name一致
show parameter global_name;
select * from dba_db_links;
alter system set global_names=FALSE;
--如果创建连接后还是无法查询，可以试一下把server_name换成连接字符串。
create [public] database link link_name connect to user_name identified by user_pwd using
'(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 17.16.27.251)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = GHY)
)
)';
--查看dblink详细信息，如是否在使用中
select * from v$dblink;
40、查看数据库版本
select * from v$version;
41、移动表或表分区
--移动表的语法
Alter table tablename move [Tablespace new_name Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging];
--移动分区的语法
alter table tablename move (partition partname) [update global indexes];
--之后之后必须重建索引
Alter index indexname rebuild;
--如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，
--可以采用如下的方法移动Lob段
alter table tablename move lob(lobsegname) store as (tablespace newts);
42、查看数据文件是否被存储占用
--如果找不到记录，说明数据文件没有存储任何数据
select segment_name,file_id,blocks from dba_extents where file_id=5;
43、删除表空间的空数据文件
--首先查询到数据文件的file_id(dba_data_files)，然后查看数据文件是否是空数据文件(dba_extents)
alter tablespace tbs_name drop datafile 'datafile_path_name.dbf';
44、不配置网络，直接连接
--启动cmd
sqlplus /nolog
conn user/pwd@IP:1521/DBName;
45、锁定对象后的解锁
--查出锁定的对象sid与serial#，杀死会话（两列数据用逗号连接）
select * from v$session where sid in (select session_id from v$locked_object);
--结果
SID SERIAL#
================
136 14178
--杀死会话
alter system kill session '136,14178';
46、取得一段时间内的所有日期
--两个日期之间的天数
select to_char(to_date('2017-01-01','yyyy-mm-dd')+level-1) dt,level
from dual connect by level
