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;
linux查看当前文件夹内容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,PROGR = 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;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。