Archive for the ‘Programming’ Category
Sometimes we occasionally just miss the obvious, for years. Just noticed that an easy way to release code to a particular schema is to login as your normal DBA user (USER1) [as preferred by audit], use the alter session command to switch to point to the release schema (USER2) and run your DDL. Oracle behaves, from an object-owner perspective, as if you are logged-in as the schema owner without all the negative aspects of actually having to login as the schema owner or prefix all object with the schema owner.
This is particularly useful when you have multiple schemas within the database and you are releasing to more than one schema at once.
sqlplus USER1@ORCL SQL*Plus: Release 22.214.171.124.0 Production on Wed Sep 17 12:17:36 2014 Copyright (c) 1982, 2010, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options 12:17:38 USER1@ORCL>select owner,table_name from dba_tables where table_name = 'N1'; no rows selected 12:17:46 USER1@ORCL>create table n1 (c1 date); Table created. 12:17:56 USER1@ORCL>select owner,table_name from dba_tables where table_name = 'N1'; OWNER TABLE_NAME ------------------------------ ------------------------------ USER1 N1 12:17:57 USER1@ORCL>alter session set current_schema=USER2; Session altered. 12:18:08 USER1@ORCL>create table n1 (c1 date); Table created. 12:18:11 USER1@ORCL>select owner,table_name from dba_tables where table_name = 'N1'; OWNER TABLE_NAME ------------------------------ ------------------------------ USER2 N1 USER1 N1
When you end up spending a far great a percentage of your day than seems sensible killing off Java connections that Developers have carelessly left lying around, locking objects all over the place, you need a solution to get them to go away. The solution is to let them do it themselves!
I’m not advocating granting ALTER SYSTEM to Developers! That way madness lies, or certainly some unintended consequences. I’m all for Dev’s having a lot of freedom in the database, just not freedom with the database.
So, creating a stored procedure (in this example as sys, but as any user with an explicit ALTER SYSTEM privilege granted will do) to kill sessions without allowing too much latitude to do anything else seems appropriate. Here’s one I built earlier:
create or replace procedure sys.kill_session ( p_sid IN number, p_serial IN number, p_instance IN number) as -- Neil Chandler. Grant the ability to kill session on a restricted basis. 21.07.2010 l_username varchar2(30) := null; l_priv number := 1; begin -- Who owns the session? select username into l_username from gv$session where sid = p_sid and serial#=p_serial and inst_id = p_instance; -- Check for DBA role select count(*) into l_priv from dba_role_privs where grantee = l_username and granted_role = 'DBA'; -- If the user has the DBA priv, deny the kill request if l_priv > 0 or l_username is null then dbms_output.put_line ('User request to kill session '||p_sid||','||p_serial||',@'||p_instance|| ' denied. Session is for privileged user '||l_username||'.'); else dbms_output.put_line ('Killing user '||l_username||' - '||p_sid||','||p_serial||',@'||p_instance); execute immediate 'alter system disconnect session '''|| p_sid||','||p_serial||',@'||p_instance|| ''' immediate'; end if; end; / -- and let the proc be seen and used create or replace public synonym kill_session for sys.kill_session; grant execute on kill_session to (whomever); Then a nifty bit of sql to generate the kill commands for the Developers. Please include your favourite columns from gv$session: select username,status,blocking_session, 'exec kill_session ('|| sid||','||serial#||','||inst_id||')' Kill_Command from gv$session where username is not null and type <> 'BACKGROUND' / USERNAME STATUS BLOCKING_SESSION KILL_COMMAND --------- ------- ---------------- ------------------------------------ SYS ACTIVE exec kill_session (31,65,1) SYSTEM INACTIVE exec kill_session (58,207,1) USER_1 INACTIVE exec kill_session (59,404,1) USER_2 INACTIVE exec kill_session (72,138,1) USER_2 INACTIVE exec kill_session (46,99,2)
May the odds be forever in your favour. Let the killing commence…
When giving a presentation last year about how much a DBA should do to get to the bottom of a problem; in a discussion between Martin Widlake, myself, and the audience we amusingly concluded that we probably shouldn’t be doing things quite right first time.
What do I mean by this? Well, we should be doing things right-enough. We don’t want anything to fail. We want the project you are working on to succeed. BUT, where is the mileage in doing everything right first time? NOBODY got commended by their management for implementing a really efficient IOT in the original schema design over and above a Heap. How many people will ooh and aah, and give you a really big bonus, over than nifty single table hash cluster that makes the system just Zing? Or the really, really clever statistics design. Nobody ever notices when the implementation is just right.
That said, even when you have plenty of influence over the design, spend ages getting it right, ensuring all the right bits are partitioned in the right way, the right services on the more appropriate nodes, seriously exceed the required number of transaction per seconds as they gave you a much bigger set of servers and SAN resources than you asked for, and then the Developers go and use an ORM like Hibernate and mess the whole thing up:
- Can you just change the query to work like this…. No.
- You query appears to be mixing ANSI-standard SQL with Oracle-syntax SQL. Can you please be consistent as this isn’t a good idea… No.
– You want me to write a trigger to audit when a new record has a different value in a column because you don’t know how to write a join, and you have no idea what a Windowing function is in SQL. Oh, you don’t actually understand SQL at all!
Some days it’s just not worth chewing through the straps.
When using SQL Developer, there is a feature called Completion Insight which help you write SQL and PL/SQL in the SQL Worksheets. It will auto-pop helpful things, like object name when typing a SELECT statement, column names if it is aware of the object which you are accessing and things like that.
The pop-up seems to pop-up inconsistently, appearing when I don’t want it and not appearing when I do. I finally snapped and decided to work out (look up) exactly how this feature works. And it’s like this.
1. Make sure your SQL is correctly formed. If you have multiple bits of SQL in the Worksheet, and they are not terminated with a “;” or “/“, then it can get confused about where you are contextually within a statement and not pop-up.
2. The Auto-popup has a delay of either 0.3s (SQL) or 0.6s (PL/SQL). This can be changed in Tools -> Preferences -> Code Editor -> Completion Insight. I have now set a 1.0s delay so it’s not quite so eager to appear.
3. You want the pop-up to appear? Press CTRL + SPACE. If it still does not appear, then it cannot determine your SQL Context (see point 1) or there’s nothing to pop.
Hope this helps you. It’s certainly helped me.
I’ve been spending some time working in Apex recently, building a small app to draw together the monitoring of application and infrastructure components into a single easy-to-visualise tool. As part of that, I wanted to be able to read and report on the alert log. Traditionally, that would have meant creating an external table to point to the alert log and reading it that way, with lots of string manipulation and regular expressions to try to pull out useful bits of information. However, Oracle 11G had made that a lot easier. Step forward X$DBGALERTEXT. This is the decoded version of the XML version of the Alert log, and as such provides lots of lovely columns to filter by, rather than a single line of text to decode. Particularly useful (for me) is the MESSAGE_LEVEL. Is this line of text informational (16), or critical (1), or something in between? Of course, each “normal” line of text is still available in the MESSAGE_TEXT column.
SQL> desc x$dbgalertext; Name Type ------------------------------ -------------------------------------------------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE ORGANIZATION_ID VARCHAR2(64) COMPONENT_ID VARCHAR2(64) HOST_ID VARCHAR2(64) HOST_ADDRESS VARCHAR2(46) MESSAGE_TYPE NUMBER MESSAGE_LEVEL NUMBER MESSAGE_ID VARCHAR2(64) MESSAGE_GROUP VARCHAR2(64) CLIENT_ID VARCHAR2(64) MODULE_ID VARCHAR2(64) PROCESS_ID VARCHAR2(32) THREAD_ID VARCHAR2(64) USER_ID VARCHAR2(64) INSTANCE_ID VARCHAR2(64) DETAILED_LOCATION VARCHAR2(160) PROBLEM_KEY VARCHAR2(64) UPSTREAM_COMP_ID VARCHAR2(100) DOWNSTREAM_COMP_ID VARCHAR2(100) EXECUTION_CONTEXT_ID VARCHAR2(100) EXECUTION_CONTEXT_SEQUENCE NUMBER ERROR_INSTANCE_ID NUMBER ERROR_INSTANCE_SEQUENCE NUMBER VERSION NUMBER MESSAGE_TEXT VARCHAR2(2048) MESSAGE_ARGUMENTS VARCHAR2(128) SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128) SUPPLEMENTAL_DETAILS VARCHAR2(128) PARTITION NUMBER RECORD_ID NUMBER
Very handy. Just add your own view, synonym and permissions to read the view, and you’re away…
create view v_$alert_log as select * from x$dbgalertext; create public synonym v$alert_log for sys.v_$alert_log; grant select on v$alert_log to whomever... 1* select message_text from v$alert_log where ...; MESSAGE_TEXT ----------------------------------------------------------------- LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Shared memory segment for instance monitoring created Picked latch-free SCN scheme 2 Autotune of undo retention is turned on. LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up: Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. Using parameter settings in server-side pfile /home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initorcl.ora System parameters with non-default values: processes = 200 sessions = 322 sga_max_size = 2G pre_page_sga = TRUE nls_language = "ENGLISH" nls_territory = "UNITED KINGDOM" filesystemio_options = "SetAll" sga_target = 2G control_files = "/u02/oradata/orcl/control01.ctl" . [snip] . aq_tm_processes = 1 diagnostic_dest = "/u20/apps/oracle" PMON started with pid=2, OS id=2492 PSP0 started with pid=3, OS id=2494 VKTM started with pid=4, OS id=2512 at elevated priority VKTM running at (1)millisec precision with DBRM quantum (100)ms GEN0 started with pid=5, OS id=2520 DIAG started with pid=6, OS id=2522 ...etc...