Oracle Tidbits: Difference between revisions
(Clean Output SQLPlus Settings) |
m (formatting fix) |
||
| (4 intermediate revisions by 2 users not shown) | |||
| 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: | ||
| Line 19: | Line 27: | ||
<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> | ||
==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. | |||
==Basic user Creation== | |||
<pre> | |||
create user cooluser identified by aw3s0mepwd | |||
default tablespace users | |||
temporary tablespace temp; | |||
grant create session to cooluser; | |||
grant resource to cooluser; | |||
</pre> | |||
Latest revision as of 15:32, 14 December 2010
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.
- SQLPLUS /NOLOG
- Connect internal
- alter system set log_archive_dest_1="LOCATION=F:\ORACLE\EDWD" SCOPE=MEMORY;
Also, if you want to interact with RMAN, here are some useful bits:
- rman target=<username>@<oracle_connect_string>
- RMAN> backup recovery area;
- RMAN> delete archivelog all;
Retrieving DDL from Oracle 9i
Use this:
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
as in:
SELECT DBMS_METADATA.GET_DDL('TABLE', MY_TABLE, CHIP);
Setting Up a Clean Output for long Spools from SQL*Plus
- SET HEAD OFF
- SET FEED OFF
- SET PAGESIZE 0
- SET LINESIZE 200 (or whatever)
- SET LONG 90000
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.
Basic user Creation
create user cooluser identified by aw3s0mepwd default tablespace users temporary tablespace temp; grant create session to cooluser; grant resource to cooluser;