DROP USER test CASCADE
--删除表空间
DROP TABLESPACE TEST_LIFT_HEALTH INCLUDING CONTENTSAND DATAFILES @H_502_0@ @H_502_0@//取消授权 @H_502_0@revoke connect,resource from test; @H_502_0@//授予权限
grant connect,resource to test; @H_502_0@查询所有用户和当前用户表空间
select username,default_tablespace fromdba_users;
@H_502_0@select username,default_tablespace fromuser_users; @H_502_0@ @H_502_0@修改用户默认表空间 @H_502_0@alter user jlyz default tablespace jlyz_test; @H_502_0@查询当前用户拥有权限 @H_502_0@select*fromrole_sys_privs; @H_502_0@ @H_502_0@删除表的某一行 @H_502_0@ALTER TABLE alonerewardinfo DROP COLUMNzfids; @H_502_0@查询表中某一列在所有表中被引用的记录
select b.table_name,b.column_name from user_constraints a
inner join user_cons_columns b
on a.constraint_name =b.constraint_name
@H_502_0@where a.r_constraint_name='SYS_C0018059' @H_502_0@ @H_502_0@ @H_502_0@ @H_502_0@ @H_502_0@查看数据库默认的密码管理方式有效期 @H_502_0@SELECT * FROM dba_profiles WHERE profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'; @H_502_0@修改用户密码并设置数据库默认密码为永久有效 @H_502_0@ ALTER PROFILEDEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED ; @H_502_0@Oracle用户解锁 @H_502_0@alter user sw account unlock @H_502_0@oracle导入导出 @H_502_0@导入: impjlyz/123456789 file=E:\jlyz.dmp log=E:\jlyz.logfromuser=jlyz touser=jlyz @H_502_0@导出: expjlyz/123456789 file=E:\jlyz.dmp log=E:\log-.log--1、查看表空间的名称及大小
SELECTt.tablespace_name,round(SUM(bytes / (1024 * 1024)),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 /(1024 * 1024),0) total_space
FROMdba_data_files
@H_502_0@ORDERBY tablespace_name; @H_502_0@ @H_502_0@--修改表空间大小 @H_502_0@alterdatabasedatafile'D:\ORACLDATA\JLYZ_TEST.DBF'resize10240m @H_502_0@ @H_502_0@ @H_502_0@--查看表空间使用情况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 * 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
GROUPBY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024),2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUPBY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
@H_502_0@ORDERBY1;