Oracle Command

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;