本文详细介绍了Oracle数据库的管理操作，包括创建与删除数据库、表空间、用户，分配权限，以及数据备份与导入。此外，还涉及了查看表空间属性、用户权限、表信息等常用命令，以及处理表空间自动增长、密码有效期设置和锁定问题的方法。最后，讲解了跨库查询、调整数据库连接数以及如何处理无主键的表。
一、数据库操作
1、建数据库
使用创建向导创建数据库实例MERP
处理空表导不出：alter system set deferred_segment_creation=false;
2、创建表空间
create tablespace MERPD
datafile 'D:\app\Administrator\oradata\MERPTEST\MERPD.dbf'
size 500M AUTOEXTEND ON NEXT 200M
logging
extent management local;
3、创建用户
CREATE USER merp IDENTIFIED BY 123456 DEFAULT TABLESPACE MERPD;
分配表空间给用户
alter user merp default tablespace MERPD;
4、分配用户权限
grant connect,resource,dba,create session to merp ;
5、删除表空间
DROP TABLESPACE MERPTRD INCLUDING CONTENTS AND DATAFILES;
修改表空间名字
alter tablespace MERPD rename to BTD;
6、删除用户
drop user merp cascade;
select username,sid,serial#,paddr from v$session where username='merp';
7、导出备份
exp dfdx/123456@DFDX file = d:\dfdx.dmp log = d:\dfdxlog.dmp full = y (全部用户)/owner=(dfdx)（个别用户）compress = n
8、导入备份
创建数据库后 SQL> alter system set deferred_segment_creation = false scope=spfile;
处理空表导出：select 'alter table '||table_name||' allocate extent;' from user_tables where nvl(num_rows,0)=0
imp merp/123456@MERP file = d:\merp0312.dmp log = d:\demolog.dmp full = y ignore = y tables = (tablenames)
imp merphx/123456@MERPHX file = d:\merphx0229.dmp grants=n rows=y ignore=y fromuser=merphx touser=merphx
imp merp/123456@MERP file=D:\merp20210831.dmp grants=n rows=y ignore=y fromuser=(merp) touser=(qh) tables = (YG_PR_BOM,YG_PR_BOMCHILD)
导入到不同表空间：imp HI_APP_TENANT1201_NEW/pass@10.0.33.83:1521/orcl file=d:\tenant1201.dmp fromuser=HI_APP_TENANT1201 touser=HI_APP_TENANT1201_NEW tablespaces=DB_TENANT1201_NEW;
9、对oracle常用的操作命令
1)查看表空间的属性
select tablespace_name,extent_management,allocation_type from dba_tablespaces
2)查找一个表的列,及这一列的列名,数据类型
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from user_tab_columns where TABLE_NAME='xxxx'
3)查找表空间中的用户表
select * from all_tables where owner='xxx' order by table_name desc
4)在指定用户下,的表的数量
select count(*) from user_tab_columns
5)查看数据库中的表名,表列,所有列
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from user_tab_columns order by table_name desc
6)查看用户ZBFC的所有的表名及表存放的表空间
select table_name,tablespace_name from all_tables where owner='xxxx' order by table_name desc
7)生成删除表的文本
select 'Drop table '||table_name||';' from all_tables where owner="ZBFC";
8)删除表级联删除
drop table table_name [cascade constraints];
9)查找表中的列
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from user_tab_columns where column_name like '%'||'地'||'%' order by table_name
desc
10)查看数据库的临时空间
select tablespace_name,EXTENT_SIZE,current_users,total_extents,used_extents,MAX_SIZE,free_extents from v$sort_segment;
--创建临时表空间
create temporary tablespace DFDXT
tempfile 'D:\app\Administrator\oradata\dfdx\DFDXT01.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--创建数据表空间
create tablespace DFDXD
logging
datafile 'G:\oracledata\DFDXD.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--创建用户并指定表空间
create user dfdx identified by 123456
default tablespace DFDXD
temporary tablespace DFDXT;
--给用户授予权限
grant connect,resource,dba to dfdx;
oracle正在启动或关闭中的解决方法
set ORACLE_SID=merp
sqlplus "/as sysdba"
shutdown immediate; //关闭数据库
startup; //启动数据库
shutdown immediate; //再次关闭数据库
startup mount; //启动例程
recover datafile 3; //修复文件3，因为前面显示文件3出了问题
shutdown immediate; //再次关闭数据库
startup; //启动数据库，发现启动成功
----查看各表空间分配情况。
select tablespace_name, sum(bytes) / 1024 / 1024 from dba_data_files group by tablespace_name;
3.查看各表空间空闲情况。
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;
-------
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 "使用率" desc;
4.更改数据表大小(10G)
alter database datafile '/ora/oradata/radius/undo.dbf' resize 10240m；
5.设置表空间不足时自动增长
5.1查看表空间是否自动增长
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
5.2 设置表空间自动增长
ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON;//打开自动增长
ALTER DATABASE DATAFILE 'F:\APP\ADMINISTRATOR\ORADATA\MERPHX\MERPJXD1.DBF' AUTOEXTEND ON NEXT 200M ;//每次自动增长200m
ALTER DATABASE DATAFILE 'F:\app\Administrator\oradata\MERP\MERPD.DBF' AUTOEXTEND ON NEXT 200M MAXSIZE 10240M;//每次自动增长200m，数据表最大不超过1G
5.3 增加数据库文件并设置自动增长
ALTER TABLESPACE MERPD ADD DATAFILE
'E:\oracleData\MERPD02.DBF' SIZE 1024M AUTOEXTEND ON NEXT 500M;
二、数据库配置
--查看数据库密码有效期
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
--修改密码有效期为无限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
(1)锁表查询的代码有以下的形式：
select count(*) from v$locked_object;
select * from v$locked_object;
(2)查看哪个表被锁
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
(3)查看是哪个session引起的
select a.OS_USER_NAME, c.owner, c.object_name, b.sid, b.serial#, logon_time
from v$locked_object a, v$session b, dba_objects c
where a.session_id = b.sid
and a.object_id = c.object_id
order by b.logon_time;
(4)杀掉对应进程
执行命令：alter system kill session '1025,41'; 需要用户有权限操作
其中1025为sid,41为serial#.
Oracle dmp文件导入（还原）到不同的表空间和不同的用户下
参考导入命令
impdp onlinecolzs/onlinecozs dumpfile=XXX.dmp directory=dpdata1 remap_schema=onlinecol:onlinecolzs remap_tablespace=ts_onlinecol_prod:zaixianzs_data
参数说明
dumpfile:需要导入的dmp文件名称
directory:dmp文件所在的目录文件夹
REMAP_SCHEMA： 该选项用于将源方案的所有对象装载到目标方案中。 REMAP_SCHEMA=source_schema:target_schema
REMAP_TABLESPACE： 将源表空间的所有对象导入到目标表空间中。 REMAP_TABLESPACE=source_tablespace:target:tablespace
1、创建表空间和用户。
2、创建目录
create directory dpdata1 as 'D:\tempdmp'
把dmp文件复制到刚才创建的目录文件夹下(D:\tempdmp)，这个文件夹需要自己手动创建
3、执行impdp导入命令：
impdp onlinecolzs/onlinecozs dumpfile=XXX.dmp directory=dpdata1 remap_schema=merphx:merpjx remap_tablespace=MERPHD:MERPJXD
impdp merpjx/123456 dumpfile = merp0323.dmp directory=dpdata1 remap_schema = merphx:merpjx remap_tablespace=MERPHD:MERPJXD
其中：ts_onlinecol_prod：原先dmp文件的表空间名称，如果不知道原先的表空间名称是什么，先随便写个，到时候导入的时候会提示表空间不存在，把提示不存在的表空间名称写上去就可以。
zaixianzs_data：需要导入到的表空间的名称（第一步创建的表空间名称）
----------------------------------------------------------------------------------------------------------------
导出：expdp merphx/123456@merphx dumpfile=merphx0323.dmp directory=dpdata1 schemas=merphx
directory可以采用数据库默认的几个（DATA_PUMP_DATA），也可以自定义
导入：impdp newUsername/password@orcl dumpfile=xxx.dmp directory=xxxx remap_schema=oldUserName:newUserName
remap_tablespace=oldTablespace:newTablespace
---------------------------------------------------------------------------------------------------------------------
跨库查询
--CREATE DATABASE link CY CONNECT TO MERP IDENTIFIED BY "123456" USING '192.168.8.10:1521/MERP'
--SELECT * FROM JS_SYS_COMPANY@CY
修改process、session值大小
select count(*) from v$session #连接数
Select count(*) from v$session where status='ACTIVE' #并发连接数
show parameter processes #最大连接
alter system set processes = value scope = spfile;重启数据库 #修改连接
1.查看Oracle最大连接数
SQL>show parameter processes #最大连接数
2.修改最大连接数
SQL>alter system set processes=value scope=spfile
重启数据库
SQL>shutdown force
SQL>start force
3.查看当前连接数
SQL>select * from V$SESSION where username is not null
4.查看不同用户的连接数
SQL>select username,count(username) from V$SESSION where username is not null group by username #查看指定用户的连接数
5.查看并发连接数
SQL>select count(*) from V$SESSION where status='ACTIVE' #查看并发连接数
6.查指定程序的连接数
SQL>select count(*) from V$SESSION where program='JDBC Thin Client' #查看JDBC连接Oracle的数目
————————————————
版权声明：本文为CSDN博主「海笑天涯」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/haixiao1314/article/details/23206747
--查询是否存在主键
WITH AA AS (
SELECT
cu.TABLE_NAME
FROM
user_cons_columns cu,
user_constraints au
WHERE
cu.constraint_name = au.constraint_name
AND au.constraint_type = 'P'
AND CU.TABLE_NAME LIKE 'YG%'
),
bb AS ( SELECT table_name, num_rows FROM user_tables WHERE TABLE_NAME LIKE 'YG%' ) SELECT
B.*,
CASE
WHEN A.TABLE_NAME IS NULL THEN
'无主键' ELSE '有主键'
END ,'alter table '||B.TABLE_NAME ||' add primary key (ID) enable validate;'
FROM
BB B
LEFT JOIN AA A ON A.TABLE_NAME = B.TABLE_NAME
WHERE
A.TABLE_NAME IS NULL --无主键
扫一扫
147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
