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

• Use the CREATE DATABASE LINK statement to create a database link.
• A database link is a schema object in one database that enables you to access objects on another database.
• The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.