Oracle Tidbits - Revision history

Chip: formatting fix

2010-12-14T15:32:08Z

formatting fix

← Older revision		Revision as of 15:32, 14 December 2010
Line 34:		Line 34:

	==Basic user Creation==		==Basic user Creation==
	<~~blockquote~~>		<pre>
	create user cooluser identified by aw3s0mepwd		create user cooluser identified by aw3s0mepwd
	default tablespace users		default tablespace users
	temporary tablespace temp		temporary tablespace temp;
	;

	grant create session to cooluser;		grant create session to cooluser;
	grant resource to cooluser;		grant resource to cooluser;
	</~~blockquote~~>		</pre>

Chip: Added Basic User Creation

2010-12-14T15:30:49Z

Added Basic User Creation

← Older revision		Revision as of 15:30, 14 December 2010
Line 32:		Line 32:
	==ORA-00942: Stored Procedures Do Not Respect ROLE based Rights==		==ORA-00942: Stored Procedures Do Not Respect ROLE based Rights==
	If you get "ORA-00942: Table or view does not exist" during a stored procedure compilation, and you're otherwise sure that you have the right table/view/synonym names of course, then it may be that the RIGHTS to the object are granted through a role, which the stored procedure does not respect. Try GRANTing the rights directly to the Procedure's owner, rather via a Role, and see if that doesn't help.		If you get "ORA-00942: Table or view does not exist" during a stored procedure compilation, and you're otherwise sure that you have the right table/view/synonym names of course, then it may be that the RIGHTS to the object are granted through a role, which the stored procedure does not respect. Try GRANTing the rights directly to the Procedure's owner, rather via a Role, and see if that doesn't help.

			==Basic user Creation==
			<blockquote>
			create user cooluser identified by aw3s0mepwd
			default tablespace users
			temporary tablespace temp
			;

			grant create session to cooluser;
			grant resource to cooluser;
			</blockquote>

Chip: /* If Archive Logs Fill Up */

2010-12-09T16:00:20Z

If Archive Logs Fill Up

← Older revision		Revision as of 16:00, 9 December 2010
Line 8:		Line 8:
	<li>alter system set log_archive_dest_1="LOCATION=F:\ORACLE\EDWD" SCOPE=MEMORY;</li>		<li>alter system set log_archive_dest_1="LOCATION=F:\ORACLE\EDWD" SCOPE=MEMORY;</li>
	</ul>		</ul>

			Also, if you want to interact with RMAN, here are some useful bits:
			<ul>
			<li>rman target=<username>@<oracle_connect_string></li>
			<li>RMAN> backup recovery area;</li>
			<li>RMAN> delete archivelog all;</li>
			</ul>

	==Retrieving DDL from Oracle 9i==		==Retrieving DDL from Oracle 9i==
	Use this:		Use this:

Admin: ORA-00942: table or view does not exist

2008-04-21T18:19:32Z

ORA-00942: table or view does not exist

← Older revision		Revision as of 18:19, 21 April 2008
Line 21:		Line 21:
	<li>SET LONG 90000</li>		<li>SET LONG 90000</li>
	</ul>		</ul>

			==ORA-00942: Stored Procedures Do Not Respect ROLE based Rights==
			If you get "ORA-00942: Table or view does not exist" during a stored procedure compilation, and you're otherwise sure that you have the right table/view/synonym names of course, then it may be that the RIGHTS to the object are granted through a role, which the stored procedure does not respect. Try GRANTing the rights directly to the Procedure's owner, rather via a Role, and see if that doesn't help.

Admin: /* Setting Up a Clean Output for long Spools from SQLPlus /

2008-04-21T16:15:00Z

Setting Up a Clean Output for long Spools from SQL*Plus

← Older revision		Revision as of 16:15, 21 April 2008
Line 19:		Line 19:
	<li>SET PAGESIZE 0</li>		<li>SET PAGESIZE 0</li>
	<li>SET LINESIZE 200 (or whatever)</li>		<li>SET LINESIZE 200 (or whatever)</li>
			<li>SET LONG 90000</li>
	</ul>		</ul>

Admin: Clean Output SQLPlus Settings

2008-04-21T16:11:16Z

Clean Output SQLPlus Settings

← Older revision		Revision as of 16:11, 21 April 2008
Line 13:		Line 13:
	as in:		as in:
	<blockquote>SELECT DBMS_METADATA.GET_DDL('TABLE', MY_TABLE, CHIP);</blockquote>		<blockquote>SELECT DBMS_METADATA.GET_DDL('TABLE', MY_TABLE, CHIP);</blockquote>
			==Setting Up a Clean Output for long Spools from SQL*Plus==
			<ul>
			<li>SET HEAD OFF</li>
			<li>SET FEED OFF</li>
			<li>SET PAGESIZE 0</li>
			<li>SET LINESIZE 200 (or whatever)</li>
			</ul>

Admin: Retrieving DDL from Oracle 9

2008-03-10T16:10:55Z

Retrieving DDL from Oracle 9

← Older revision		Revision as of 16:10, 10 March 2008
Line 8:		Line 8:
	<li>alter system set log_archive_dest_1="LOCATION=F:\ORACLE\EDWD" SCOPE=MEMORY;</li>		<li>alter system set log_archive_dest_1="LOCATION=F:\ORACLE\EDWD" SCOPE=MEMORY;</li>
	</ul>		</ul>
			==Retrieving DDL from Oracle 9i==
			Use this:
			<blockquote>SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)</blockquote>
			as in:
			<blockquote>SELECT DBMS_METADATA.GET_DDL('TABLE', MY_TABLE, CHIP);</blockquote>

Admin: initial page with archive log tidbit

2007-11-28T22:12:22Z

initial page with archive log tidbit

New page

=Useful things I always forget about Oracle=
==If Archive Logs Fill Up==
After you've made some space, or to direct archiving to an emptier place, remind Oracle where to right the archive files. When it locks up, it resets something in memory that needs to be reset.

<ul>
<li>SQLPLUS /NOLOG</li>
<li>Connect internal</li>
<li>alter system set log_archive_dest_1="LOCATION=F:\ORACLE\EDWD" SCOPE=MEMORY;</li>
</ul>