A script to truncate tables that have foreign keys referencing them. It also keeps one record in the table, the one with ID=0. In this case, each table has this field as a primary key.
Finally, some tables are not to be truncated. The readible names of these tables are in a metadata table called TDWM_TABLES. The readible name is in the field VIEWNAME.
The process is:
- disable constraints on the selected tables, i.e. with names like 'TDWH_%'
- create a new intermediate table via CTAS that contains the records to keep, i.e. with ID=0
- truncate the table
- copy records back from the intermediate table
- drop intermediate table
- enable constraints on the selected tables, i.e. with names like 'TDWH_%'. Note: enable PK constraints before enabling the FK constraints!
Here is the script:
/* SCRIPT */
SET SERVEROUTPUT ON SIZE UNLIMITED
BEGIN
-- Disable constraints
DBMS_OUTPUT.PUT_LINE ('-> Disabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' CASCADE' as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%')
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;
-- Truncate tables
DBMS_OUTPUT.PUT_LINE ('-> Truncating tables');
FOR reg IN (
SELECT
'CREATE TABLE Z_' || TABLE_NAME || ' AS SELECT * FROM ' || TABLE_NAME || ' WHERE ID=0' AS ST1,
'TRUNCATE TABLE ' || TABLE_NAME AS ST2,
'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM Z_' || TABLE_NAME AS ST3,
'DROP TABLE Z_' || TABLE_NAME AS ST4
FROM USER_TABLES t
INNER JOIN TDWM_TABLES b ON UPPER(t.TABLE_NAME) = UPPER(b.TABLENAME)
WHERE t.TABLE_NAME LIKE 'TDWH_%' AND
UPPER(b.VIEWNAME) NOT IN ('TDAYOFWEEK','TMONTHOFYEAR','TQUARTEROFYEAR')
)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
DBMS_OUTPUT.PUT_LINE (reg.ST2); EXECUTE IMMEDIATE reg.ST2;
DBMS_OUTPUT.PUT_LINE (reg.ST3); EXECUTE IMMEDIATE reg.ST3;
DBMS_OUTPUT.PUT_LINE (reg.ST4); EXECUTE IMMEDIATE reg.ST4;
END LOOP;
-- Enable constraints
DBMS_OUTPUT.PUT_LINE ('-> Enabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%' ORDER BY CONSTRAINT_NAME DESC)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;
END;
Unlimited login attempts on Oracle 11g
In Oracle 10g, by default, each user has unlimited login attempts before being blocked. In Oracle 11g this behaviour is changed to 10 attempts.
When a user/schema is created, it gets assigned to the default profile. The easiest way for unlimited login attempts for all users is to give the default profile the rights:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
When a user/schema is created, it gets assigned to the default profile. The easiest way for unlimited login attempts for all users is to give the default profile the rights:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Reconfigure OracleXE built-in webserver's port
OracleXE comes with a built-in webserver, running on port 8080. Since this is a wellknown port, chances are that it is already in use on your machine, and therefore conflicting with an already running webserver.
Check this blog to reconfigure it:
http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html
Check this blog to reconfigure it:
http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html
Cannot load package & required privilege is not held by the client
How to start a filesystem dtsx package from sql agent, without a config file or without adding a package password to the command line:
- ssis: make note of the package owner, should be windows (domain) account
- ssis: package: encrypt with userkey
- os: sql agent service should run under LocalSystem account
- os: add package owner to local groups: SQLServer2005DTSUser$SERVER, SQLServer2005SQLAgent$SERVER
- ssms: create credential for package owner
- ssms: create proxy using created credential, add all principals, add all subsystems
- ssms: use the created proxy as "run as" user for the job step that starts the package
There is no easy option to specify the password for a package from sql agent, hence the "encrypt with userkey".
See also: http://support.microsoft.com/kb/918760
Background information
Principals have access to a proxy. A proxy is a mapping to a credential. A credential is a mapping to a Windows account.
1. SSMS / Security / Credential, create a credential. Set identity to an existing account that has the appropriate rights to run the job.
2. SQL Agent / Proxies / SSIS Package Execution, create a new proxy
2a. Proxy / General / Credential name: select the credential from step 1
2b. Proxy / General / Subsystems: select all
2c. Proxy / Principals / MSDB Role, add principals: sqlagentuserrole, sqlagentreaderrole, sqlagentoperatorrole. This are the principals that can access the proxy.
2d. The account must have "logon as a batch job" privileges (check with secpol.msc)
4. Set SQL Server Agent service account to LocalSystem
5. If needed, run a job step under another account. Typically: a SQL Server account based on a Windows account: SQL Server Agent / Jobs / job / Steps / Edit / Advanced / Run as user,
- select proxy
- type SSIS package
- package source File system
If the "A required privilege is not held by the client" error occurs (from Microsoft website):
1. Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account.
2. Stop and then start the SQL Server Agent service.
3. Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.
4.Stop and then start the SQL Server Agent service.
From Marc at Stackoverflow:
So it boils down to really just two accounts:
one account is needed to run the SQL Server Agent Windows service - this is a Windows account on your machine / server which needs to have enough permissions to run the service, start and stop it - either use LocalSystem, Network Service, or whatever other Windows account you have to run services with
The other account would be the account to run your SQL Server Agent steps under - that's typically a SQL Server account (which could be based on a Windows account), and it needs enough privileges inside SQL Server to do its job, e.g. it needs access to the database objects and all. I would strive to have just one account for each app that runs the SQL Server jobs - makes life a whole lot easier!
From Influent at SQLTeam:
1. set ProtectionLevel to DontSaveSensitive in package properties in Visual Studio (using SSPI anyway so not sure it mattered)
2. created configuration file (had to alter path to config file in dtsx file since it's different on servers than in dev environment)
3. instead of importing the package into MSDB, set the job step Package Source to "File system" and pointed to dtsx file on local drive of server
4. set the Configurations tab of the job step to point to the dtsConfig file created in step 2
5. made sure the AD user kicking off the job was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER (I eventually removed Administrator permissions because I only needed it to log to Windows Event Log)
6. made the associated SQL user to the user in step 5 part of the SQLAgentUserRole in the msdb database
7. made sure the local user assigned to the SQL Agent service was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER
From xprog.blogspot.com:
1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314). The step failed.
The error 1314 is "A required privilege is not held by the client".
This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.
To fix it verify:
1. The proxy account have the "Act as part of the operating system" privilege.
2. The SQL Server Account is in the group
SQLServer2005MSSQLUser$$
3. The Proxy account is in the group
SQLServer2005DTSLUser$$
SQL Server Agent Account must have 'Adjust memory quotas for a process' (or 'Increase quotas' in pre-Windows 2003) privilege on SQL Server.
From FriggleFragle at MSDN:
an issue i found was that sql server agent and ssis had been changed to not use the local system account. this caused me to continually get the dreaded --> "A required privilege is not held by the client" error. i fought with it all afternoon and eventually updated the services to use the local system account and it worked. perhaps this will save some other as much time as i wasted.
- ssis: make note of the package owner, should be windows (domain) account
- ssis: package: encrypt with userkey
- os: sql agent service should run under LocalSystem account
- os: add package owner to local groups: SQLServer2005DTSUser$SERVER, SQLServer2005SQLAgent$SERVER
- ssms: create credential for package owner
- ssms: create proxy using created credential, add all principals, add all subsystems
- ssms: use the created proxy as "run as" user for the job step that starts the package
There is no easy option to specify the password for a package from sql agent, hence the "encrypt with userkey".
See also: http://support.microsoft.com/kb/918760
Background information
Principals have access to a proxy. A proxy is a mapping to a credential. A credential is a mapping to a Windows account.
1. SSMS / Security / Credential, create a credential. Set identity to an existing account that has the appropriate rights to run the job.
2. SQL Agent / Proxies / SSIS Package Execution, create a new proxy
2a. Proxy / General / Credential name: select the credential from step 1
2b. Proxy / General / Subsystems: select all
2c. Proxy / Principals / MSDB Role, add principals: sqlagentuserrole, sqlagentreaderrole, sqlagentoperatorrole. This are the principals that can access the proxy.
2d. The account must have "logon as a batch job" privileges (check with secpol.msc)
4. Set SQL Server Agent service account to LocalSystem
5. If needed, run a job step under another account. Typically: a SQL Server account based on a Windows account: SQL Server Agent / Jobs / job / Steps / Edit / Advanced / Run as user,
- select proxy
- type SSIS package
- package source File system
If the "A required privilege is not held by the client" error occurs (from Microsoft website):
1. Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account.
2. Stop and then start the SQL Server Agent service.
3. Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.
4.Stop and then start the SQL Server Agent service.
From Marc at Stackoverflow:
So it boils down to really just two accounts:
one account is needed to run the SQL Server Agent Windows service - this is a Windows account on your machine / server which needs to have enough permissions to run the service, start and stop it - either use LocalSystem, Network Service, or whatever other Windows account you have to run services with
The other account would be the account to run your SQL Server Agent steps under - that's typically a SQL Server account (which could be based on a Windows account), and it needs enough privileges inside SQL Server to do its job, e.g. it needs access to the database objects and all. I would strive to have just one account for each app that runs the SQL Server jobs - makes life a whole lot easier!
From Influent at SQLTeam:
1. set ProtectionLevel to DontSaveSensitive in package properties in Visual Studio (using SSPI anyway so not sure it mattered)
2. created configuration file (had to alter path to config file in dtsx file since it's different on servers than in dev environment)
3. instead of importing the package into MSDB, set the job step Package Source to "File system" and pointed to dtsx file on local drive of server
4. set the Configurations tab of the job step to point to the dtsConfig file created in step 2
5. made sure the AD user kicking off the job was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER (I eventually removed Administrator permissions because I only needed it to log to Windows Event Log)
6. made the associated SQL user to the user in step 5 part of the SQLAgentUserRole in the msdb database
7. made sure the local user assigned to the SQL Agent service was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER
From xprog.blogspot.com:
1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314). The step failed.
The error 1314 is "A required privilege is not held by the client".
This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.
To fix it verify:
1. The proxy account have the "Act as part of the operating system" privilege.
2. The SQL Server Account is in the group
SQLServer2005MSSQLUser$$
3. The Proxy account is in the group
SQLServer2005DTSLUser$$
SQL Server Agent Account must have 'Adjust memory quotas for a process' (or 'Increase quotas' in pre-Windows 2003) privilege on SQL Server.
From FriggleFragle at MSDN:
an issue i found was that sql server agent and ssis had been changed to not use the local system account. this caused me to continually get the dreaded --> "A required privilege is not held by the client" error. i fought with it all afternoon and eventually updated the services to use the local system account and it worked. perhaps this will save some other as much time as i wasted.
External tables
Data can be imported into and exported from Oracle 10g in different ways. E.g. by using the SQL*Loader tool for text files (sqlldr) or the Data Pump (impdp/expdp) tools for the propriety Oracle RAW format. For previous Oracle versions, the tools imp and are also available. Those import/export to external locations on the client machine, not the server machine.
Another way to get data in and out of Oracle is to define an external table. To do this you have to create a table in Oracle in the regular way using a CREATE TABLE statement, but with adding an additional ORGANIZATION EXTERNAL clause.
Within this clause the properties of the external (server side!) location can be specified.
EXAMPLE
CREATE TABLE TEST
(
"CODE" VARCHAR2(17 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
CHARACTERSET WE8MSWIN1252
FIELDS TERMINATED BY 0X'09'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION ( DATA_PUMP_DIR: 'EXTFILE.TXT' )
)
FIELD LIST
The list of fields following the create table keywords cannot be inferred dynamically, but has to be specified explicitly. So it is not possible to "hook up" a text file and retrieving the list of fields automatically.
Trick: import the data first into a table using SQL Developer, TOAD, etc, and create the DDL script from it. Finally, copy and paste the field list from this script.
It is also possible to specify the "external" list of fields inside the organization external clause. This is, for example, similar to how it is done using Oracle Warehouse Builder (OWB).
TYPE
One of these properties is "TYPE", which specifies the access driver that Oracle uses. There are two values for this property:
DEFAULT DIRECTORY / LOCATION
A directory object has to be available in Oracle. If it is not already present, it has to be created. For example:
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\'
Now the tag DATA_PUMP_DIR can be used in the organization external clause. The DEFAULT DICTIONARY property is mandatory and has to be used when setting the LOCATION.
A path specification is not allowed between the single-quotes.
This is not valid:
The options are rather straightforward, except perhaps "FIELDS TERMINATED BY". To set the field delimiter a character can be specified between single-quotes. But it is also possible to specify special characters like tab or linefeed.
To specify a tab:
FIELDS TERMINATED BY 0X'09'
The 0X instructs Oracle that the value between the single-quotes should be interpreted as an ASCII value. In the example, ASCII character 9 is a tab.
OPTIONALLY ENCLOSED BY '"'
It is also possible to specify that some values have double-quotes around them. This however does not work in the following case:
Column 1 value - tab - tab - Column 3 value
Here a record has a value for columns 1 and 3, but not for column 2. It should be read as NULL, but is actually skipped. This results in the value of column 3 being wrongly inserted in column 2.
CHARACTER SET
Explicitly setting the character set of the external location prevents problems with interpreting the data and can improve performance.
From Odi's blog:
LOGGING
All errors are logged to a bad file - containing the violating records - and a log file - containing messages about the error that occured.
To disable logging, (some of) the following keywords can be added to the clause:
Another way to get data in and out of Oracle is to define an external table. To do this you have to create a table in Oracle in the regular way using a CREATE TABLE statement, but with adding an additional ORGANIZATION EXTERNAL clause.
Within this clause the properties of the external (server side!) location can be specified.
EXAMPLE
CREATE TABLE TEST
(
"CODE" VARCHAR2(17 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATA_PUMP_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
CHARACTERSET WE8MSWIN1252
FIELDS TERMINATED BY 0X'09'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION ( DATA_PUMP_DIR: 'EXTFILE.TXT' )
)
FIELD LIST
The list of fields following the create table keywords cannot be inferred dynamically, but has to be specified explicitly. So it is not possible to "hook up" a text file and retrieving the list of fields automatically.
Trick: import the data first into a table using SQL Developer, TOAD, etc, and create the DDL script from it. Finally, copy and paste the field list from this script.
It is also possible to specify the "external" list of fields inside the organization external clause. This is, for example, similar to how it is done using Oracle Warehouse Builder (OWB).
TYPE
One of these properties is "TYPE", which specifies the access driver that Oracle uses. There are two values for this property:
- ORACLE_LOADER: only read access, from all kinds of text files (but not, for example, Excel .xls)
- ORACLE_DATAPUMP: read and write access, a propriety RAW format
The access driver is a wrapper Oracle uses to call the previously mentioned SQL*Loader or Data Pump functionality.
An error occurs when an insert statement is performed on a table is created with access driver ORACLE_LOADER:
SQL Error: ORA-30657: operation not supported on external organized table30657.0000 - "operation not supported on external organized table"The ORACLE_DATAPUMP type has to be used to export data from Oracle using an external table. Unfortunately, this data cannot be humanly read or imported into any other tool then an Oracle database.
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don't do that!
DEFAULT DIRECTORY / LOCATION
A directory object has to be available in Oracle. If it is not already present, it has to be created. For example:
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\'
Now the tag DATA_PUMP_DIR can be used in the organization external clause. The DEFAULT DICTIONARY property is mandatory and has to be used when setting the LOCATION.
A path specification is not allowed between the single-quotes.
This is not valid:
- LOCATION ( 'C:\EXTFILE.TXT' )
The options are rather straightforward, except perhaps "FIELDS TERMINATED BY". To set the field delimiter a character can be specified between single-quotes. But it is also possible to specify special characters like tab or linefeed.
To specify a tab:
FIELDS TERMINATED BY 0X'09'
The 0X instructs Oracle that the value between the single-quotes should be interpreted as an ASCII value. In the example, ASCII character 9 is a tab.
OPTIONALLY ENCLOSED BY '"'
It is also possible to specify that some values have double-quotes around them. This however does not work in the following case:
Column 1 value - tab - tab - Column 3 value
Here a record has a value for columns 1 and 3, but not for column 2. It should be read as NULL, but is actually skipped. This results in the value of column 3 being wrongly inserted in column 2.
CHARACTER SET
Explicitly setting the character set of the external location prevents problems with interpreting the data and can improve performance.
From Odi's blog:
If you do not specify the CHARACTERSET parameter, the database character set is used to interpret the input file. As the input file is in WE8MSWIN1252, the ORACLE_LOADER driver gets confused trying to interpret single-byte WE8MSWIN1252 codes as multibyte AL32UTF8 codes.
The character set of the input file depends on the way it was created. Even on US Windows, you can create text files in different encodings. Notepad allows you to save the file in ANSI code page (=WE8MSWIN1252 on US Windows), Unicode (=AL16UTF16LE), Unicode big endian (=AL16UTF16), and UTF-8 (=AL32UTF8). The Command Prompt edit.exe editor saves the files in the OEM code page (=US8PC437 on US Windows).
-- Sergiusz
LOGGING
All errors are logged to a bad file - containing the violating records - and a log file - containing messages about the error that occured.
To disable logging, (some of) the following keywords can be added to the clause:
- NODISCARDFILE
- NOBADFILE
- NOLOGFILE
Oracle specification of the ORACLE_LOADER access driver:
SQLite enforces foreign key constraints
As of version 3.6.19, SQLite finally supports foreign key constraints that can be enforced.
http://sqlite.org/releaselog/3_6_19.html
A pragma statement has to be execute on the database to enable the enforcement:
PRAGMA foreign_keys = true;
http://sqlite.org/releaselog/3_6_19.html
A pragma statement has to be execute on the database to enable the enforcement:
PRAGMA foreign_keys = true;
Measuring disk I/O speed
Disk thruput tester is a handy tool to test the sequential read and write performance of storage media, like harddisks and SSD. It supports configurating custom block sizes:
http://www.objectso.nl/
Using this tool, testing the read/write performance of 8KB, 16KB, 32KB, etc, sized blocks is a breeze. The size and location of the test file is also configurable.
Note: on the website the tool is called "disk throughput tester".
http://www.objectso.nl/
Using this tool, testing the read/write performance of 8KB, 16KB, 32KB, etc, sized blocks is a breeze. The size and location of the test file is also configurable.
Note: on the website the tool is called "disk throughput tester".
Winload.exe missing or corrupt (0xc000000e)
Today we had a problem with Acronis Disk Doctor. We used it to try to resize a existing partition. The tool doesn't directly support dynamic disks - only after downloading additional drivers. These were downloaded, because we had those in our system, and the system had to be rebooted. No changes had been made to this point. But after the reboot, the system stopped almost immediately with a "black screen" reporting:
0xC000000E \WINDOWS\SYSTEM32\WINLOAD.EXE INFO: THE SELECTED ENTRY COULD NOT BE LOADED BECAUSE THE APPLICATION IS MISSING OR CORRUPT
What made this case different from all others that I could find on the internet is that the system partition was no longer accessible. It was not assigned the C drive letter.
So, the suggestions I could find did not work. They include, but are not limited to:
0xC000000E \WINDOWS\SYSTEM32\WINLOAD.EXE INFO: THE SELECTED ENTRY COULD NOT BE LOADED BECAUSE THE APPLICATION IS MISSING OR CORRUPT
What made this case different from all others that I could find on the internet is that the system partition was no longer accessible. It was not assigned the C drive letter.
So, the suggestions I could find did not work. They include, but are not limited to:
- Repair using the Vista installation disc
- From the Vista installation disc's command prompt option:
bootrec /fixmbr -> completes succesfully, but didn't fix the issue - From the Vista installation disc's command prompt option:
bootrec /fixboot -> no system partition can be found - From the Vista installation disc's command prompt option:
bootrec /scanos -> no system partition can be found - Perform the following three commands from the Vista installation disc's command prompt option:
x:\windows\system32\bcdedit /set {default} device partition=c:
x:\windows\system32\bcdedit /set {default} osdevice partition=c:
x:\windows\system32\bcdedit /set {bootmgr} device partition=c:
-> no system partition can be found
Bootrec is the "fdisk /mbr" for Windows Vista and Windows 7.
If you got here and you still have a C drive letter assigned and that drive is accessible, then the suggestions above might work. I suggest you try them first.
If you don't have a system drive letter, then the resolution is fairly simple:
Start the repair option from a Windows 7 installation disk.
It detected that there were issues with the current Vista installation and fixed them. We rebooted the system and all was well again. No data loss and still running Vista.
We used a Windows 7 Ultimate x64 disk to fix a Windows Vista Ultimate x32 OS.
Subscribe to:
Posts (Atom)