TABLES NAMES

— TO HAVE A LIST OF TABLES YOU OWN
SELECT TABLE_NAME FROM USER_TABLES;

— TO HAVE A LIST OF TABLES YOU CAN SELECT (YOURS + YOU ARE GRANTED TO SELECT)
SELECT TABLE_NAME FROM ALL_TABLES;

— TO HAVE A LIST OF ALL TABLES IN THE DB (IF YOU’RE GRANTED TO SEE THEM)
SELECT TABLE_NAME FROM DBA_TABLES;

OTHERS

— GET THE CREATE SCRIPT
SELECT DBMS_METADATA.GET_DDL (‘TABLE’,'<TABLE_NAME>’)||’;’ FROM DUAL;

–SELECT CONSTRAINT
SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME='<CONST NAME>’;

–SELECT VIEW
SELECT * FROM SYS.DBA_VIEWS WHERE VIEW_NAME LIKE ‘<VIEW_NAME>’

— create index
CREATE INDEX NATIONAL_SEARCH_HITS_IDX ON NATIONAL_SEARCH_HITS (VCMID);
select index_name from dba_indexes where table_name=’taBLE’;

Select COLUMN_NAME from user_tab_columns where table_name=’NEO_TC_APPLICATION’;

DATABASE INFO

— GET THE DB BLOCK SIZE AND CHARACTER SET
SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘DB_BLOCK_SIZE’;
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_NCHAR_CHARACTERSET’
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_CHARACTERSET’

COPY AND CREATES

–COPY/BACKUP TABLE
CREATE TABLE <TABLE_BAKCKUP> AS SELECT * FROM <TABLE>;

— CREATE A VIEW
CREATE OR REPLACE VIEW V_IINSIGHT_LASTUPDATED AS
SELECT …

CREATE A USER (DBA)

DROP USER <USERNAME>;

CREATE USER RTB IDENTIFIED BY <USERNAME>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP

SELECT TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
FROM DBA_TABLESPACES
ORDER BY TABLESPACE_NAME

GRANT DBA TO <USERNAME>;

CREATE AUTO-NUMBER FOR A TABLE

ORACLE + AUTO NUMBER

CREATE TABLE <TABLE>
(ID NUMBER PRIMARY KEY,
NAME VARCHAR2(30));

SELECT * FROM <TABLE>;

CREATE SEQUENCE <TABLE_SEQUENCE>
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER <TRIGGER>
BEFORE INSERT
ON <TABLE>
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT <TABLE_SEQUENCE>.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

INSERT INTO <TABLE> (NAME) VALUES (‘TEST’);

DATES

to_date(LASTUPDATED,’YYYYMMDDHH24MISS’)

TO_CHAR( MODIFIEDON,’YYYYMMDDHH24MISS’)

Administration

List the log file size

select trunc(completion_time), sum(BLOCKS*BLOCK_SIZE/1024/<wbr/>1024) from  v$archived_log group by  trunc(completion_time) order by 1;

List events

select event,count(*) from gv$session where wait_class <> 'Idle' group by event

List locks

select * from DBA_2PC_PENDING;