Oracle 12c Cheat Sheet - Revision history

Nicolas at 06:09, 29 May 2017

2017-05-29T06:09:52Z

← Older revision		Revision as of 06:09, 29 May 2017
Line 110:		Line 110:
	SQL></pre>		SQL></pre>

			==Create TableSpace==

			<syntaxhighlight lang="sql">
			CREATE TABLESPACE "JDELOCAL_DV920" DATAFILE 'C:\E1LOCAL\DATA\JDELOCAL_DV920.DBF' SIZE 104857600 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192
			EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
			</syntaxhighlight>

			<syntaxhighlight lang="sql">
			CREATE USER "JDELOCAL_DV920" IDENTIFIED BY VALUES password DEFAULT TABLESPACE "JDELOCAL_DV920" TEMPORARY TABLESPACE "TEMP";
			</syntaxhighlight>

			<syntaxhighlight lang="sql">
			alter user JDELOCAL_DV920 quota unlimited on JDELOCAL_DV920;
			</syntaxhighlight>

2017-05-28T04:12:04Z

No log

← Older revision		Revision as of 04:12, 28 May 2017
Line 153:		Line 153:
	===No log===		===No log===
	ORA-00742: Log read detects lost write in thread 1 sequence 20 block 248885		ORA-00742: Log read detects lost write in thread 1 sequence 20 block 248885
			<syntaxhighlight lang="sql">
			recover database until cancel;
			</syntaxhighlight>
			<syntaxhighlight lang="sql">
			Specify log: filename \| Auto \| Cancel
			AUTO
			</syntaxhighlight>

2017-05-28T04:09:36Z

No log

← Older revision		Revision as of 04:09, 28 May 2017
Line 152:		Line 152:
	==Recovery==		==Recovery==
	===No log===		===No log===
			ORA-00742: Log read detects lost write in thread 1 sequence 20 block 248885

2017-05-28T03:51:29Z

← Older revision		Revision as of 03:51, 28 May 2017
Line 149:		Line 149:
	<pre> SELECT os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') AS logtime FROM dba_audit_trail ORDER BY logtime DESC; </pre>		<pre> SELECT os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') AS logtime FROM dba_audit_trail ORDER BY logtime DESC; </pre>
	<pre> SELECT * FROM (SELECT DISTINCT os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') as logtime FROM dba_audit_trail ORDER BY logtime ASC)WHERE ROWNUM <= 10; </pre>		<pre> SELECT * FROM (SELECT DISTINCT os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') as logtime FROM dba_audit_trail ORDER BY logtime ASC)WHERE ROWNUM <= 10; </pre>

			==Recovery==
			===No log===

2017-05-28T03:42:09Z

Startup Database

← Older revision		Revision as of 03:42, 28 May 2017
Line 69:		Line 69:

	* startup (or startup mount or startup nomount)		* startup (or startup mount or startup nomount)
	* if startup with nomount: database open
	* if startup with nomount: alter database mount; alter database open		* if startup with nomount: alter database mount; alter database open
			* if startup with mount: alter database open
	<pre>		<pre>
	SQL> startup		SQL> startup

2017-05-28T03:40:16Z

Startup Database

← Older revision		Revision as of 03:40, 28 May 2017
Line 49:		Line 49:
	==Startup Database==		==Startup Database==

			* start a sql session
	<pre>		<pre>
	sqlplus /nolog		sqlplus /nolog
Line 58:		Line 58:
	SQL> </pre>		SQL> </pre>

			* Connect as sysdba
	<pre>		<pre>
	* Connect as sysdba
	SQL> connect sys/password as sysdba		SQL> connect sys/password as sysdba
	Connected to an idle instance.		Connected to an idle instance.

2017-05-28T03:39:34Z

Startup Database

← Older revision		Revision as of 03:39, 28 May 2017
Line 48:		Line 48:

	==Startup Database==		==Startup Database==
	* Connect as sysdba

	<pre>		<pre>
Line 56:		Line 56:
	Copyright (c) 1982, 2014, Oracle. All rights reserved.		Copyright (c) 1982, 2014, Oracle. All rights reserved.

			SQL> </pre>

			<pre>
			* Connect as sysdba
			SQL> connect sys/password as sysdba
			Connected to an idle instance.
	SQL> </pre>		SQL> </pre>
	* Inquiry the current state (if applicable)		* Inquiry the current state (if applicable)
Line 61:		Line 67:
	select open_mode from v$database;		select open_mode from v$database;
	</syntaxhighlight>		</syntaxhighlight>

	* startup (or startup mount or startup nomount)		* startup (or startup mount or startup nomount)
	* if startup with nomount: database open		* if startup with nomount: database open
	* if startup with nomount: alter database mount; alter database open		* if startup with nomount: alter database mount; alter database open
	~~<pre>~~
	~~SQL> connect sys/password as sysdba~~
	~~Connected to an idle instance.~~
	~~SQL> </pre>~~

	<pre>		<pre>
	SQL> startup		SQL> startup

2017-05-28T03:38:05Z

Startup Database

← Older revision		Revision as of 03:38, 28 May 2017
Line 49:		Line 49:
	==Startup Database==		==Startup Database==
	* Connect as sysdba		* Connect as sysdba
	* startup (or startup mount or startup nomount)
	* if startup with nomount: database open
	* if startup with nomount: alter database mount; alter database open
	<pre>		<pre>
	sqlplus /nolog		sqlplus /nolog
Line 59:		Line 57:

	SQL> </pre>		SQL> </pre>
			* Inquiry the current state (if applicable)
	<syntaxhighlight lang="sql">		<syntaxhighlight lang="sql">
	select open_mode from v$database;		select open_mode from v$database;
	</syntaxhighlight>		</syntaxhighlight>
			* startup (or startup mount or startup nomount)
			* if startup with nomount: database open
			* if startup with nomount: alter database mount; alter database open
	<pre>		<pre>
	SQL> connect sys/password as sysdba		SQL> connect sys/password as sysdba

2017-05-28T03:36:56Z

Startup Database

← Older revision		Revision as of 03:36, 28 May 2017
Line 59:		Line 59:

	SQL> </pre>		SQL> </pre>

			<syntaxhighlight lang="sql">
			select open_mode from v$database;
			</syntaxhighlight>

	<pre>		<pre>
	SQL> connect sys/password as sysdba		SQL> connect sys/password as sysdba

2017-04-28T12:18:32Z

← Older revision		Revision as of 12:18, 28 April 2017
Line 108:		Line 108:

	===Create a User===		===Create a User===
	<~~pre~~>CREATE USER user IDENTIFIED BY password;</~~pre~~>		<syntaxhighlight lang="oracle11">CREATE USER user IDENTIFIED BY password;</syntaxhighlight>

	===Grant Access===		===Grant Access===
	<~~pre~~>GRANT role TO user</~~pre~~>		<syntaxhighlight lang="oracle11">GRANT role TO user;</syntaxhighlight>

	=== List Users ===		=== List Users ===
	<~~pre~~>SELECT * FROM ALL_USERS;</~~pre~~>		<syntaxhighlight lang="oracle11">SELECT * FROM ALL_USERS;</syntaxhighlight>

	===Change Password===		===Change Password===
	<~~pre~~>ALTER USER user IDENTIFIED BY password </~~pre~~>		<syntaxhighlight lang="oracle11">ALTER USER user IDENTIFIED BY password;</syntaxhighlight>

	==Create Dabase==		==Create Dabase==