Oracle – Useful stuff

This article shows a list of useful commands for Oracle that you can directly use in SQLPLus.

Create table space

create tablespace MY_DATA_TS
datafile '/home/oracle/app/oracle/product/11.2.0/dbhome_2/my_data_ts.dbf'
size 10M
autoextend on maxsize 200M
extent management local uniform size 64K;

Create a new user

CREATE USER username
IDENTIFIED BY username
DEFAULT TABLESPACE MY_DATA_TS
QUOTA 10M ON MY_DATA_TS;

Drop user

DROP USER username CASCADE;

Drop tablespace

DROP TABLESPACE <tablespacename> INCLUDING CONTENTS AND DATAFILES;

Grant

GRANT CREATE SESSION TO username;
GRANT CREATE TABLE TO username;
GRANT CREATE VIEW TO username;
GRANT CREATE MATERIALIZED VIEW TO username;
GRANT CREATE DATABASE LINK TO username;
GRANT CREATE SEQUENCE TO username;
GRANT CREATE PROCEDURE TO username;
GRANT CREATE TRIGGER TO username;

Show users

select * from all_users;

Show user grants

SELECT GRANTEE, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = '<USER_NAME>';

Show tables

select * from user_objects where object_type = 'TABLE';

Show the table structure

describe tablename;

Alter table

alter table table_name modify column_name datatype;

If you need to modify more than one column you can use the following command:

alter table table_name
modify
   (
   column1_name  column1_datatype,
   column2_name  column2_datatype,
   column3_name  column3_datatype,
   column4_name  column4_datatype
   );

Rename column

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

Date

Select hour from Date

SELECT TO_CHAR(date_column, 'dd.mm.yyyy hh24') time_value
FROM some_table
GROUP BY TO_CHAR(date_column, 'dd.mm.yyyy hh24');

02/02/2006 12:12:23 -> 2/2/2006 12

Select partial date

select trunc(sysdate,'mm') from dual; -- select until first day of month
select trunc(sysdate,'dd') from dual; -- select until day
select trunc(sysdate,'hh') from dual; - select until hours

Insert a date

insert into table_name (date_field) values (to_date('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));

Object

Display all constraints

SELECT * FROM USER_CONSTRAINTS

View triggers

select * from user_triggers;

View a specific trigger:

select * from user_triggers where trigger_name = '<YOUR TRIGGER>';

Search invalid triggers

SELECT *
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'TRIGGER'
AND STATUS <> 'VALID'

Nomenclature

SID: unique name of the INSTANCE (eg the oracle process running on the machine). Oracle considers the “Database” to the be files.
Service Name: alias to an INSTANCE (or many instances). The main purpose of this is if you are running a cluster, the client can say “connect me to SALES.acme.com”, the DBA can on the fly change the number of instances which are available to SALES.acme.com requests, or even move SALES.acme.com to a completely different database without the client needing to change any settings.
Database: set of files where data is stored.
Instance: set of processes that manipulate the database. For most folks, there is one instance per database. You can get multiple instances per database when you’re using RAC though.
Schema: set of objects owned by a particular user account. Most user accounts should not own any objects, so they are often not associated with a schema.

MadBit