[ mssql ] create mdf without ldf

CREATE DATABASE AdventureWorksDW2012 ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\AdventureWorksDW2012_Data.mdf') FOR ATTACH_REBUILD_LOG; 

NOTE :

1. place the mdf file in the path that MSSQL has permission, otherwise you will get

The log cannot be rebuilt when the primary file is read-only.

2. I download AdventureWorksDW2012 here, and rename what is in the zip file into `AdventureWorksDW2012_Data.mdf`

ref : codeplex

[ oracle ] not a valid month

select * from nls_session_parameters;

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY    AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS    .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT    DD-MON-RR
NLS_DATE_LANGUAGE    AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT    HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY    $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP    FALSE

Since my import date format is `5/26/2013`, I have to change the nls_date_format.

alter session set nls_date_format='MM-DD-YY';

[ C# ] Provider=OraOLEDB.Oracle Connection Pool

From oracle site that link to MS site,

Default services enabled DBPROP_INIT_OLEDBSERVICES

property value

All services (default) DBPROPVAL_OS_ENABLEALL
All except pooling and automatic transaction enlistment (DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_TXNENLISTMENT & ~DBPROPVAL_OS_RESOURCEPOOLING)
All except pooling (DBPROPVAL_OS_ENABLEALL &

~DBPROPVAL_OS_RESOURCEPOOLING)

All except automatic transaction enlistment (DBPROPVAL_OS_ENABLEALL &

~DBPROPVAL_OS_TXNENLISTMENT)

All except Client Cursor Engine (DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_CLIENTCURSOR)
All except pooling, automatic transaction enlistment, and Client Cursor Engine (DBPROPVAL_OS_ENABLEALL & ~DBPROPVAL_OS_RESOURCEPOOLING & ~DBPROPVAL_OS_TXNENLISTMENT & ~DBPROPVAL_OS_CLIENTCURSOR)
Pooling and automatic transaction enlistment only, session level aggregation only DBPROPVAL_OS_TXNENLISTMENT & DBPROPVAL_OS_RESOURCEPOOLING
No services DBPROPVAL_OS_DISABLEALL
Default services enabled Value in connection string
All services (default) “OLE DB Services = -1;”
All except pooling and automatic transaction enlistment “OLE DB Services = -4;”
All except Client Cursor Engine “OLE DB Services = -5;”
All except pooling, automatic transaction enlistment, and Client Cursor Engine “OLE DB Services = -8;”
Pooling and automatic transaction enlistment only, session level aggregation only “OLE DB Services = 3;”
No services “OLE DB Services = 0;”

We noticed that Oracle pooling is enabled by default.

Quote from Oracle site, “By default, the OLEDB_SERVICES registry entry for OraOLEDB is set, under the CLSID of OraOLEDB, to 0xffffffff (that is, -1), which enables all services. Certain OLE DB Services can also be disabled or enabled programmatically through the DBPROP_INIT_OLEDBSERVICES property setting.”

[SOLVED] ORA-01653: unable to extend table SYS.IDL_UB2$ by 128 in tablespace SYSTEM

Problem

Error report:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.IDL_UB2$ by 128 in tablespace SYSTEM
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

Validation

The tablespace SYSTEM is full. We can validate using the login `system` by the following SQL.

SELECT TABLESPACE_NAME
, FILE_NAME
, BYTES / 1024 / 1024 AS BYTES_MB
, AUTOEXTENSIBLE
, MAXBYTES  / 1024 / 1024 AS MAXBYTES_MB
FROM DBA_DATA_FILES;

If the `bytes` == `maxbytes`, then it is full.

Solution

This is how to extend the tablespace by manual.

ALTER DATABASE DATAFILE 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'
AUTOEXTEND ON NEXT 1M MAXSIZE 1024M;

Tips

How to shutdown oracle database

login sqlplus as sysdba

C : > sqlplus /  as sysdba

shutdown

SQL > shutdown immediate

or startup

SQL > startup