Oracle基础命令

lovesais

已于 2024-07-15 09:54:29 修改

阅读量105

点赞数 3

分类专栏：数据库文章标签： oracle 数据库

于 2024-07-15 09:50:29 首次发布

本文链接：https://blog.csdn.net/lovesais/article/details/140430110

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

常用基础命令

1、导出数据库dmp包（全库）

exp sys/sys@TestDB file=DB.dmp full=y

2、导入数据库dmp包

imp sys/sys@ora9i  file=/home/Db/out.dmp  full=y  ignore=y

3、创建表空间

CREATE TABLESPACE TBS_DATA DATAFILE 
  '/opt/oracle/oradata/orcl/TBS_DATA.dbf' SIZE 5120M AUTOEXTEND ON NEXT 1024M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;

4、创建用户赋值权限

DROP USER USER_SMCX CASCADE;
CREATE USER "USER_SMCX" IDENTIFIED BY "USER_SMCX"
 DEFAULT TABLESPACE USER_SMCX
 PROFILE DEFAULT
 QUOTA UNLIMITED ON USER_SMCX;
GRANT "DBA" TO "USER_SMCX" WITH ADMIN OPTION;
ALTER USER "USER_SMCX" DEFAULT ROLE "DBA";

GRANT CREATE SESSION TO USER_SMCX ;
exec dbms_java.grant_permission( 'USER_SMCX', 'SYS:java.net.SocketPermission', '127.0.0.1:9000', 'connect, resolve');
exec dbms_java.grant_permission( 'USER_SMCX', 'SYS:java.net.SocketPermission', '127.0.0.1:9001', 'connect, resolve');

5、查询密码的有效期设置

SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';

6、修改密码

ALTER USER SCUD_TJ1 IDENTIFIED BY SCUD_TJSQ;

7、查看用户账户状态

SELECT username,PROFILE,ACCOUNT_STATUS,LOCK_DATE  FROM dba_users;

8、长久对应可通过将密码有效期由默认的180天修改成“无限制”

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED

问题排查

查看当前连接用户的信息

select * from user_users

查询那些应用的连接数此时是多少

SELECT
	b.MACHINE,
	b. PROGRAM,
	COUNT (*)
FROM
	v$process A,
	v$session b
WHERE
	A .ADDR = b.PADDR
AND b.USERNAME IS NOT NULL
GROUP BY
	b.MACHINE,
	b. PROGRAM
ORDER BY
	COUNT (*) DESC;

查看表空间的名字及文件所在位置

select tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
from sys.dba_data_files
order by tablespace_name

查询当前表空间下使用情况

select a.tablespace_name,
a.bytes / 1024 / 1024 "sum MB",
(a.bytes - b.bytes) / 1024 / 1024 "used MB",
b.bytes / 1024 / 1024 "free MB",
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "used%"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes) desc;