Site icon Narayana Tutorial

SQL DBA

SQL DBA

SQL statement for creating table space

Syntax

CREATE TABLESPACE  <TablespaceName> DATAFILE  '<Datafile Path>' 
SIZE 100M AUTOEXTEND  ON MAXSIZE UNLIMITED ;

Example

CREATE TABLESPACE TUTORIALTBS DATAFILE 'C:oraclexeoradataXETUTORIALTBS.DBF' 
SIZE 100M AUTOEXTEND  ON MAXSIZE UNLIMITED ;

 

SQL statement for alter table space — ADD DATAFILE

Syntax

ALTER TABLESPACE <TablespaceName> ADD DATAFILE '<Datafile Path>' 
SIZE 4G AUTOEXTEND ON MAXSIZE UNLIMITED;

Example

ALTER TABLESPACE TUTORIALTBS  ADD DATAFILE 'C:oraclexeoradataXETUTORIAL01.DBF' 
SIZE 4G AUTOEXTEND ON MAXSIZE UNLIMITED;

 

SQL statement for alter table space — RESIZE DATA FILE SIZE

Syntax

ALTER DATABASE DATAFILE '<Datafile Path>' RESIZE 900M;

Example

ALTER DATABASE DATAFILE 'C:oraclexeoradataXETUTORIALTBS.DBF' RESIZE 900M;

 

SQL statement for creating user

Syntax

CREATE USER <UserName> IDENTIFIED BY <Password> DEFAULT TABLESPACE <TablespaceName> 
TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

Example

CREATE USER TESTUSER  IDENTIFIED BY TESTPASSWORD  DEFAULT TABLESPACE TUTORIALTBS
 TEMPORARY TABLESPACE TEMP;

 

SQL statement for update / change user password

Syntax

ALTER USER <UserName> IDENTIFIED BY <Password>;

Example

ALTER USER TESTUSER  IDENTIFIED BY TESTNEWPASSWORD;

 

SQL statement for assign table space quota to user

Syntax

ALTER USER <UserName> QUOTA 100M ON <Tablespace Name>;

Example

ALTER USER TESTUSER QUOTA 100M ON TUTORIALTBS;

 

SQL statement for assign table space unlimited quota to user

Syntax

ALTER USER <UserName> QUOTA UNLIMITED ON <Tablespace Name>;

Example

ALTER USER TESTUSER QUOTA UNLIMITED ON TUTORIALTBS;

 

SQL statement for Lock the user

Syntax

ALTER USER <UserName> ACCOUNT LOCK;

Example

ALTER USER TESTUSER ACCOUNT LOCK;

 

SQL statement for Un-Lock the user

Syntax

ALTER USER <UserName> ACCOUNT UNLOCK;

Example

ALTER USER TESTUSER ACCOUNT UNLOCK;

SQL statement for to make the user password expire

Syntax

ALTER USER <UserName> IDENTIFIED <Password> PASSWORD EXPIRE;

Example

ALTER USER TESTUSER IDENTIFIED <Password> PASSWORD EXPIRE;

 

 

SQL statement for DBA grants to user

Syntax

GRANT DBA TO <UserName>;

Example

GRANT DBA TO TESTUSER;

 

SQL statement for individual objects grants to user

GRANT CREATE ANY TABLE TO TESTUSER;
GRANT CREATE ANY VIEW TO TESTUSER;
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO TESTUSER;
GRANT EXECUTE ON SYS.DBMS_DDL TO TESTUSER;
GRANT DROP ANY VIEW TO TESTUSER;
GRANT SELECT ANY DICTIONARY TO TESTUSER;
GRANT SELECT ANY DICTIONARY TO TESTUSER;
GRANT EXECUTE ON SYS.DBMS_CRYPTO TO TESTUSER;
GRANT EXECUTE ON SYS.DBMS_DDL TO TESTUSER;
GRANT EXECUTE ON SYS.DBMS_STATS TO TESTUSER;

 

SQL statement for Running Queries

User Running Queries

By using the following statements we can check running queries(active sessions) for particular user.

Syntax

SELECT a.sid, a.username,b.sql_id, b.sql_fulltext FROM V$SESSION a, V$SQL b
WHERE a.sql_id = b.sql_id and a.status = 'status' and a.username = '<db-user>';

Example

SELECT a.sid, a.username,b.sql_id, b.sql_fulltext from V$SESSION a, V$SQL b
WHERE a.sql_id = b.sql_id and a.status = 'ACTIVE' and a.username = 'TESTUSER';

All Running Queries

Syntax

SELECT a.sid, a.username,b.sql_id, b.sql_fulltext FROM V$SESSION a, V$SQL b
WHERE a.sql_id = b.sql_id and a.status = '<status>' ;

Example

SELECT a.sid, a.username,b.sql_id, b.sql_fulltext from V$SESSION a, V$SQL b
WHERE a.sql_id = b.sql_id and a.status = 'ACTIVE' ;

Example

SELECT sesion.sid,sesion.serial#, sql_text FROM v$sqltext sqltext, v$session sesion 
WHERE sesion.sql_hash_value = sqltext.hash_value 
AND sesion.sql_address = sqltext.address 
AND sesion.username is not null order by sqltext.piece

 

SQL statement for Objects creation date to check

By using the following statement we can check objects creation details.

Types of Objects

  1. CONSUMER GROUP
  2. INDEX PARTITION
  3. SEQUENCE
  4. QUEUE
  5. SCHEDULE
  6. TABLE PARTITION
  7. RULE
  8. PROCEDURE
  9. OPERATOR
  10. LOB PARTITION
  11. WINDOW
  12. DATABASE LINK
  13. LOB
  14. PACKAGE
  15. PACKAGE BODY
  16. LIBRARY
  17. RULE SET
  18. PROGRAM
  19. TYPE BODY
  20. CONTEXT
  21. XML SCHEMA
  22. TRIGGER
  23. JOB CLASS
  24. UNDEFINED
  25. DIRECTORY
  26. TABLE
  27. INDEX
  28. SYNONYM
  29. VIEW
  30. FUNCTION
  31. WINDOW GROUP
  32. INDEXTYPE
  33. CLUSTER
  34. TYPE
  35. RESOURCE PLAN
  36. EVALUATION CONTEXT
  37. JOB
SELECT created,object_name,owner FROM dba_objects 
WHERE owner = '<db-user>' 
AND object_type = '<object-name>'
AND object_name not like 'BIN%' 
AND to_date(created,'DD-MM-YY')='<creation-date>'

Example

SELECT created,object_name,owner FROM dba_objects 
WHERE owner = 'TESTUSER' 
AND object_type = 'TABLE'
AND object_name not like 'BIN%' 
AND to_date(created,'DD-MM-YY')='11-May-2010'

dba_objects table contains the following metadata.

Query:
describe dba_objects;
output
Name           Null Type          
-------------- ---- ------------- 
OWNER               VARCHAR2(30)  
OBJECT_NAME         VARCHAR2(128) 
SUBOBJECT_NAME      VARCHAR2(30)  
OBJECT_ID           NUMBER        
DATA_OBJECT_ID      NUMBER        
OBJECT_TYPE         VARCHAR2(19)  
CREATED             DATE          
LAST_DDL_TIME       DATE          
TIMESTAMP           VARCHAR2(19)  
STATUS              VARCHAR2(7)   
TEMPORARY           VARCHAR2(1)   
GENERATED           VARCHAR2(1)   
SECONDARY           VARCHAR2(1)

Public Database Link