[Oracle] oracle snippts #oracle #sql

snippets.md

Oracle commands

Tablespaces

CREATE TABLESPACE tbs_mdist2
   DATAFILE 'tbs_mdist2.dbf' 
   SIZE 100m;

CREATE TEMPORARY TABLESPACE tbs_mdist_tmp2
    TEMPFILE 'tbs_mdist_tmp2.dbf' size 5M AUTOEXTEND ON;

Schema / Users

CREATE USER M_DISTRIBUTION_DEV
  IDENTIFIED BY oracle
  DEFAULT TABLESPACE tbs_mdist2
  TEMPORARY TABLESPACE tbs_mdist_tmp2
  QUOTA 800M on tbs_mdist2;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_mdist_tmp2;

ALTER DATABASE DEFAULT TABLESPACE tbs_mdist2;

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

Import SQL con SQLc

sqlplus user/pwd@//localhost:1521/ORCLPDB1 yourpath/yourscript.sql

Alter session con locale e language in italiano

Anche per evitare errori tipo ORA-01843: not a valid month (provando a indicare SET per Settembre dove nella sessione non c'è il locale ITALIAN)

ALTER SESSION SET NLS_LANGUAGE = 'ITALIAN';
ALTER SESSION SET NLS_TERRITORY = 'ITALY';
ALTER SESSION SET NLS_CURRENCY = '€';
ALTER SESSION SET NLS_ISO_CURRENCY = 'ITALY';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';
ALTER SESSION SET NLS_CALENDAR = 'GREGORIAN';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'ITALIAN';
ALTER SESSION SET NLS_SORT = 'WEST_EUROPEAN';
ALTER SESSION SET NLS_TIME_FORMAT = 'HH24:MI:SSXFF';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH24:MI:SSXFF';
ALTER SESSION SET NLS_TIME_TZ_FORMAT = 'HH24:MI:SSXFF TZR';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'DD-MON-RR HH24:MI:SSXFF TZR';
ALTER SESSION SET NLS_DUAL_CURRENCY = '€';
ALTER SESSION SET NLS_COMP = 'BINARY';
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'BYTE';
ALTER SESSION SET NLS_NCHAR_CONV_EXCP = 'FALSE';