--创建表空间
CREATE TABLESPACE BZ_DATA
DATAFILE'D:\oracle\product\10.2.0\oradata\PRISON\BZ_DATA01.DBF'SIZE 50M;
--创建用户
CREATE USER bzprison
IDENTIFIED BY bzprison
DEFAULT TABLESPACE BZ_DATA;
--授予权限
GRANT dba TO bzprison;
GRANT CONNECT TO bzprison;
GRANT RESOURCE TO bzprison;
--删除用户
DROP USER test CASCADE
--删除表空间
DROP TABLESPACE TEST_LIFT_HEALTH INCLUDING CONTENTSAND DATAFILES
//取消授权
revoke connect,resource from test;
//授予权限
grant connect,resource to test;
select username,default_tablespace fromdba_users;
select username,default_tablespace fromuser_users;
alter user jlyz default tablespace jlyz_test;
select*fromrole_sys_privs;
删除表的某一行
ALTER TABLE alonerewardinfo DROP COLUMNzfids;
查询表中某一列在所有表中被引用的记录
select b.table_name,b.column_name from user_constraints a
inner join user_cons_columns b
on a.constraint_name =b.constraint_name
where a.r_constraint_name=@H_403_165@'SYS_C0018059'
@H_403_165@
@H_403_165@
@H_403_165@
@H_403_165@
查看数据库默认的密码管理方式有效期
SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
ALTER PROFILEDEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ;
Oracle用户解锁
alter user sw account unlock
oracle导入导出
导入: impjlyz/123456789 file=E:\jlyz.dmp log=E:\jlyz.logfromuser=jlyz touser=jlyz
导出: expjlyz/123456789 file=E:\jlyz.dmp log=E:\log-.log
--1、查看表空间的名称及大小
SELECTt.tablespace_name,round(SUM(bytes / (@H_403_165@1024 * @H_403_165@1024)),@H_403_165@0) ts_size
FROMdba_tablespaces t,dba_data_files d
WHEREt.tablespace_name = d.tablespace_name
GROUPBY t.tablespace_name;
SELECTtablespace_name,
file_id,
file_name,
round(bytes /(@H_403_165@1024 * @H_403_165@1024),@H_403_165@0) total_space
FROMdba_data_files
ORDERBY tablespace_name;
--修改表空间大小
alterdatabasedatafile@H_403_165@'D:\ORACLDATA\JLYZ_TEST.DBF'resize@H_403_165@10240m
--查看表空间使用情况
SELECTUPPER(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 * @H_403_165@100,@H_403_165@2),@H_403_165@'990.99') || @H_403_165@'%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (@H_403_165@1024 * @H_403_165@1024),@H_403_165@2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (@H_403_165@1024 * @H_403_165@1024),@H_403_165@2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUPBY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (@H_403_165@1024 * @H_403_165@1024),@H_403_165@2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUPBY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDERBY@H_403_165@1;
原文链接：https://www.f2er.com/oracle/211639.html