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;