oracle常用语法1
--创建普通表空间
CREATE TABLESPACE JFRAME_TBS DATAFILE 'D:\dmpfile\jframe_tbs.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT COMPRESS;
--查看所有用户
select * from dba_users;
--创建用户
create user jframe identified by jframe
default tablespace JFRAME_TBS;
--赋予普通权限
GRANT CONNECT,RESOURCE TO jframe;
--创建用户
CREATE USER linelost identified by linelost
default tablespace LINELOST_TAB;
--赋予dba权限
GRANT CONNECT,RESOURCE,DBA TO linelost;
--查看JFRAME用户所有权限
select privilege from dba_sys_privs where grantee='JFRAME'
union
select privilege from dba_sys_privs where grantee in
(select granted_role from dba_role_privs where grantee='JFRAME' );
--查看表是否分区
select partition_name from user_tab_partitions
where table_name='表'
--dblink 从一个数据库将数据复制到另一个数据库
create public database link dblink_200
connect to jframe identified by jframe
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.29.200)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
--删除dblink
drop public database link LINELOST_202;
create table ACT_RU_VARIABLE as select * from ACT_RU_VARIABLE@dblink_200
--表空间移动
alter tablespace YXXT_TBS offline; --将目标表空间设置为脱机状态
mv /u03/oradata/tqxs/yxxt_tbs.dbf /backup/oradata/tqxs/tablespace/
alter tablespace YXXT_TBS rename datafile '/u03/oradata/tqxs/yxxt_tbs.dbf' to '/backup/oradata/tqxs/tablespace/yxxt_tbs.dbf';
alter tablespace YXXT_TBS online;
--表空间创建
CREATE TABLESPACE YXXT_TBS DATAFILE '/backup/oradata/tqxs/tablespace/yxxt_tbs_two.dbf' SIZE 5120M AUTOEXTEND ON NEXT 148M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT COMPRESS;
--给表空间添加文件 普通表
alter tablespace YXXT_TBS add DATAFILE '/backup/oradata/tqxs/tablespace/yxxt_tbs_two.dbf' SIZE 5120M AUTOEXTEND ON NEXT 148M MAXSIZE UNLIMITED NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT COMPRESS;
ALTER TABLESPACE YXXT_TBS ADD DATAFILE '/backup/oradata/tqxs/tablespace/yxxt_tbs_four.dbf' SIZE 5120M AUTOEXTEND ON NEXT 200M MAXSIZE 20G ;
--临时表空间
ALTER TABLESPACE YXXT_TBS ADD TEMPFILE '/backup/oradata/tqxs/tablespace/yxxt_tbs_two.dbf' SIZE 5120M AUTOEXTEND ON NEXT 200M;
--查看表空间使用率
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 and a.tablespace_name='YXXT_TBS';
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
-- D.file_name "表空间路径",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
-- , file_name
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME
--,DD.file_name
) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and f.tablespace_name like 'P_JL_MPOINT%'
ORDER BY 1
--查看表空间所在位置
select * from dba_data_files where tablespace_name='YXXT_TBS'
--查看用户临时表空间
select username,default_tablespace,temporary_tablespace from dba_users where username='YXXT';
--执行存储过程 报没有权限创表错误
在存储过程后接 AUTHID CURRENT_USER 表示可以在存储过程上使用调用者权限
CREATE OR REPLACE PROCEDURE P_CREATE_PRO
AUTHID CURRENT_USER IS
--查看表空间已分配空间 HWMsize
select * from (
select file_id,file_name,round(bytes/1024/1024)filesize from dba_data_files
) a,(
select file_id,round(sum(dfs.BYTES)/1024/1024) filesize from dba_free_space dfs group by file_id
)b,(
select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id
)c where a.file_id=b.file_id and a.file_id=c.file_id
--删除表空间
drop tablespace gaungdong_cbxx_temp_tbs including contents and datafiles cascade constraint;
--删除用户
drop user topway_temp cascade
--创大表空间
create bigfile tablespace T_ELEC_DATA_USER_topway_temp datafile'/u04/oracle/tqxs/T_ELEC_DATA_USER_topway_temp.dbf' size
204800M autoextend on next 100M maxsize unlimited logging extent management local autoallocate default compress;
--压缩表空间
alter database datafile'/u04/oradata/tqxs/dwxs_tbs_guangdong.dbf' resize 204800M;
--创建索引
主键索引
create unique index IDX_T_P_ELEC_DATA on T_ELEC_DATA_USER ( USER_CODE, BILL_MONTH ) tablespace linelost_tbs parallel 18 nologging;
一般索引
create index IDX_T_P_USER_ELEC_BILL_MONTH on T_ELEC_DATA_USER (BILL_MONTH) tablespace linelost_tbs parallel 18 nologging;
--修改job执行时间
exec dbms_job.next_date(285,to_date('2018-06-07 23:59:00', 'yyyy-mm-dd hh24:mi:ss'));
declare
V_STR_COUNT VARCHAR2(200);
begin
FOR I IN 126..195 LOOP
V_STR_COUNT:='alter database datafile '||I ||' offline drop;';
DBMS_OUTPUT.PUT_LINE(V_STR_COUNT);
END LOOP;
end;
oracle 重启数据库
sqlplus /nolog
conn /as sysdba
--立即重启 shutdown immediate
--强制重启 shutdown abort
--启动 startup open
--表空间使用率 1
SELECT *
FROM (SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1)
WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'TEMP','UNDOTBS');
--查看Oracle 临时表空间使用率
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
--查看Temp 表空间实际使用磁盘大小
Select f.tablespace_name,
d.file_name "Tempfile name",
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",
round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024,
2) "Free MB",
round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",
round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /
round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,
2) as "Used_Rate(%)"
from SYS.V_$TEMP_SPACE_HEADER f,
DBA_TEMP_FILES d,
SYS.V_$TEMP_EXTENT_POOL p
where f.tablespace_name(+) = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id
;
--查看当前临时表空间使用大小与正在占用临时表空间的sql语句
select sess.SID,
segtype,
blocks * 8 / 1000 "MB",
sql_text
from v$sort_usage sort,
v$session sess,
v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc
;
