Oracle常用SQL语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- 删除表的内容，并清空其所占用表空间。但是不删除表结构。
truncate table xxx;
alter table xxx move tablespace xxx;
-- 切记，一定要重建索引，否则新增修改记录时候可能出错（ORA-01502）
select * from all_indexes where table_name = 'tablename';
alter index idx_name rebuild;

-- 查询全系统中占用表空间最大的一些表
select * from (
SELECT OWNER, TABLE_NAME, SUM(SPACEM) SPACEM
  FROM (SELECT A.OWNER, A.TABLE_NAME, B.SPACEM
          FROM DBA_LOBS A
          JOIN (SELECT OWNER, SEGMENT_NAME, SPACEM
                 FROM (SELECT OWNER,
                              SEGMENT_NAME,
                              BYTES / 1024 / 1024 / 1024 SPACEM
                         FROM DBA_SEGMENTS
                        WHERE SEGMENT_TYPE IN ('LOBSEGMENT','LOB PARTITION'))) B
            ON A.SEGMENT_NAME = B.SEGMENT_NAME
        UNION
        SELECT OWNER, SEGMENT_NAME, SPACEM
          FROM (SELECT OWNER, SEGMENT_NAME, BYTES / 1024 / 1024 / 1024 SPACEM
                  FROM DBA_SEGMENTS
                 WHERE SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')))
 GROUP BY OWNER, TABLE_NAME
 ORDER BY 3 DESC)
 where rownum <= 60;
 -- 或者用下面更简介的语句
 select a.tablespace_name,a.segment_name,a.BYTES/1024/1024 
 from user_segments a order by 2 desc;

# 新增用户和表空间并赋予权限
create tablespace xxx logging datafile '/oradata/dspdb/xxx01.dbf' 
size 10m autoextend on next 10m maxsize unlimited;

create user xxx identified by "xxx" default tablespace xxx temporary tablespace temp; 
grant all privileges to xxx;

-- 删除用户
drop user xxx cascade;
-- 删除表空间和数据文件
drop tablespace xxx including contents and datafiles;

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256

-- 查询所有表和视图
SELECT COUNT(*) FROM tab;
SELECT COUNT(*) FROM user_tables;
SELECT COUNT(*) FROM all_tables;
SELECT COUNT(*) FROM dba_tables;
-- 系统时间
select date() from dual;

-- 查询服务器字符集
select userenv('language') from dual;
-- 主要环境变量
select * from V$NLS_PARAMETERS

show parameter background_dump_dest;

-- 查询多少个实例
select instance_name,status from v$instance;

-- 查询表空间数量
select tablespace_name from dba_tablespaces;
select * from dba_data_files order by file_name;
-- 表空间有多少用户
select owner from dba_tables where tablespace_name='SYSTEM' group by owner;
-- 用户占用那些表空间
select distinct tablespace_name from dba_segments where owner='xxx';
-- 查询数据库字符集：
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
-- 查询所有文件的路径（系统参数文件路径）
select * from dba_directories;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 启动归档模式
archive log list;
select log_mode from v$database;
-- 先指定归档日志路径
alter system set log_archive_dest = '/oradata/dspdb/arch' scope=spfile sid='*';
archive log list;
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
archive log list;
-- 手工强制归档一次
alter system archive log current;

-- 查询日志分组信息
select * from v$log;
select archived,status from v$log;
select * from v$logfile;
-- 查询归档日志
select * from v$archived_log where archived='YES' and deleted='NO';
-- 执行rman 逻辑上删除归档日志
rman
RMAN> connect target /
>crosscheck archivelog all;
>delete expired archivelog all;
>quit
-----
>crosscheck archivelog all;
>delete noprompt expired archivelog all;
>delete noprompt archivelog all completed before 'sysdate-1';


-- Oracle 日志文件目录 (资金清算系统在备份的时候，有log记录在Oracle的相应目录)
select * from dba_directories
-- 正式库中这两个参数需要调整（第一个用到了，第二个好像没用）
create or replace directory DIR_NAME as '/oradata/dspdb/DIR_PATH';

-- 查询用户
select * from dba_users where account_status='OPEN';
select username,default_tablespace from dba_users where account_status='OPEN';
-- 查询所有用户以及状态
select username,account_status from dba_users order by username;
-- 查询用户的账号和密码
select username,password from dba_users;
-- 查询密码自动过期时间
select * from dba_profiles where resource_name ='PASSWORD_LIFE_TIME' and profile='DEFAULT';
-- 设置成永不过期
alter profile default limit password_life_time unlimited;
-- 解锁用户
alter user XXX identified by oracle account unlock;
-- 修改用户密码
alter user sdx identified by sdx111.222;
或者 alter user system identified by values 'xxxxxx';

-- 查看权限
select * from user_sys_privs;
-- 检查占用CPU的进程情况。
select * from v$process  a join v$session b on a.addr=b.paddr    and a.spid=20543

-- 调整表空间大小
select * from dba_data_files order by file_name;
alter tablespace UNDOTBS1 add datafile '/xxx/undotbs02.dbf' size 50m autoextend 
on next 50m maxsize unlimited;

alter database datafile '/oradata/dspdb/undotbs01.dbf' autoextend on;
alter database datafile '/oradata/dspdb/undotbs01.dbf' resize 10240M;

-- 清除表空间和数据
drop user USER_NAME       	cascade;

-- 删除用户
drop user xxx cascade;
-- 删除表空间和数据文件
drop tablespace xxx including contents and datafiles;

-- 创建用户
create user xxx identified by "xxx" default tablespace xxx temporary tablespace temp; 
grant all privileges to xxx;


-- 数据备份是有dba权限，还原的时候也需要相应的用户有 dba 权限。
grant dba to xxx;

-- 完事了要回收dba权限。
revoke dba from xxx;

ALTER USER xxx QUOTA 50M ON tablespace_name;
ALTER USER xxx QUOTA UNLIMITED ON tablespace_name;

select * from user_ts_quotas;
select * from dba_ts_quotas;

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--需要给 expdp 命令分配 备份目录的读写权限
create or replace directory dumps_dir as '/home/oracle/backup';
create or replace directory dumps_dir as '/home/oracle/dumps';
create or replace directory liq_log_dir as '/home/oracle/backup/xxx';
-- grant read,write on directory dumps_dir to system;
-- grant read,write on directory dumps_dir to system;
select * from dba_directories;

-- 导出数据
exp username/password@sid file=d:\exp.dmp full=y
imp username/password@sid file=d:\exp.dmp full=y ignore=y

-- 导入数据库文件
imp xxx/xxx file=xxx.dmp log=xxx.log full=y

-- 关于 dblink 的问题
-- 授权某个用户具有管理dblink的权限
grant create public database link, create database link to xxx;
-- 所有dblink
select * from dba_db_links;
create database link dsdblink connect to xxx identified by xxx using 'orcl';
create database link DSDBLINK connect to xxx identified by xxx using ' 
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.77.1)(PORT = 1521))
    )从
    (CONNECT_DATA =
      (SID = orcl)
      (SERVER = DEDICATED)
    )
)';
drop database link dsdblink;
drop public database link dsdblink;

----------------------------------------------------------------------------
-- 对表空间的特殊处理
select * from dba_data_files order by tablespace_name,file_name;
select FILE_NAME,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files order by maxbytes desc,file_name;
select FILE_NAME,tablespace_name,MAXBLOCKS,USER_BLOCKS,MAXBYTES,BYTES,USER_BYTES 
from dba_data_files order by tablespace_name,file_name;
select FILE_NAME,MAXBYTES,BYTES,USER_BYTES from dba_data_files order by file_name;

alter tablespace UNDOTBS1 add datafile '/xxx/undotbs03.dbf' size 50m autoextend 
on next 50m maxsize unlimited;
alter database datafile '/home/oracle/oradata/orcl/undotbs2_01.dbf' autoextend on;
alter database datafile '/home/oracle/oradata/orcl/undotbs2_01.dbf' resize 50M;
alter tablespace UNDOTBS1 drop datafile '/home/oracle/oradata/orcl/undotbs01.dbf';

-- 创建新的undo表空间
create undo tablespace UNDOTBS2 datafile '/xxx/undotbs2_01.dbf' size 1G;
alter system set undo_tablespace=UNDOTBS2;
drop tablespace UNDOTBS1 including contents and datafiles;

select * from dba_tablespaces;
或者
select tablespace_name,status from dba_tablespaces
 
-- 创建新的临时表空间
create temporary tablespace TEMP2 tempfile '/xxx/temp2_01.dbf' size 50M autoextend on;
alter database default temporary tablespace TEMP2;
drop tablespace TEMP including contents and datafiles cascade constraints;

-- 收缩表空间
select * from v$datafile;
select file#, name from v$datafile;
select max(block_id) from dba_extents where file_id=5; 
select 4194289 * 8 / (1024*1024) from dual;

select distinct SEGMENT_NAME from dba_extents where file_id= 42;
select * from dba_extents where file_id= 42;
-- alter table test move tablespace xxx;

-- 查询各张表占用磁盘空间大小。单位：GB
SELECT OWNER, TABLE_NAME, SUM(SPACEM) SPACEM
  FROM (SELECT A.OWNER, A.TABLE_NAME, B.SPACEM
          FROM DBA_LOBS A
          JOIN (SELECT OWNER, SEGMENT_NAME, SPACEM
                 FROM (SELECT OWNER,
                              SEGMENT_NAME,
                              BYTES / 1024 / 1024 / 1024 SPACEM
                         FROM DBA_SEGMENTS
                        WHERE SEGMENT_TYPE IN ('LOBSEGMENT','LOB PARTITION'))) B
            ON A.SEGMENT_NAME = B.SEGMENT_NAME
        UNION
        SELECT OWNER, SEGMENT_NAME, SPACEM
          FROM (SELECT OWNER, SEGMENT_NAME, BYTES / 1024 / 1024 / 1024 SPACEM
                  FROM DBA_SEGMENTS
                 WHERE SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')))
 GROUP BY OWNER, TABLE_NAME
 ORDER BY 3 DESC;


select * from dba_data_files order by maxbytes desc,file_name;

--删除表
TRUNCATE TABLE BLT_BULLETINANNEX drop storage;

--
SELECT T.TABLE_NAME, T.INITIAL_EXTENT/1024/1024 mb
FROM USER_TABLES T
WHERE T.TABLE_NAME = 'BLT_BULLETINANNEX';

-- 收缩表：
alter table  BLT_BULLETINANNEX  enable row movement;
alter table  BLT_BULLETINANNEX  shrink space;
alter table  BLT_BULLETINANNEX  disable row movement;

SELECT A.TABLESPACE_NAME, A.BYTES/1024/1024 TOTAL, B.BYTES/1024/1024 USED, C.BYTES/1024/1024 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
order by 4 desc;

-- risize 表空间
select * from dba_data_files order by maxbytes desc,file_name;
alter database datafile '/oradata/dspdb/ogg/ogg_tbs_01.dbf' 	resize 10G;

-- 删除表之后释放空间，不放入回收站。
-- 查询回收站状态
SELECT value FROM V$parameter WHERE Name = 'recyclebin';
-- 查询回收站中的表
select bytes,segment_type,owner,tablespace_name,segment_name from dba_segments 
where segment_name like 'BIN$%'
select object_id from dba_objects where object_name like 'BIN%';
select * from dba_recyclebin;
select owner,object_name,original_name,operation,type,space from dba_recyclebin order by space desc;
-- 关闭回收站，删除表，启用回收站，解决问题。
ALTER SESSION SET RECYCLEBIN=OFF;
drop table xxx."BIN$wycF2sxuNI3gVQAAAAAAAQ==$0";
drop table xxx."BIN$wyaNBoyEM0fgVQAAAAAAAQ==$0";
ALTER SESSION SET RECYCLEBIN=ON;
-- 直接删除表及其数据，不放入回收站
drop table table_name purge;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 突发情况处理办法：

-- 从闪回区找回误删除的数据，重新插入数据库
--select count(*) from tquerycomformat as of timestamp 
to_timestamp('2020-04-07 20:00:00','yyyy-mm-dd hh24:mi:ss');

--insert into tquerycomformat
--select * from tquerycomformat as of timestamp 
to_timestamp('2020-04-07 20:00:00','yyyy-mm-dd hh24:mi:ss');

-- 创建表空间（单个表空间最大能自增长到32GB）
create tablespace xxx logging datafile '/oradata/dspdb/xxx01.dbf' 
size 50m autoextend on next 50m maxsize unlimited;

-- 不需要主动创建用户，impdp会自动创建
create user xxx identified by "xxx" default tablespace xxx temporary tablespace temp; 
grant all privileges to xxx;

-- 还原整个用户表
impdp system/xxx@dspdb directory=dumps_dir dumpfile=xxx.dmp logfile=xxx.log 
schemas=xxx; data_options=skip_constraint_errors; 

-- 备份其中一张表
expdp system/xxx@dspdb directory=dumps_dir dumpfile=xxx.dmp logfile=xxx.log tables=xxx.xxx;

-- 还原其中一张表
impdp system/xxx@dspdb directory=dumps_dir dumpfile=xxx.dmp tables=xxx.xxx 
remap_schema=xxx:xxx remap_tablespace=xxx:xxx;

impdp system/xxx@dspdb directory=dumps_dir dumpfile=xxx.dmp tables=xxx.xxx
table_exists_action=replace;

impdp xxx/xxx@dspdb file=xxx.xxx.dmp log=xxx.log full=y ignore=y
-- expdp s/s directory=dump_dir1 dumpfile=sas1.dmp logfile=aa.log
-- schemas=scott 
-- tables=emp,dept 
-- query='where deptno=20'    		带条件
-- tablespaces=temp,example  		按表空间
-- full=y       					整个库
-- content=
									-- all			所有
									-- metadata_only只导表结构
									-- data_only 	只导出表数据

-- impdp
-- remap_schema=scott:system    	更改owner
-- tablespaces=example   			导入表空间
-- table_exists_action   			-- skip 		是如果已存在表，则跳过并处理下一个对象；
									-- append		是为表增加数据；
									-- truncate		是截断表，然后为其增加新数据；
									-- replace		是删除已存在表，重新建表并追加数据