Oracle Command

最新推荐文章于 2022-03-31 08:50:11 发布

原创最新推荐文章于 2022-03-31 08:50:11 发布 · 778 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#command #oracle #table #database #user #system

本文档详细介绍了Oracle数据库中用户创建、权限管理、表空间操作等关键任务的具体步骤及命令，为数据库管理员提供实用的操作指导。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. Create the user
create user dbuser identified by password default tablespace temp quota unlimited on data quota 0 on system quota 0 on tools quota 0 on users;
-- Create the user
create user CSSSDEV identified by "" default tablespace CSSS_06 temporary tablespace TEMP profile DEFAULT quota unlimited on csss_06 quota unlimited on temp;

2. Grant/Revoke object privileges
grant select , insert , update , delete , alter on tablename to dbuser;
garant select on jxjk.tablename to dbuser;

3. Grant/Revoke role privileges
grant connect to dbuser with admin option;
grant connect to dbuser;
grant connect,resources,imp_full_database,exp_full_database, create public synonym,
drop public synonym to ddbh;
grant alter system,drop user,create user to dbuser;
revoke select on table_name from dbuser;
grant dba to dbuser;
revoke dba from dbuser;
grant alter any table to dbuser with option;
revoke alter any table from dbuser;

4. Lookup Tablespace
select * from dba_tablespace;
alter tablespace users offline;
drop tablespace users;

5. Lookup freetablespace
SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

6. Lookup tablespace path
SELECT TABLESPACE_NAME,BYTES/1024/1024 MB,FILE_NAME FROM DBA_DATA_FILES;

7. Create dblink
create database link serverlink connect to dbserver identified by password user ‘db_server’;

8. Create synonym
Create synonym dbuser.tablename for dbuser.tablename;

9. Alter password
alter dbuser identified by newpassword;

10. Create sequence
create sequence seq_name increment by 1 start with 1 nomaxvalue nocycle;
-- Create sequence
create sequence PROD_SEQUENCE minvalue 99 maxvalue 999999999 start with 5160 increment by 1 cache 20;
drop sequence seq_name;

11. Create view
create or replace view royal_mdview as
select t1.str_field as f1 , t2.str_field ad f2 from toyal_mtable t1, royal_datable t2 where t1.int_field = t2.int_filed;

12. Drop view
drop view royal_mdview;

13. Alter table
alter table table_name add int_fielde integer;
alter table table_name drop column field;
alter table table_name modify str_field not null;
alter table table_name modify str_field null;
alter table table_name drop constraint pk_name cascade;
alter table table_name add constraint pk_name foreign key(key_name) references table_name on delete cascade;
alter table table_name drop constraint fk_name;
alter table table_name modify str_field default ‘deafult_value’;
alter table table_name modify str_field default null;
create unique index inx_name on table(int_field);
drop index inx_name;

14. Kill spid
alter system kill session ‘sid,serial#’ immediate;