Hot answers tagged scripting
5
In a shell script:
#!/bin/bash
sqlplus user/pass@server/DATABASE<<THEEND
-- Change "1" to the desired fatal return code
whenever sqlerror exit 1;
@yoursqlscript.sql
quit;
THEEND
Or you can just run:
sqlplus user/pass@server/DATABASE @yoursqlscript
... and put the whenever sqlerror exit 1; at the top of your .sql script(s).
4
How do I make the DROP wait for the CREATE CLUSTERED to finish?
You don't, it already does. The statements are sequential, i.e. synchronous, one after the other.
What is heap storage? Paul Randal explains
So what you really have is just a stack of paper thrown all over the desk, almost literally. Your operation neatly numbers the pages on the bottom ...
4
If the server is using TCP/IP, then the simple way is to just telnet to the SQL Server port and see if it connects. By default, that's port 1433, so this should work:
telnet servername 1433
That will probably be appropriate in most cases.
If it's using a different port, or dynamic ports (common with a named instance), then you'll need to determine which ...
3
DROP DATABASE is a very special command that cannot be undone. To my knowledge there is no way to drop a database inside a transaction. I quote the manual:
DROP DATABASE cannot be executed inside a transaction block.
Whenever you run two commands in a script, they are automatically wrapped into a transaction. You can explicitly begin and commit ...
3
You can't put CREATE FUNCTION inside IF logic since it needs to be in its own batch.
I would do it this way. If the function doesn't exist yet, create an empty stub. Now you can always safely issue an alter.
IF OBJECT_ID('dbo.function_name') IS NULL
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'CREATE FUNCTION dbo.function_name()
RETURNS INT
AS
...
3
ksh or bash let you script items through SQL*Plus, and you can do quite complex stuff through this. However, shell scripting tends to be a bit 'write-only' and sh and its derivatives aren't really much good for developing complex program logic. For running automated tasks they're OK. For complex client-side data manipulation, not so good (sed/awk ...
3
The end of your post is really the important bit:
information regarding what best suits the needs of database administrators.
So let's start there.
What are your needs as a DBA? You generally have two realms of operation: system level maintenance, and database level maintenance (whereby all actions are merely maintaining the system for better ...
3
You can't select columns from the view that aren't exposed in the view definition. If your view only includes three columns in the SELECT list, you can only reference those three columns in queries against the view.
You can't use table aliases that are defined in the view in queries against the view. You cannot, therefore, reference the e2 alias when you ...
2
You have three(3) options to go about looking at what is running in MySQL
OPTION #1 : General Log
You could simply activate the general log and look for the timestamp between 3:00-3:30 AM. You could either have the general log as a text file or a MyISAM table. Here are my posts on using the general log:
How to enable MySQL general log? (Jan 08, 2012)
How ...
2
The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text.
It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL.
The script you need ...
2
This is probably not the best way to do what you want to do. The "proper" way would probably be to execute the SQL commands in your file via jdbc.
That said, that's a long way from where you are now, and we can probably make what you are trying to do work.
The likely problem is that there is a space in the path to psql.exe.
When you use that particular ...
2
Part of the reason that you don't get the heap to shrink as much as you expect is that there's 8 bytes in each row for the dummy column. That might be large, depending on the average size of a typical row. Then you drop column dummy, leaving that space unused. Try this without adding and removing dummy and make your clustered index on other columns. They ...
2
Provided you have the Microsoft.SqlServer.Smo assembly in your GAC on the local machine, this can be easily done with PowerShell:
[Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
$server = New-Object Microsoft.SqlServer.Management.Smo.Server("YourSqlServerName")
# do a simple operation to see if you can get data
Try {
...
1
First, you can issue DROP DATABASE; from a multiline script, but it has to be the only statement of its transaction:
CREATE DATABASE droptest;
\c droptest
CREATE TABLE a (id integer);
Then run the script:
SELECT id
FROM a;
\c postgres
DROP DATABASE droptest;
It results in:
id
----
(0 rows)
psql (9.1.9, server 9.0.6)
WARNING: psql version 9.1, ...
1
It makes a difference... SQL*Plus can use variables, but they're a SQL*Plus feature and you can just grab that block of code and pass it to an Oracle backend to execute (like you could with a Transact SQL block for example).
You can choose between using bind variables in SQL*Plus as follows:
http://www.adp-gmbh.ch/ora/sqlplus/use_vars.html
Or use ...
1
For Toad, from this answer on SO:
I think this will accomplish what you want. You can declare a bind variable, insert a value into it, and then use it in future statements.
variable l_var varchar2(1);
begin
select dummy
into :l_var
from dual;
end;
select *
from dual
where dummy = :l_var;
1
If you're only concerned about Agent Jobs and logins, I think the easiest approach is to leverage a linked server and querying the relative tables. I would do this by first creating a linked server on the old instance that points at the new instance. Once I had that, I could run the following queries:
Logins
SELECT * FROM ...
Only top voted, non community-wiki answers of a minimum length are eligible
