Oracle常用语句-CSDN博客

================常用命令=============================
一、Oracle可以使用自带的sqlplus（点击“开始”，搜索“sqlplus”）打开，也可以使用cmd窗口命令符。
1、使用自带的sqlplus输入命令：
sqlplus /as sysdba （无需密码）
2、使用cmd打开
1.sqlplus /nolog
2.conn sys/口令 as sysdba（连接身份）

二、创建用户
CREATE USER 用户名 IDENTIFIED BY 口令 [ACCOUNT LOCK | UNLOCK] (默认是LOCK，一般设置为UNLOCK)
创建完用户还不能登录，需要授权。（权限：就是若干个数据库角色的集合）
三、授权
grant 权限 to 用户名;
权限分三种：
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。一般自己建的数据库直接授权DBA后就不用授权后两个了。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
四、撤销授权
revoke 权限 from 用户名;

=========================删除用户======================
drop user 用户名 cascade;

===========================创建表空间================================
create bigfile tablespace bxfund
datafile 'D:\Ysszx\bxfund.dbf' size 50g
autoextend on next 50M maxsize unlimited logging
extent management local autoallocate
segment space management auto;

============创建临时表空间如果建超大空间，需要在前面添加bigfile==============
create bigfile temporary tablespace bxfund_temp
tempfile 'E:\RbWork\tablenamespace\bxfund_temp.dbf'
size 80g
autoextend on next 50m
extent management local

=================创建用户并指定表空间===========================
create user piccfund identified by piccfund
default tablespace bxfund
tablespace finchinafcdd;

=========================dmp文件导入数据库======================
在cmd下用 imp导入格式： imp userName/passWord file=bmp文件路径 ignore = y (忽略创建错误)full=y(导入文件中全部内容);

imp PICCFUND/PICCFUND file="全路径" ignore=y full=y;

eg：
imp piccreport/piccreport file=F:\外出开会\piccreport20180326.dmp ignore=y full=y;

=======================导出dmp文件============================

数据导出：

exp 用户名/密码@网络服务名 file=xxx.dmp tables=(表名);

eg:
exp user/123456@10.83.200.171 file=d:\dbbackup\file1221_zwy.dmp log=d:\dbbackup\file1221_zwy.log

exp user/"""123@456"""@10.83.200.171 file=d:\dbbackup\file1221_zwy.dmp log=d:\dbbackup\file1221_zwy.log

注：数据库密码中含有@字符时，用"""区分。

1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中

exp rmdb/rmdb123@hz_rmdb file=d:\daochu.dmp full=y

2 将数据库中system用户与sys用户的表导出

exp rmdb/rmdb123@hz_rmdb file=d:\daochu.dmp owner=(system,sys)

3 将数据库中的表table1 、table2导出

exp rmdb/rmdb123@hz_rmdb file=d:\daochu.dmp tables=(table1,table2)

4 将数据库中的表table1中的字段filed1以"00"打头的数据导出

exp rmdb/rmdb123@hz_rmdb file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"

上面是常用的导出，对于压缩我不太在意，用winzip把dmp文件可以很好的压缩。
不过在上面命令后面加上 compress=y 就可以了

================查看表空间使用情况===================
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

=========================查看用户属于哪个表空间======================
select username,default_tablespace from dba_users where username='用户名';

eg:
select username,default_tablespace from dba_users where username='SCOTT';--用户名需要大写

--统计当前数据库表及数据量
select u.TABLE_NAME,u.NUM_ROWS from user_tables u
where table_name in('CSJJXX','JJHZXX','CSZQXX')

--强制走索引
select /*+index(t index_name)*/ * from xxx t;

--重启数据库
sqlplus ....
shutdown immediate ;
startup;

---表碎片整理
--https://blog.csdn.net/ggwxk1990/article/details/77867748
alter table lusers enable row movement ;

ALTER TABLE lusers SHRINK SPACE COMPACT

ALTER TABLE lusers SHRINK SPACE cascade;

--
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;

## 利用视图查看
select name from v$datafile;

--表空间使用情况查询
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　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
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;

---------------------------------
--增加表空间文件
ALTER TABLESPACE app_data ADD DATAFILE
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 50M;

--手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP02.DBF'
RESIZE 100M;
---------------------------------
SQL code
--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

--创建表空间
CREATE TABLESPACE report_bxfund LOGGING DATAFILE '/home/oracle/app/oracle/oradata/orcl/report_bxfund.dbf' SIZE 20480M AUTOEXTEND ON NEXT 128M EXTENT MANAGEMENT LOCAL;

--创建临时表空间
create temporary tablespace report_bxfund_temp tempfile '/home/oracle/app/oracle/oradata/orcl/report_bxfund_temp.dbf' size 1024m autoextend on next 32m extent management local;

--创建用户
CREATE USER report_bxfund IDENTIFIED BY report_bxfund DEFAULT TABLESPACE report_bxfund TEMPORARY TABLESPACE report_bxfund_temp;

--授权
grant connect,resource,dba to report_bxfund;

grant create session to report_bxfund;

--## 利用视图查看
select name from v$datafile;

----获得创建表空间的语句：*/

SELECT dbms_metadata.get_ddl('TABLESPACE','SYSTEM') FROM dual;

---- 添加表空间的语句：*/

ALTER TABLESPACE report_bxfund ADD DATAFILE '/home/oracle/app/oracle/oradata/orcl/report_bxfund2.dbf' SIZE 20480M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

---删除用户删除表空间--------------------------------------
drop tablespace zhjjetf including contents and datafiles cascade constraint;

drop user zhjjetf cascade;

--- 权限访问表空间不受限
alter user GUARD4ABC quota unlimited on JK4ABC

--- 修改用户密码
alter user <username> identified by xxxx

转载于:https://www.cnblogs.com/yaohuiqin/p/10368065.html