Linux 操作 oracle 数据库

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

1、Oracle监听启动命令：lsnrctl start

2. sqlplus 登录数据库

3.OS版本及Oracle版本

select banner from v$version;

4.查询该数据库的实例名

select name from v$database;

5.查询该用户下所有的表

select * from tab;

6.查询当前用户的缺省表空间

select username,default_tablespace from user_users;

7.查询当前用户的角色

select * from user_role_privs;

8.可以看到表内容

SQL> select * from dba_directories;

//用户权限不够，需要给enfo_ods 导出目录的读写权限

SQL> conn sys/Oracle001@enfodw as sysdba;

Connected.

//由于目录权限不清楚，下面就用dba的身份新建一个目录

SQL> create directory wensm_dir as '/home/oracle/wensm';

Directory created.

SQL> grant write,read on directory wensm_dir to sjcj;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

9.查询所有表空间

select file_name,tablespace_name,bytes from dba_data_files;

查看表空间的名称及大小:

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;

查看表空间物理文件的名称及大小 :

SELECT TABLESPACE_NAME,FILE_ID,FILE_NAME,ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;

查看所有表空间对应的数据文件名：

SQL>SELECT DISTINCT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;

查看表空间的使用情况 :
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;

COLUMN TABLESPACE_NAME FORMAT A18;
COLUMN SUM_M FORMAT A12;
COLUMN USED_M FORMAT A12;
COLUMN FREE_M FORMAT A12;
COLUMN PTO_M FORMAT 9.99;

SELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(S.USEDSPACE/1024/1024))||'M' USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M, SUM(S.USEDSPACE)/SUM(S.BYTES) PTUSED FROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE, SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE A.FILE_ID(+)=B.FILE_ID GROUP BY B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME) S GROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) DESC;

查看数据文件的 hwm （可以 resize 的最小空间）和文件头大小 :
SELECT V1.FILE_NAME,V1.FILE_ID,NUM1 TOTLE_SPACE,NUM3 FREE_SPACE,
NUM1-NUM3 "USED_SPACE(HWM)",NVL(NUM2,0) DATA_SPACE,NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+) AND V1.FILE_ID=V3.FILE_ID(+);
数据文件大小及头大小 :
SELECT V1.FILE_NAME,V1.FILE_ID,
NUM1 TOTLE_SPACE,
NUM3 FREE_SPACE,
NUM1-NUM3 USED_SPACE,
NVL(NUM2,0) DATA_SPACE,
NUM1-NUM3-NVL(NUM2,0) FILE_HEAD
FROM
(SELECT FILE_NAME,FILE_ID,SUM(BYTES) NUM1 FROM DBA_DATA_FILES GROUP BY FILE_NAME,FILE_ID) V1,
(SELECT FILE_ID,SUM(BYTES) NUM2 FROM DBA_EXTENTS GROUP BY FILE_ID) V2,
(SELECT FILE_ID,SUM(BYTES) NUM3 FROM DBA_FREE_SPACE GROUP BY FILE_ID) V3
WHERE V1.FILE_ID=V2.FILE_ID(+)
AND V1.FILE_ID=V3.FILE_ID(+);

(运行以上查询，我们可以如下信息：
Totle_pace:该数据文件的总大小，字节为单位
Free_space:该数据文件的剩于大小，字节为单位
Used_space:该数据文件的已用空间，字节为单位
Data_space:该数据文件中段数据占用空间，也就是数据空间，字节为单位
File_Head:该数据文件头部占用空间，字节为单位)

10. 查看日志文件 :
SQL> COL MEMBER FORMAT A50
SQL>SELECT * FROM V$LOGFILE;

11. 如何查看当前数据库的字符集 :
SQL>SELECT USERENV('LANGUAGE') FROM DUAL;
SQL>SELECT USERENV('LANG') FROM DUAL;

12. 如何查看客户端登陆的 IP 地址 :
SQL>SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM DUAL;

如何在生产数据库中创建一个追踪客户端 IP 地址的触发器：
CREATE OR REPLACE TRIGGER ON_LOGON_TRIGGER AFTER LOGON ON DATABASE
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(SYS_CONTEXT('USERENV', 'IP_ADDRESS'));
END;

REM 记录登陆信息的触发器
CREATE OR REPLACE TRIGGER LOGON_HISTORY
AFTER LOGON ON DATABASE --WHEN (USER='WACOS') --ONLY FOR USER 'WACOS'
BEGIN
INSERT INTO SESSION_HISTORY SELECT USERNAME,SID,SERIAL#,AUDSID,OSUSER,ACTION,SYSDATE,NULL,SYS_CONTEXT('USERENV','IP_ADDRESS'),TERMINAL,MACHINE,PROGRAM FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');
END;

13.查询当前日期 :
SQL> SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;

14.修改用户密码

查看所有用户名、密码

select username,password from dba_users;

修改用户密码

//修改用户口令格式 alter user 用户名 identified by 新密码;

SQL> alter user system identified by manager;

若切换另外一个用户就要断掉当前的登录
discon 回车键
若再连接其他用户
conn 回车键再录入其他登录用户信息即可

select * from dba_users; 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from all_users; 查看你能管理的所有用户！
select * from user_users; 查看当前用户信息！

-- 查询你 当前用户下,有哪些表
SELECT * FROM user_tables

-- 查询你 当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]
SELECT * FROM all_tables

-- 查询当前数据库所有的表， 需要你有 DBA 的权限
SELECT * FROM dba_tables

使用例子：

1、创建新用户：jkw

--查询数据库表空间
select distinct TABLESPACE_NAME from tabs;

create tablespace SJCJ_DAT DATAFILE 'D:\app\oukf\virtual\oradata\orcl\SJCJ_DAT.DBF' SIZE 2048M AUTOEXTEND ON NEXT 5M MAXSIZE 3072M;

--修改表空间大小

ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/app/oracle/oradata/test/system02.dbf' SIZE 20480M AUTOEXTEND OFF;

--查看你能管理的所有用户
select * from all_users;

--删除用户
--drop user JKW cascade;

/*第3步：创建用户并指定表空间 */
create user jkw identified by jkw123
default tablespace RMDB
--temporary tablespace rmdb_temp;

CREATE USER SJCJ IDENTIFIED BY sjcj DEFAULT TABLESPACE SJCJ_DAT;

create user hc_notify identified by hc_password default tablespace NOTIFYDB temporary tablespace NOTIFYDB_TEMP;

/*第4步：给用户授予权限 */

grant connect,resource,dba to jkw;

grant dba to hc_notify;

grant connect,resource to hc_notify;

grant select any table to hc_notify;

grant delete any table to hc_notify;

grant update any table to hc_notify;

grant insert any table to hc_notify;

2、导入数据：

imp jkw/jkw123@112.230.253.54/rmdb file=C:\Users\Administrator\Desktop\mhc.dmp full=y

--查看临时表空间文件

select name from v$tempfile;

--查看用户和表空间的关系

select USERNAME,TEMPORARY_TABLESPACE from DBA_USERS;

--如果有用户的默认临时表空间是NOTIFYDB_TEMP的话，建议进行更改

alter user xxx temporary tablespace tempdefault;

---设置tempdefault为默认临时表空间

alter database default temporary tablespace tempdefault;

--删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件

drop tablespace NOTIFYDB_TEMP including contents and datafiles;

--查看表空间文件

select name from v$datafile;

--停止表空间的在线使用

alter tablespace 表空间名称 offline;

--删除表空间NOTIFYDB_TEMP及其包含数据对象以及数据文件

drop tablespace NOTIFYDB_TEMP including contents and datafiles;

--查看所有的用户

select * from all_users;

--查看当前用户信息

select * from user_users;

--查看当前用户的角色

select * from user_role_privs;

--查看当前用户的权限

select * from user_sys_privs;

--查看当前用户的表可操作权限

select * from user_tab_privs;

--查看某一个表的约束,注意表名要 大写

select * from user_constraints where table_name='TBL_XXX';

--查看某一个表的所有索引,注意表名要 大写

select index_name,index_type,status,blevel from user_indexes where table_name = 'TBL_XXX';

--查看索引的构成,注意表名要 大写

select table_name,index_name,column_name, column_position FROM user_ind_columns WHERE table_name='TBL_XXX';

--系统数据字典 DBA_TABLESPACES 中记录了关于表空间的详细信息

select * from sys.dba_tablespaces;

--查看用户序列

select * from user_sequences;

--查看数据库序列

select * from dba_sequences;